I'm coding a student management program with tkinter for gui and sqlite for the database, and i get this error when i try to uptade an info (im a junior, sorry for the messy code lol, and texts might seem weird its because i translated it):
Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.11_3.11.2288.0_x64__qbz5n2kfra8p0\Lib\tkinter\__init__.py", line 1967, in __call__
return self.func(*args)
^^^^^^^^^^^^^^^^
File "C:\Users\berat\Desktop\Ogrenci_kayit_sistemi\test.py", line 431, in update_response
update_record();
^^^^^^^^^^^^^^^
File "C:\Users\berat\Desktop\Ogrenci_kayit_sistemi\test.py", line 422, in update_record
cursor.execute(update_query, updated_values + [result[0]])
sqlite3.OperationalError: near "WHERE": syntax error
That error occurs here:
def update_record():
updated_values = [var.get() for var in entry_vars]
update_query = "UPDATE students SET {} WHERE id = ?".format(
", ".join(f"{column[0]} = ?" for column in cursor.description if column[0] != "photo")
)
cursor.execute(update_query, updated_values + [result[0]])
conn.commit()
result_window.destroy()
def update_response():
response_update = messagebox.askquestion("confirm", "gonna update")
if response_update == "yes":
update_record();
messagebox.showinfo("info", "success")
elif response_update == "no":
messagebox.showinfo("info","cancelled")
and here is the all codes that is related to update screen and other methods:
def search_student():
global student_id
student_id = entry_ara_student_id.get()
cursor.execute("SELECT id, name, surname, gender, b_date, b_place, phone, adress, student_id, class, section, price, installment, reg_date FROM students WHERE student_id=?",
(student_id,))
result = cursor.fetchone()
if result:
show_search_result(result)
else:
messagebox.showinfo("info", "no data.")
def show_search_result(result):
global photo_update
result_window = Toplevel(root)
result_window.title("Data edit")
entry_vars = []
for i, column_name in enumerate(cursor.description):
if column_name[0] == "photo": #skipped
continue
label = Label(result_window, text=column_name[0])
label.grid(row=2+i, column=0, padx=5, pady=5, sticky="e")
if column_name[0] in ["id","reg_date","price","installments","student_id"]:
var = StringVar(result_window)
var.set(result[i])
entry = Entry(result_window, width=20, textvariable=var, state='disabled')
elif column_name[0] in ["gender", "class", "section", "installments"]:
values = ["Male", "Female"] if column_name[0] == "gender" else list(range(1, 9)) if column_name[0] == "class" else ["A", "B", "C", "D", "E", "F"] if column_name[0] == "section" else list(range(1, 9))
var = StringVar(result_window)
var.set(result[i])
entry = Combobox(result_window, textvariable=var, values=values, state="readonly")
else:
var = StringVar(result_window)
var.set(result[i])
entry = Entry(result_window, width=20, textvariable=var)
entry.grid(row=2+i, column=1, padx=5, pady=5, sticky="w")
entry_vars.append(var)
def update_record():
updated_values = [var.get() for var in entry_vars]
update_query = "UPDATE students SET {} WHERE id = ?".format(
", ".join(f"{column[0]} = ?" for column in cursor.description if column[0] != "photo")
)
cursor.execute(update_query, updated_values + [result[0]])
conn.commit()
result_window.destroy()
def update_response():
response_update = messagebox.askquestion("confirm", "gonna update")
if response_update == "yes":
update_record();
messagebox.showinfo("info", "success")
elif response_update == "no":
messagebox.showinfo("info","cancelled")
btn_update = Button(result_window, width=19, text="update record", command=update_response)
btn_update.grid(row=len(cursor.description)+2, column=0, padx=10, pady=5)
def delete_record():
response_delete = messagebox.askquestion("confirm", "gonna delete")
if response_delete == "yes":
cursor.execute("DELETE FROM students WHERE id = ?", (result[0],))
conn.commit()
result_window.destroy()
messagebox.showinfo("info", "deleted")
elif response_delete == "no":
messagebox.showinfo("info", "cancelled")
btn_delete = Button(result_window, width=19, text="delete", command=delete_record)
btn_delete.grid(row=len(cursor.description)+2, column=1, padx=10, pady=5)
def photo_update():
cursor.execute("SELECT photo FROM students WHERE student_id=?", (student_id,))
photo_data = cursor.fetchone()
if photo_data:
image = Image.open(io.BytesIO(photo_data[0]))
target_width = 200
target_height = 200
resized_image = image.resize((target_width, target_height), Image.BILINEAR)
photo = ImageTk.PhotoImage(resized_image)
label_photo.config(image=photo)
label_photo.image = photo
else:
label_photo.config(image='')
label_photo.image = None
label_photo = Label(result_window)
label_photo.grid(row=0,column=0,columnspan=2)
def update_photo():
file_path = filedialog.askopenfilename()
if file_path:
with open(file_path, "rb") as file:
photo_data = file.read()
response_update = messagebox.askquestion("confirm", "gonna update")
if response_update == "yes":
messagebox.showinfo("info", "success")
cursor.execute("UPDATE students SET photo=? WHERE student_id=?", (photo_data, student_id))
conn.commit()
photo_update() #direkt yeniliyor bu şekilde
elif response_update == "no":
messagebox.showinfo("info", "cancelled")
btn_guncelle = Button(result_window, text="update", command=update_photo)
btn_guncelle.grid(row=1,column=0,columnspan=2, padx=10, pady=5)
label_arama = Label(tab2, text="record update",fg="blue")
label_arama.grid(row=0, column=0,padx=10, pady=5,sticky="w")
label_ara_student_id = Label(tab2, text="student id:")
label_ara_student_id.grid(row=1, column=0, padx=10, pady=5,sticky="e")
entry_ara_student_id = Entry(tab2, width=23)
entry_ara_student_id.grid(row=1, column=1, padx=10, pady=5,columnspan=2)
btn_ara = Button(tab2, width=19, text="Sorgula", command=lambda:[search_student(),photo_update()])
btn_ara.grid(row=2, column=1,padx=10, pady=5,columnspan=2)
cursor.description may not be what you want if you have executed photo_update() before executing update_record().
Suggest changing entry_vars to dictionary instead of list, and use it to build the UPDATE SQL statement instead:
def show_search_result(result):
...
entry_vars = {} # use dictionary instead of list
for i, column_name in enumerate(cursor.description):
...
entry_vars[column_name[0]] = var
def update_record():
updated_values = [var.get() for var in entry_vars.values()]
update_query = "UPDATE students SET {} WHERE id = ?".format(
", ".join(f"{column} = ?" for column in entry_vars)
)
...
...