asked on

How can I search MySQL table from a Tkinter form?

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

TheVeee

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")

lessthan2

ASKER

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

lessthan2

ASKER

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

ASKER CERTIFIED SOLUTION

TheVeee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lessthan2

ASKER

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.

lessthan2

ASKER

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