sqlite 3 python help

[yuvalsaias]

hello everyone i have a problem as you can see in the code and i don't know how to fix it, i need your help please with this situation about what am i doing wrong.

thank you:)

this is the error i get:

Error:
Traceback (most recent call last):
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/guardslist.py", line 35, in <module>
    guard_time_insert(name)
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/guardslist.py", line 27, in guard_time_insert
    c.execute("INSERT INTO guardwatch(date,name,time,end) VALUES(?,?,?,?)",(date,name,guardwatch,end))
sqlite3.OperationalError: table guardwatch has no column named end

from random import *
import sqlite3
import sys
import time
guardscount = int(input('how many guards there are?:'))
guardtimes = [23, 24, 2, 3, 5, 6, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21]
guardlist = []

conn = sqlite3.connect('guardlist.db')
c = conn.cursor()

def guard_names_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardnames(name TEXT)")

def guard_name_insert(name):
    c.execute("INSERT INTO guardnames(name) VALUES(?)",(name,))
    conn.commit()


def guard_time_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardwatch(date REAL,name TEXT,time REAL,end REAL)")

def guard_time_insert(name):
    date = time.strftime("%H:%M:%S")
    guardwatch = guardtimes[randint(0, len(guardtimes) - 1)]
    end = 10
    c.execute("INSERT INTO guardwatch(date,name,time,end) VALUES(?,?,?,?)",(date,name,guardwatch,end))
    conn.commit()

guard_time_table()
guard_names_table()
for i in range(0,guardscount):
    name = input('what is name of the guard?:')
    guard_name_insert(name)
    guard_time_insert(name)

c.close()
conn.close()

[buran]

It looks like your code (when I removed the formatting) works fine. I would guess you have created guardlist.db and guardwatch with different column name(s). To check the names in the table guardwatch execute the following code

import sqlite3
conn = sqlite3.connect('guardlist.db')
curs = conn.execute('select * from guardwatch')
names = [description[0] for description in curs.description]
print (names)
curs.close()
conn.close()

[yuvalsaias]

hey thank you very much for your help that problem was solved for me but i have another problem here.
i want to select the last value from a tuple and than add '3' to it but i cant because its a tuple and i cant use it as an integer.
how can i do that i will be able to add 3 to that ?
hope you understood what im asking sorry about my english.

Error:
Traceback (most recent call last):
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/guardslist.py", line 58, in <module>
    print(guard_time_distance(name, guardtime))
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/guardslist.py", line 44, in guard_time_distance
    print(10 - int(lastguard))
TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'

from random import *
import sqlite3
import sys
import time
import datetime
guardscount = int(input('how many guards there are?:'))
guardtimes = [23, 24, 2, 3, 5, 6, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21]
guardlist = []

guardnames = []
for i in range(0,guardscount):
    name = input('what is name of the guard?:')
    guardnames.append(name)


conn = sqlite3.connect('guardlist.db')
c = conn.cursor()


def guard_names_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardnames(name TEXT)")

def guard_name_insert(name):
    c.execute("INSERT INTO guardnames(name) VALUES(?)",(name,))
    conn.commit()


def guard_time_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardwatch(date REAL,name TEXT ,guardstart INTEGER,guardend REAL)")

def guard_time_insert(name,guardtime):
    date = datetime.date.today()
    end = int(guardtime) + 3
    if end >= 24:
        end -= 24
    c.execute("INSERT INTO guardwatch(date,name,guardstart,guardend) VALUES(?,?,?,?)",(date,name,guardtime,end))
    conn.commit()


def guard_time_distance(name,time):
    c.execute("SELECT guardstart FROM guardwatch WHERE name= name")
    data = c.fetchall()
    lastguard = data[-1]
    print( int(lastguard) + 3)



guard_time_table()
guard_names_table()

for name in guardnames:
    guardtime = guardtimes[randint(0, len(guardtimes) - 1)]
    print(guard_time_distance(name, guardtime))
    guard_name_insert(name)
    guard_time_insert(name,guardtime)


c.close()
conn.close()

[sparkz_alot]

Please provide an example of what you are talking about.

[yuvalsaias]

what im talking about is about that part of the code:

def guard_time_distance(name,time):
    c.execute("SELECT guardstart FROM guardwatch WHERE name= name")
    data = c.fetchall()
    lastguard = data[-1]
    print( int(lastguard + 3))

the lastguard variable contain the last value of the guardstart column where name=name(name will be provide by the for loop at the bottom of the full code)
now i want to add 3 to the value(which will always be a number) that lastguard varibale hold.
but it gives me the error i posted at the top of the code in the last massage which say i cant do it because lastguard is a tuple and i cant add an integer to it.
so i need to know what can i do so i will be able to add 3 to that number thats lastguard  variable holding.
thank you

[snippsat]

It's a little messy,i do not get data into guardlist.db when i run it.
Can make your error,i do not get same error i get error at line 43.

>>> lastguard = ('1', '2')
>>> print(int(lastguard) + 3)
Traceback (most recent call last):
  File "<string>", line 301, in runcode
  File "<interactive input>", line 1, in <module>
TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'

# Fix
>>> print(int(lastguard[1]) + 3)
5

Add print([color=#000000][size=small][font=Consolas,]lastguard)[/font][/size][/color] as line 44,i guess you see that it's a tuple.

[buran]

data is tuple of tuples, and obviously you know how to access tuple elements by index :-)
Note that if there is more than one guard with name=name, c.fetchall will return more than one result and with data[-1] you will process only the last one

[yuvalsaias]

thank you for your help!
again please there is something really wired about the error i am getting here which i cant figure out why:

Error:
Traceback (most recent call last):
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/test.py", line 52, in <module>
    print(guard_time_distance('yuval', guardtime))
  File "C:/Users/Yuval/PycharmProjects/guardsdatabase/test.py", line 43, in guard_time_distance
    c.execute("SELECT guardstart FROM guardwatch WHERE name= gname")
sqlite3.OperationalError: no such column: gname

gname is a argument which when i run it as a example of my name 'yuval' which gname is holding at the time with the function is running.
i dont know why it say "there is no such column as gname"

from random import *
import sqlite3
import sys
import time
import datetime

guardscount = int(input('how many guards there are?:'))
guardtimes = [23, 24, 2, 3, 5, 6, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21]
guardlist = []

guardnames = []
for i in range(0, guardscount):
    name = input('what is name of the guard?:')
    guardnames.append(name)

conn = sqlite3.connect('guardlist.db')
c = conn.cursor()


def guard_names_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardnames(name TEXT)")


def guard_name_insert(name):
    c.execute("INSERT INTO guardnames(name) VALUES(?)", (name,))
    conn.commit()


def guard_time_table():
    c.execute("CREATE TABLE IF NOT EXISTS guardwatch(date REAL,name TEXT ,guardstart INTEGER,guardend REAL)")


def guard_time_insert(name, guardtime):
    date = datetime.date.today()
    end = int(guardtime) + 3
    if end >= 24:
        end -= 24
    c.execute("INSERT INTO guardwatch(date,name,guardstart,guardend) VALUES(?,?,?,?)", (date, name, guardtime, end))
    conn.commit()


def guard_time_distance(gname, time):
    c.execute("SELECT guardstart FROM guardwatch WHERE name= gname")
    for row in c.fetchall():
        print(row)


guard_time_table()
guard_names_table()

guardtime = guardtimes[randint(0, len(guardtimes) - 1)]
print(guard_time_distance('yuval', guardtime))
guard_name_insert('yuval')
guard_time_insert('yuval', guardtime)

c.close()
conn.close()

[buran]

Oh, it's wrong even in your previous code. Your SQL statement - you don't supply gname as argument, but you hardcode gname in it. So it thinks gname is column and understand the sql condition as column name = column gname
it should be

 c.execute("SELECT guardstart FROM guardwatch WHERE name=?", gname)

[nilamo]

def guard_time_distance(name,time):
    c.execute("SELECT guardstart FROM guardwatch WHERE name = name")
    data = c.fetchall()
    lastguard = data[-1]
    print( int(lastguard + 3))

Let's rename some of those variables to make it a little clearer:

def guard_time_distance(name,time):
    c.execute("SELECT guardstart FROM guardwatch WHERE name = ?", name)
    results = c.fetchall()
    lastrow = results[-1]
    lastguard = lastrow[0]
    print( int(lastguard + 3))

context.fetchall() returns a list of rows.  Each row is a list of columns.