?
Solved

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?

Posted on 2009-04-16
9
Medium Priority
?
661 Views
Last Modified: 2013-11-05
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 :-)
0
Comment
Question by:lessthan2
  • 6
  • 3
9 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24167248
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
 

Author Comment

by:lessthan2
ID: 24167499
Thank you cxr. I'm testing this out right now.
0
 

Author Comment

by:lessthan2
ID: 24168140
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24168440
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
 

Author Comment

by:lessthan2
ID: 24168755
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 24168831
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
 

Author Comment

by:lessthan2
ID: 24169181
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
 

Author Comment

by:lessthan2
ID: 24169814
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
 

Author Closing Comment

by:lessthan2
ID: 31571212
Great job cxr. If I have other Tkinter, or python questions, I'll look you up.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question