Python Forum
mySQL Database error not resolving.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
mySQL Database error not resolving.
#1
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'
Reply
#2
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.
Reply
#3
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,))
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Error creating database with python and form? shams 3 381 Aug-02-2021, 02:00 PM
Last Post: deanhystad
  MYSQL.CONNECTOR ERROR DB1 8 968 Jul-23-2021, 03:31 AM
Last Post: DB1
  pool mysql error - not catch by try\except? korenron 1 392 Jul-05-2021, 11:26 AM
Last Post: ibreeden
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 440 Jul-02-2021, 02:19 PM
Last Post: xtc14
  UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 error from Mysql call AkaAndrew123 1 932 Apr-28-2021, 08:16 AM
Last Post: AkaAndrew123
  Problem updating value in MySQL database dangermaus33 1 501 Nov-24-2020, 08:32 PM
Last Post: dangermaus33
  chatterbot utf-8 errors with mysql database isolatedastronaut 0 556 Nov-08-2020, 06:54 AM
Last Post: isolatedastronaut
  Error in Mysql Client when upgrading Django Abi 0 611 Sep-21-2020, 06:11 AM
Last Post: Abi
  Mysql-Python Connectivity Error aryamanurs 2 1,068 Apr-29-2020, 02:21 PM
Last Post: aryamanurs
  MYSQL how to assign a table for each user in my username table database? YoshikageKira 1 1,389 Dec-26-2019, 05:57 AM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020