Posts: 37
Threads: 12
Joined: Jul 2021
Hi all I am getting this error" Not all parameters were used in the SQL statement" when I try to insert values in mysql database through a Tkinter GUI. Can somebody help. here is my code
import tkinter as tk
from tkinter import*
import mysql.connector
from tkinter import messagebox
mydb= mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
cursor=mydb.cursor()
def clinic():
clinic_id=ent1.get()
clinic_name=ent2.get()
clinic_address=ent3.get()
clinic_city=ent4.get()
clinic_state=ent5.get()
clinic_phone=ent6.get()
clinic_email=ent7.get()
clinic_website=ent8.get()
sql=("INSERT INTO dentalclinic(clinic_id,clinic_name,clinic_address,clinic_city,clinic_state,clinic_phone,clinic_email,clinic_website)" "VALUES(%s,%s,s%,s%,%s,%s,s%,s%)" )
cursor.execute(sql,(clinic_id,clinic_name,clinic_address,clinic_city,clinic_state,clinic_phone,clinic_email,clinic_website))
mydb.commit()
messagebox.showinfo("Confirmation","Clinic Added")
print("DONE")
return True
cursor.execute('select count(*) from dentalclinic')
next_num = cursor.fetchall()[0][0]
win=Tk()
win.title("ADD Clinic")
win.geometry("500x600")
win.configure(background='light blue')
win.resizable(False,False)
#frm1=Frame(win,bg="light blue")
#frm1.pack(side=tk.LEFT,padx=20)
var1=StringVar()
clinic_id=StringVar()
var2=StringVar()
clinic_name=StringVar()
var3=StringVar()
clinic_address=StringVar()
var4=StringVar()
clinic_city=StringVar()
var5=StringVar()
clinic_state=StringVar()
var6=StringVar()
clinic_phone=StringVar()
var7=StringVar()
clinic_email=StringVar()
var8=StringVar()
clinic_website=StringVar()
##cursor.execute('select (clinic_name) from dentalclinic')
##cname=cursor.fetchall()
#cname=StringVar()
#cname.set(sql1)
label= Label(win,text= "Clinic Details",font=('Helvetica 20 italic'),bg="light blue")
label.grid(row=0,column=2,padx=10,pady=10)
#var1['value']=c_name
label1=Label(win,textvariable=var1,bg="light blue")
var1.set("Clinic ID. ")
label1.grid(row=2,column=1,padx=10,pady=10)
ent1=Entry(win,textvariable=clinic_id,width=10)
clinic_id.set(next_num+1)
ent1.grid(row=2,column=2,sticky=tk.W,padx=10,pady=10)
label2=Label(win,textvariable=var2,bg="light blue")
var2.set("Clinic Name")
label2.grid(row=3,column=1,padx=10,pady=10)
ent2=Entry(win,textvariable=clinic_name,width=40)
clinic_name.set(" ")
ent2.grid(row=3,column=2,padx=10,pady=10)
label3=Label(win,textvariable=var3,bg="light blue")
var3.set("Address")
label3.grid(row=4,column=1,padx=10,pady=10)
ent3=Entry(win,textvariable=clinic_address,width=40)
clinic_address.set(" ")
ent3.grid(row=4,column=2,padx=10,pady=10)
label4=Label(win,textvariable=var4,bg="light blue")
var4.set("City")
label4.grid(row=5,column=1,padx=10,pady=10)
ent4=Entry(win,textvariable=clinic_city,width=40)
clinic_city.set(" ")
ent4.grid(row=5,column=2,padx=10,pady=10)
label5=Label(win,textvariable=var5,bg="light blue")
var5.set("State")
label5.grid(row=6,column=1,padx=10,pady=10)
ent5=Entry(win,textvariable=clinic_state,width=40)
clinic_state.set(" ")
ent5.grid(row=6,column=2,padx=10,pady=10)
label6=Label(win,textvariable=var6,bg="light blue")
var6.set("Phone No.")
label6.grid(row=7,column=1,padx=10,pady=10)
ent6=Entry(win,textvariable=clinic_phone,width=40)
clinic_phone.set(" ")
ent6.grid(row=7,column=2,padx=10,pady=10)
label7=Label(win,textvariable=var7,bg="light blue")
var7.set("Email")
label7.grid(row=8,column=1,padx=10,pady=10)
ent7=Entry(win,textvariable=clinic_email,width=40)
clinic_email.set(" ")
ent7.grid(row=8,column=2,padx=10,pady=10)
label8=Label(win,textvariable=var8,bg="light blue")
var8.set("Website")
label8.grid(row=9,column=1,padx=10,pady=10)
ent8=Entry(win,textvariable=clinic_website,width=40)
clinic_website.set(" ")
ent8.grid(row=9,column=2,padx=10,pady=10)
##def clear_text():
## workrequired.set(' ')
## cursor.execute('select count(*) from workrequired')
## next_num = cursor.fetchall()[0][0]
## srno.set(next_num+1)
btn=Button(win, text="ADD",command= lambda:[clinic(), clear_text()])
btn.grid(row=10,column=2,padx=10,pady=10)
win.mainloop()The error message
Output: 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\add clinic.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\add clinic.py", line 141, in <lambda>
btn=Button(win, text="ADD",command= lambda:[clinic(), clear_text()])
File "C:\Users\INDIAN\Desktop\python exercises\order form\add clinic.py", line 21, in clinic
cursor.execute(sql,(clinic_id,clinic_name,clinic_address,clinic_city,clinic_state,clinic_phone,clinic_email,clinic_website))
File "C:\Users\INDIAN\AppData\Local\Programs\Python\Python38-32\lib\site-packages\mysql\connector\cursor.py", line 559, in execute
raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
Posts: 1,822
Threads: 2
Joined: Apr 2017
Have you checked that you have the same number of placeholders as values in the tuple?
Posts: 586
Threads: 1
Joined: Aug 2019
In two placeholders you have "s%" instead of "%s".
"INSERT INTO dentalclinic(clinic_id,clinic_name,clinic_address,clinic_city,clinic_state,clinic_phone,clinic_email,clinic_website)" "VALUES(%s,%s,s%,s%,%s,%s,s%,s%)"
# should be:
"INSERT INTO dentalclinic(clinic_id,clinic_name,clinic_address,clinic_city,clinic_state,clinic_phone,clinic_email,clinic_website) VALUES(%s,%s,%s,%s,%s,%s,s%,s%)" Also you use a second string of the values, which is not as usual. It should be one sql string.
Posts: 37
Threads: 12
Joined: Jul 2021
Still the same error. I cam unable to understand it. the same sql command works fine in my other scripts where i am making 2 entries in one case and 13 entries in second case. i have checked for the number of place holders , and tried different solutions like removing the parantheses etc but still the same error. can some one please clarify this for me.
Posts: 6,201
Threads: 16
Joined: Feb 2020
Oct-12-2021, 02:24 PM
(This post was last modified: Oct-12-2021, 02:24 PM by deanhystad.)
ibreeden did not catch all the s% %s errors.
VALUES(%s,%s,%s,%s,%s,%s,s%,s%)
# oops! ^ With so many %s it is easy to miss the s%. Like trying to find the black and white horse in a herd of zebras. A little bit of white space improves matters.
query = "INSERT INTO dentalclinic(clinic_id, clinic_name, clinic_address, clinic_city, clinic_state, clinic_phone, clinic_email, clinic_website)" \
"VALUES(%s %s %s %s %s %s %s %s)"
values = (clinic_id, clinic_name, clinic_address, clinic_city, clinic_state, clinic_phone, clinic_email, clinic_website)
cursor.execute(query, values)But this is always going to be error prone. It is always best if you can have the computer do the tedious work. If making a window to edit/create database information is a pattern you are going to use multiple times should make a tool that makes it easier. Something like this:
import tkinter as tk
from tkinter import messagebox
import mysql.connector
CLINIC_FORM_TEMPLATE = [
['clinic_id', 'ID.', 10],
['clinic_name', 'Name', 40],
['clinic_address', 'Address', 40],
['clinic_city', 'City', 40],
['clinic_state', 'State', 40],
['clinic_phone', 'Phone Number', 40],
['clinic_email', 'Email', 40],
['clinic_website', 'Website', 40]
]
class Form(tk.Frame):
'''A form consisting of labels and string entries. Build me up using
addField(db_field_id, label_text, entry_width), or all at once
using a template [field1, field2..fieldN]'''
def __init__(self, *args, template=None, font=None, **kwargs):
super().__init__(*args, **kwargs)
self.label_font = font
self.labels = {}
self.entries = {}
self.variables = {}
if template is not None:
self.create_form(template)
def clear(self):
'''Clear all entries'''
for var in self.variables.values():
var.set('')
def add_field(self, id, name, width):
'''Add field to the form.
id is the name of the field. Can be anything, so if you get info from a database use the data field key.
name is what appears in a Label next to the Entry for this field.
width is the width of the entry
'''
row = len(self.entries) # Fields appear in the order they are added, top to bottom.
label = tk.Label(self, text=name, font=self.label_font, bg=self["bg"])
label.grid(row=row, column=0, padx=10, pady=10, sticky='E')
var = tk.StringVar(self, '')
entry = tk.Entry(self, textvariable=var, width=width)
entry.grid(row=row, column=1, padx=10, pady=10, sticky='W')
self.labels[id] = label # You can get Label, Entry and StringVariable using id as a key.
self.entries[id] = entry
self.variables[id] = var
def create_form(self, template):
'''Add multiple fields to form. template is a list of field lists'''
for field in template:
self.add_field(*field)
@property
def values(self):
'''Return values for all my fields. Appropriate for
use in INSERT INTO db(...) VALUES(..) values'''
return [var.get() for var in self.variables.values()]
@values.setter
def values(self, values):
'''Set values for all my fields. Can use this to update form to display a database entry.'''
for value, var in zip(values, self.variables.values()):
var.set(value)
def fields(self):
'''Return list of field IDs'''
return list(self.entries.keys())
def add_clinic(form):
'''Call me when the Add Clinic button is pressed. I insert data into database using values from form'''
# Have the computer do the tedious work.
fields = form.fields()
request = ''.join([
'INSERT INTO dentalclinic(',
', '.join(fields),
') VALUES(',
', '.join(['%s']*len(fields)),
')'])
cursor.execute(request, form.values)
mydb.commit()
form.clear()
messagebox.showinfo("Confirmation", "Clinic Added")
mydb = mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
cursor = mydb.cursor()
cursor.execute('select count(*) from dentalclinic')
num_clinics = cursor.fetchall()[0][0]
win = tk.Tk()
win.title("ADD Clinic")
win.configure(background='light blue')
form = Form(win, background='light blue', template=CLINIC_FORM_TEMPLATE)
form.grid(row=0, column=0, padx=10, pady=10)
tk.Button(win, text="Add Clinic", command=lambda:add_clinic(form)).grid(row=1, column=0, padx=10, pady=10)
win.mainloop()Form is a class that knows how to make a frame filled with labels and entries. It doesn't know what any of the labels say or what any of the entries are used for, but that isn't important for a form. A form should have controls to display/enter/edit text and a label to help identify the control. Form has a values property that can be used to get or set all the field values at once.
Using Form you can quickly create windows to display/view/edit database information. You could make a viewer window consisting of a listbox that lets you choose a clinic by the ID or name and displays the clinic info in a form. To make a viewer for a different database all you need to do is create a template using the fields from the database.
I do not have a database so I could not test any of the mysql parts. I did comment out the database parts and the tkinter stuff works.
Posts: 37
Threads: 12
Joined: Jul 2021
(Oct-12-2021, 02:24 PM)deanhystad Wrote: ibreeden did not catch all the s% %s errors.
VALUES(%s,%s,%s,%s,%s,%s,s%,s%)
# oops! ^ With so many %s it is easy to miss the s%. Like trying to find the black and white horse in a herd of zebras. A little bit of white space improves matters.
query = "INSERT INTO dentalclinic(clinic_id, clinic_name, clinic_address, clinic_city, clinic_state, clinic_phone, clinic_email, clinic_website)" \
"VALUES(%s %s %s %s %s %s %s %s)"
values = (clinic_id, clinic_name, clinic_address, clinic_city, clinic_state, clinic_phone, clinic_email, clinic_website)
cursor.execute(query, values)But this is always going to be error prone. It is always best if you can have the computer do the tedious work. If making a window to edit/create database information is a pattern you are going to use multiple times should make a tool that makes it easier. Something like this:
import tkinter as tk
from tkinter import messagebox
import mysql.connector
CLINIC_FORM_TEMPLATE = [
['clinic_id', 'ID.', 10],
['clinic_name', 'Name', 40],
['clinic_address', 'Address', 40],
['clinic_city', 'City', 40],
['clinic_state', 'State', 40],
['clinic_phone', 'Phone Number', 40],
['clinic_email', 'Email', 40],
['clinic_website', 'Website', 40]
]
class Form(tk.Frame):
'''A form consisting of labels and string entries. Build me up using
addField(db_field_id, label_text, entry_width), or all at once
using a template [field1, field2..fieldN]'''
def __init__(self, *args, template=None, font=None, **kwargs):
super().__init__(*args, **kwargs)
self.label_font = font
self.labels = {}
self.entries = {}
self.variables = {}
if template is not None:
self.create_form(template)
def clear(self):
'''Clear all entries'''
for var in self.variables.values():
var.set('')
def add_field(self, id, name, width):
'''Add field to the form.
id is the name of the field. Can be anything, so if you get info from a database use the data field key.
name is what appears in a Label next to the Entry for this field.
width is the width of the entry
'''
row = len(self.entries) # Fields appear in the order they are added, top to bottom.
label = tk.Label(self, text=name, font=self.label_font, bg=self["bg"])
label.grid(row=row, column=0, padx=10, pady=10, sticky='E')
var = tk.StringVar(self, '')
entry = tk.Entry(self, textvariable=var, width=width)
entry.grid(row=row, column=1, padx=10, pady=10, sticky='W')
self.labels[id] = label # You can get Label, Entry and StringVariable using id as a key.
self.entries[id] = entry
self.variables[id] = var
def create_form(self, template):
'''Add multiple fields to form. template is a list of field lists'''
for field in template:
self.add_field(*field)
@property
def values(self):
'''Return values for all my fields. Appropriate for
use in INSERT INTO db(...) VALUES(..) values'''
return [var.get() for var in self.variables.values()]
@values.setter
def values(self, values):
'''Set values for all my fields. Can use this to update form to display a database entry.'''
for value, var in zip(values, self.variables.values()):
var.set(value)
def fields(self):
'''Return list of field IDs'''
return list(self.entries.keys())
def add_clinic(form):
'''Call me when the Add Clinic button is pressed. I insert data into database using values from form'''
# Have the computer do the tedious work.
fields = form.fields()
request = ''.join([
'INSERT INTO dentalclinic(',
', '.join(fields),
') VALUES(',
', '.join(['%s']*len(fields)),
')'])
cursor.execute(request, form.values)
mydb.commit()
form.clear()
messagebox.showinfo("Confirmation", "Clinic Added")
mydb = mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
cursor = mydb.cursor()
cursor.execute('select count(*) from dentalclinic')
num_clinics = cursor.fetchall()[0][0]
win = tk.Tk()
win.title("ADD Clinic")
win.configure(background='light blue')
form = Form(win, background='light blue', template=CLINIC_FORM_TEMPLATE)
form.grid(row=0, column=0, padx=10, pady=10)
tk.Button(win, text="Add Clinic", command=lambda:add_clinic(form)).grid(row=1, column=0, padx=10, pady=10)
win.mainloop()Form is a class that knows how to make a frame filled with labels and entries. It doesn't know what any of the labels say or what any of the entries are used for, but that isn't important for a form. A form should have controls to display/enter/edit text and a label to help identify the control. Form has a values property that can be used to get or set all the field values at once.
Using Form you can quickly create windows to display/view/edit database information. You could make a viewer window consisting of a listbox that lets you choose a clinic by the ID or name and displays the clinic info in a form. To make a viewer for a different database all you need to do is create a template using the fields from the database.
I do not have a database so I could not test any of the mysql parts. I did comment out the database parts and the tkinter stuff works.
Thanks. Will try to implement it. Though i don't understand classes right now but am trying to read about them .
|
