How do I grab user input from text boxes on a Python/Tkinter form and use it to build a query to a MySQL db?

I need to build or find a Tkinter form that can be used to grab input from a user and use that to query a mysql database.
For this task, let's say the db has a table called 'user' and contains the following fields:
fld1, fld2, fld3
Initially, if we can just build it around basic textbox type widgets. We can improve and upgrade the form after we learn to build it.
The main area I'm having trouble with is how to iterate through the entry fields on the form and then populate the corresponding field in the database.
I AM able to make a connection to the db - no trouble there.
This problem is really impeding progress, so I'll award high points to whomever can enlighten me :-)
lessthan2Asked:
Who is Participating?
 
Roger BaklundCommented:
Line 45 is missing a ) at the end:
  def do_insert(self):
        global curs
        sql = "insert into vendor (vendID,vendName) values(%s,'%s')"%( 
               self.fields['vendID'].get(),self.fields['vendName'].get() ) 
        curs.execute(sql)

Open in new window

0
 
Roger BaklundCommented:
Here is a simple example:
from Tkinter import *
 
class MyApp:
  def __init__(self):
    window = Tk()
    window.title('My application')
    
    frame = Frame(window)
    frame.pack()
    
    self.fields = {}
    
    l = Label(frame, text="Name:")
    l.grid(row=0, column=0)
    self.fields['name'] = Entry(frame)
    self.fields['name'].grid(row=0, column=1)
     
    l = Label(frame, text="Email:")
    l.grid(row=2, column=0)
    self.fields['email'] = Entry(frame)
    self.fields['email'].grid(row=2, column=1)
        
    submitbtn = Button(frame, text="Insert", command=self.do_insert)
    submitbtn.grid(row=11, column=0)
    
    clearbtn = Button(frame, text="Clear", command=self.do_clear)
    clearbtn.grid(row=11, column=1)
    
    window.mainloop()
 
  def do_clear(self):
    self.fields['name'].delete(0,END)
    self.fields['email'].delete(0,END)
 
  def do_insert(self):
    sql = "insert into MyTable set name='%s',email='%s'"%(
          self.fields['name'].get(),self.fields['email'].get())
    print sql
  
if __name__=="__main__":
    MyApp()

Open in new window

0
 
lessthan2Author Commented:
Thank you cxr. I'm testing this out right now.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lessthan2Author Commented:
This will end up giving me the basis for building all the forms I'll need, but I still need a little help with the way it creates the query.  You can see in the snippet that I added the connection and cursor, and I swapped a few values to point this to a real mysql DB and table. The table has 2 fields - vendID, and vendName.

When I launch the form and fill in the 2 fields, it outputs like this:
insert into vendor set vendID='3',vendName='bigco'
I need the query to output like this:
INSERT INTO vendor (vendID,vendName) VALUES (1, 'CDW');
I know this is a trivial thing, but I just don't know how to format this thing to make it output the way I need it and need a little bump in the right direction.
Thanks a bunch cxr
#!/usr/bin/python
from Tkinter import *
import MySQLdb
 
## modified the following... 
## Connect to DB
conn = MySQLdb.connect(host='localhost' , user='root' , passwd='XXXX', db='mind0')
##Create a cursor
curs = conn.cursor() 
 
class MyApp:
  def __init__(self):
    window = Tk()
    window.title('My application')
    
    frame = Frame(window)
    frame.pack()
    
    self.fields = {}
    
    l = Label(frame, text="vendID:")
    l.grid(row=0, column=0)    
    self.fields['vendID'] = Entry(frame)
    self.fields['vendID'].grid(row=0, column=1)    
     
    l = Label(frame, text="vendName:")
    l.grid(row=2, column=0)    
    self.fields['vendName'] = Entry(frame)
    self.fields['vendName'].grid(row=2, column=1)    
        
    submitbtn = Button(frame, text="Insert", command=self.do_insert)
    submitbtn.grid(row=11, column=0)
    
    clearbtn = Button(frame, text="Clear", command=self.do_clear)
    clearbtn.grid(row=11, column=1)
    
    window.mainloop()
 
  def do_clear(self):
  
    self.fields['vendID'].delete(0,END)
    self.fields['vendName'].delete(0,END)
## modified the following...   
  def do_insert(self): 
    curs.execute(sql = "insert into vendor set vendID='%s',vendName='%s'"%(
          self.fields['vendID'].get(),self.fields['vendName'].get())
    print sql             
  
if __name__=="__main__":
    MyApp()

Open in new window

0
 
Roger BaklundCommented:
Both formats are valid for inserts. They do the exact same thing.

Change this:

curs.execute(sql = "insert into vendor set vendID='%s',vendName='%s'"%(
          self.fields['vendID'].get(),self.fields['vendName'].get())

... into this:

global curs
sql = "insert into vendor (vendID,vendName) values(%s,'%s')"%(
          self.fields['vendID'].get(),self.fields['vendName'].get()
curs.execute(sql)
0
 
lessthan2Author Commented:
I think we're getting close,but not quite there yet.
Take a look at the code. Is my indentation correct for the stuff I added? When I run it, I get:
^^^^^^^^^^^^^^^^^^
[root@smeg code]# ./entryfrm4d.py
  File "./entryfrm4d.py", line 52
    curs.execute(sql)
       ^
SyntaxError: invalid syntax
^^^^^^^^^^^^^^^^

#!/usr/bin/python
from Tkinter import *
import MySQLdb
 
## Connect to DB
conn = MySQLdb.connect(host='localhost' , user='root' , passwd='wolf', db='mind0')
##Create a cursor
curs = conn.cursor() 
 
class MyApp:
  def __init__(self):
    window = Tk()
    window.title('My application')
    
    frame = Frame(window)
    frame.pack()
    
    self.fields = {}
    
    l = Label(frame, text="vendID:")
    l.grid(row=0, column=0)    
    self.fields['vendID'] = Entry(frame)
    self.fields['vendID'].grid(row=0, column=1)    
     
    l = Label(frame, text="vendName:")
    l.grid(row=2, column=0)   
    self.fields['vendName'] = Entry(frame)
    self.fields['vendName'].grid(row=2, column=1)    
        
    submitbtn = Button(frame, text="Insert", command=self.do_insert)
    submitbtn.grid(row=11, column=0)
    
    clearbtn = Button(frame, text="Clear", command=self.do_clear)
    clearbtn.grid(row=11, column=1)
    
    window.mainloop()
 
  def do_clear(self):  
    self.fields['vendID'].delete(0,END)
    self.fields['vendName'].delete(0,END)
 
  def do_insert(self):
  	global curs
	sql = "insert into vendor (vendID,vendName) values(%s,'%s')"%( 
		self.fields['vendID'].get(),self.fields['vendName'].get()
	curs.execute(sql)
                   
if __name__=="__main__":
    MyApp()

Open in new window

0
 
lessthan2Author Commented:
Ok cxr, I added the missing ')', a print statement, and a space after VALUE and ran it. The query it generated is perfect. For some reason, the record doesn't get added. However, I entered the generated query directly into mysql, which did work.  If you don't mind, could you take another look at the whole thing and see if you can spot where it's failing? I'm doing the same thing here and hopefully we can find the culprit.
Thanks  (you'll get some bonus points too)
#!/usr/bin/python
from Tkinter import *
import MySQLdb
 
## Connect to DB
conn = MySQLdb.connect(host='localhost' , user='root' , passwd='wolf', db='mind0')
##Create a cursor
curs = conn.cursor() 
 
class MyApp:
  def __init__(self):
    window = Tk()
    window.title('My application')
    
    frame = Frame(window)
    frame.pack()
    
    self.fields = {}
    
    l = Label(frame, text="vendID:")
    l.grid(row=0, column=0)    
    self.fields['vendID'] = Entry(frame)
    self.fields['vendID'].grid(row=0, column=1)    
     
    l = Label(frame, text="vendName:")
    l.grid(row=2, column=0)   
    self.fields['vendName'] = Entry(frame)
    self.fields['vendName'].grid(row=2, column=1)    
        
    submitbtn = Button(frame, text="Insert", command=self.do_insert)
    submitbtn.grid(row=11, column=0)
    
    clearbtn = Button(frame, text="Clear", command=self.do_clear)
    clearbtn.grid(row=11, column=1)
    
    window.mainloop()
 
  def do_clear(self):  
    self.fields['vendID'].delete(0,END)
    self.fields['vendName'].delete(0,END)
 
  def do_insert(self):
  	global curs
	sql = "insert into vendor (vendID,vendName) values (%s,'%s');"%( 
		self.fields['vendID'].get(),self.fields['vendName'].get())
	curs.execute(sql)
	print sql
                   
if __name__=="__main__":
    MyApp()

Open in new window

0
 
lessthan2Author Commented:
Nevermind my last question. I aimed the form at a different DB, added an additional field and retried. It works. I must have a typo somewhere in the first code block.
Thanks for your help.
0
 
lessthan2Author Commented:
Great job cxr. If I have other Tkinter, or python questions, I'll look you up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.