Python Forum
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()
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'



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 ="+name
I 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,))