mySQL Database error not resolving. - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: mySQL Database error not resolving. (/thread-34763.html) |
mySQL Database error not resolving. - cybertooth - Aug-30-2021 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()
RE: mySQL Database error not resolving. - Larz60+ - Aug-30-2021 suggest placing print(f"\nquery: {query}") after all query builds, lines 17, 23, 40, 54 and 61 (current line numbers) It's difficult to see which actually caused the error but line 62 is mentioned, so suspect line 61 is causing error. RE: mySQL Database error not resolving. - ibreeden - Aug-30-2021 I agree with Larz60+. It is certainly line 61. query="DELETE from clinicmaster WHERE labname ="+nameI guess "labname" is a char column. So the value has to be enclosed in single quotes. query="DELETE from clinicmaster WHERE labname = '" + name + "'"But this method is since long deprecated. I don't know exactly which version MySql uses, but in general you should use placeholders (usually a question mark or %s). query="DELETE from clinicmaster WHERE labname = ?" cursor.execute(query, (name,)) |