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
  Resolving ImportError: No module named gdb (Python in C++) mandaxyz 3 1,313 Oct-04-2023, 02:43 PM
Last Post: mandaxyz
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  error 1102 (42000) incorrect database name 's' Anldra12 4 1,651 Jun-08-2022, 09:00 AM
Last Post: Anldra12
  Mysql Syntax error in pymysql ilknurg 4 2,290 May-18-2022, 06:50 AM
Last Post: ibreeden
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,682 Feb-09-2022, 09:55 AM
Last Post: ibreeden
  Error creating database with python and form? shams 3 2,328 Aug-02-2021, 02:00 PM
Last Post: deanhystad
  MYSQL.CONNECTOR ERROR DB1 8 3,776 Jul-23-2021, 03:31 AM
Last Post: DB1
  pool mysql error - not catch by try\except? korenron 1 2,101 Jul-05-2021, 11:26 AM
Last Post: ibreeden
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 2,115 Jul-02-2021, 02:19 PM
Last Post: xtc14
  UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 error from Mysql call AkaAndrew123 1 3,386 Apr-28-2021, 08:16 AM
Last Post: AkaAndrew123

Forum Jump:

User Panel Messages

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