Aug-30-2021, 07:10 AM
Hi everyone . I get an error. " Unknown column 'xyz' in 'where clause' ". I have tried many options but am unable to solve it. I have triple checked the code and everything seems right. I am able to add new entries from add query. although the new entry shows in the tree view table it is not showing in mysql database table. more over the update is also not working. looks like some issue in communicating with the mySql database. can you please guide me.
import tkinter as tk
from tkinter import*
from tkinter import ttk
from tkinter import messagebox
import mysql.connector
mydb= mysql.connector.connect(host="localhost",user="root", password="mudit",database="clinicmaster",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
def update(rows):
trv.delete(*trv.get_children())
for i in rows:
trv.insert('','end',values=i)
def search():
q2=q.get()
query="SELECT labname,Lphno,lemail from clinicmaster WHERE labname LIKE '%"+q2+"%'or Lphno LIKE '%"+q2+"%'"
cursor.execute(query)
rows=cursor.fetchall()
update(rows)
def clear():
query=" SELECT labname,Lphno,lemail from clinicmaster"
cursor.execute(query)
rows=cursor.fetchall()
update(rows)
def getrow(event):
rowid=trv.identify_row(event.y)
item=trv.item(trv.focus())
t1.set(item['values'][0])
t2.set(item['values'][1])
t3.set(item['values'][2])
def update_lab():
name=t1.get()
phno=t2.get()
email=t3.get()
if messagebox.askyesno("Confirm Please?", " Are you sure you want to update this Lab Name ?"):
query="UPDATE clinicmaster SET Lphno=%s,lemail=%s where labname=%s"
cursor.execute(query,(name,phno,email))
clear()
else:
return True
def add_lab():
name=t1.get()
phno=t2.get()
email=t3.get()
query="INSERT INTO clinicmaster(labname,Lphno,lemail)" "VALUES(%s,%s,%s)"
cursor.execute(query,(name,phno,email))
clear()
def delete_lab():
name=t1.get()
if messagebox.askyesno("Confirm Delete?", " Are you sure you want to delete this Lab Name ?"):
query="DELETE from clinicmaster WHERE labname ="+name
cursor.execute(query)
clear()
else:
return True
#mydb= mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
#cursor=mydb.cursor()
win=Tk()
win.title("ADD Lab")
#photo=PhotoImage(file="C:\\Users\\INDIAN\\Desktop\\python exercises\\order form\\tooth1.png")
#win.image=photo
win.geometry("800x700")
win.configure(background='light blue')
win.resizable(False,False)
q=StringVar()
t1=StringVar()
t2=StringVar()
t3=StringVar()
wrapper1= LabelFrame(win,text= "Lab List")
wrapper2= LabelFrame(win,text="Search")
wrapper3= LabelFrame(win,text="Lab Data")
wrapper1.pack(fill="both",expand="yes",padx=20,pady=10)
wrapper2.pack(fill="both",expand="yes",padx=20,pady=10)
wrapper3.pack(fill="both",expand="yes",padx=20,pady=10)
trv=ttk.Treeview(wrapper1,columns=(1,2,3), show="headings",height="6")
trv.pack()
trv.heading(1,text="Lab Name")
trv.heading(2,text="Lab Phone No.")
trv.heading(3, text="Email id")
trv.bind('<Double 1>',getrow)
query="SELECT labname,Lphno,lemail from clinicmaster"
cursor.execute(query)
rows=cursor.fetchall()
update(rows)
#search section
lbl=Label(wrapper2,text="Search")
lbl.pack(side=tk.LEFT,padx=10)
ent=Entry(wrapper2,textvariable=q)
ent.pack(side=tk.LEFT,padx=6)
btn=Button(wrapper2,text="Search", command=search)
btn.pack(side=tk.LEFT,padx=6)
cbtn=Button(wrapper2,text="Clear",command=clear)
cbtn.pack(side=tk.LEFT,padx=6)
#user data section
lbl1=Label(wrapper3,text="Lab Name")
lbl1.grid(row=0,column=0,padx=5,pady=3)
ent1=Entry(wrapper3,textvariable=t1)
ent1.grid(row=0,column=1,padx=5,pady=3)
lbl2=Label(wrapper3,text="Lab Phone No,")
lbl2.grid(row=1,column=0,padx=5,pady=3)
ent2=Entry(wrapper3,textvariable=t2)
ent2.grid(row=1,column=1,padx=5,pady=3)
lbl3=Label(wrapper3,text="Lab Email")
lbl3.grid(row=2,column=0,padx=5,pady=3)
ent3=Entry(wrapper3,textvariable=t3)
ent3.grid(row=2,column=1,padx=5,pady=3)
up_btn=Button(wrapper3,text="Update", command=update_lab)
add_btn=Button(wrapper3,text="Add New", command=add_lab)
delete_btn=Button(wrapper3,text="Delete", command=delete_lab)
add_btn.grid(row=4,column=0,padx=5,pady=3)
up_btn.grid(row=4,column=1,padx=5,pady=3)
delete_btn.grid(row=4,column=2,padx=5,pady=3)
win.mainloop()Error:Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:43:08) [MSC v.1926 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license()" for more information.
>>>
=== RESTART: C:\Users\INDIAN\Desktop\python exercises\order form\untitled.py ===
Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\INDIAN\AppData\Local\Programs\Python\Python38-32\lib\tkinter\__init__.py", line 1883, in __call__
return self.func(*args)
File "C:\Users\INDIAN\Desktop\python exercises\order form\untitled.py", line 62, in delete_lab
cursor.execute(query)
File "C:\Users\INDIAN\AppData\Local\Programs\Python\Python38-32\lib\site-packages\mysql\connector\cursor.py", line 568, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\INDIAN\AppData\Local\Programs\Python\Python38-32\lib\site-packages\mysql\connector\connection.py", line 846, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\INDIAN\AppData\Local\Programs\Python\Python38-32\lib\site-packages\mysql\connector\connection.py", line 656, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'xyz' in 'where clause'