Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I search MySQL table from a Tkinter form?

Posted on 2009-04-22
6
Medium Priority
?
2,955 Views
Last Modified: 2012-05-06
CXR (expert) has helped me work out some Tkinter forms to be used to INSERT rows into, and  UPDATE a MySQL inventory database. I adopted his advice to one of my smaller tables (2 fields). The code block below works perfectly, but now we need to create some search functionality that can be used for a larger table (14 fields).
Specifically, I'd like to be able to enter all, or part of a SELECT query in a "Search box", hit a button, and have it return the record that matches the query.
Then, I need to be able to UPDATE any of the fields in the record returned by the query.
I've attached a file named 'item-frm-3.txt'. This is the actual main form to which I'd like to add the search/UPDATE functionality.
As always, thank you very much for the help and the time you save us.
#!/usr/bin/python
from Tkinter import *
import MySQLdb
#-----------------------------------------------------------
## Connect to DB
conn = MySQLdb.connect(host='localhost', user='root', db='mind0')
##Create a cursor
curs = conn.cursor() 
## *****************************************************
# Root level form
class MyApp:
  def __init__(self):
    window = Tk()
    window.title('Data Entry Form')   
    frame = Frame(window)
    frame.pack()
## BEGIN Fields ***********************************************     
    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)
## END Fields ************************************************    
 
## BEGIN Buttons ########
    clearbtn = Button(frame, text="Clear", command=self.do_clear)
    clearbtn.grid(row=11, column=0)
        
    submitbtn = Button(frame, text="Submit", command=self.do_insert)
    submitbtn.grid(row=11, column=1)
 
    updatebtn = Button(frame, text="Update", command=self.do_update)
    updatebtn.grid(row=11, column=2)
## END Buttons ##########
    
    window.mainloop()
 
## CLEAR, INSERT, and UPDATE functions
  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
 
  def do_update(self):
    global curs
    sql = "update vendor set vendName='%s' where vendID='%s';"%( 
        self.fields['vendName'].get(),
        self.fields['vendID'].get())
    curs.execute(sql)
#    print sql
                   
if __name__=="__main__":
    MyApp()

Open in new window

item-frm-3.txt
0
Comment
Question by:lessthan2
  • 4
  • 2
6 Comments
 
LVL 5

Expert Comment

by:TheVeee
ID: 24209449
Looks like you already have the update example, you just need to use it for the next update.  Lets say for example your table also contains field A, Field B and you want to add those for the update too...  Im a java coder by trade, but princples below in psuedo code...

 def do_update(self):
    global curs
    sql = "update vendor set vendName='%s', fieldA='%s', fieldB='%s' where vendID='%s';"%(
        self.fields['vendName'].get(),
        self.fields['vendID'].get())
    curs.execute(sql)

Your select fields would be:
 def do_select(self):
    global curs
    sql = "select fieldA,fieldB where vendID='%s';"%(
    rsobj = curs.execute(sql)
    //rsobj is just the guess name, but you will need to define it first as sql resultset type first, then
   formfieldA = rsobj.getString("fieldA")
   formfieldB = rsobj.getString("fieldB")

0
 

Author Comment

by:lessthan2
ID: 24210164
Veee, I'm not a programmer, so the pseudo code may not be detailed enough for me. I will try your advice though. Thanks.
0
 

Author Comment

by:lessthan2
ID: 24210925
Veee (or anyone else interested),
I know my code block below is incorrect, though I think I understand basically what you mean. I'm just struggling with the syntax. A result set has to be defined -something like "def rsobj()", then the row must be fetched and fields populated.  I've commented my definitely wrong definition line below. Following that is my sql statement, which also may be wrong. I also attached the full form in case that helps.

Please don't give up on me guys - I've been at this stuff for days and I'm determined to conquer it one way or another.
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
  def do_select(self):
  	global curs
	def rsobj()  ##### incorrect !!!!!!!
	sql = "SELECT vendID,vendName FROM vendor WHERE vendID='%s';"%( 
		self.fields['vendID'].get(),
		self.fields['vendName'].get())
	curs.execute(sql)
	print sql
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Open in new window

vendor-frm.txt
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 5

Accepted Solution

by:
TheVeee earned 1500 total points
ID: 24214449
Unfortunely IM not a phython programmer, java, c++, visual basic,  javacript and a few more, but not python unfortunely... for exact coding syntax i wont be able to provide.... sorry!
0
 

Author Comment

by:lessthan2
ID: 24214500
Not a problem Veee. I worked at my issue last night and your answer did actually help get me a little farther towards full solution, so I thank you for that.
0
 

Author Closing Comment

by:lessthan2
ID: 31573450
Though this answer wasn't exactly what I needed, it did help me to head in the right direction. The Veee tried to help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 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