Treeview Select and Update MySql Product table record
Select the row to Edit and update records from MySQL table using Tkinter Treeview
Based on the input category, the records will be collected from MySQL product table( plus2_products ) and shown inside a Treeview. User can select any row and details of the record will be displayed for edit and update. Once updated ( in MySQL table ) fresh data will be taken from the product table showing the changes.
Functions used
show_items(cat)
Collects data from MySQL table and populate the Treeview.
data_collect()
On selection of row of Treeview, populate the Entry widgets for Edit.
This function will receive the category value as integer ( 1, 2 or 3 for Breakfast, Lunch and dinner ) and collect the data from plus2_products table. Before displaying ( inserting ) the data inside treeview all previous child nodes ( rows of data ) is removed. This function is called on every update of rows of data to reflect the changes.
def show_items(cat): # Populating the treeview with records
for item in trv.get_children(): # loop all child items
trv.delete(item) # delete them
query="SELECT * FROM plus2_products WHERE p_cat=%s"
r_set=my_conn.execute(query,cat) # get the record set from table
for dt in r_set: # add data to treeview
trv.insert("",'end',iid=dt[0],text=dt[0],
values=(dt[1],dt[2],dt[3],dt[4],dt[5]))
data_collect(self) : Collect and show data
On selection of any node or row of the treeview, this function is triggered.
trv.bind("<<TreeviewSelect>>", data_collect)
Inside this function data_collect() first we read the iid ( p_id ) of the selected node.
The value of the selected node is our p_id ( product id ) , using this p_id as parameter in WHERE condition we will collect the data from MySQL product table.
query="SELECT * from plus2_products WHERE p_id=%s"
row=my_conn.execute(query,selected)
s = row.fetchone() # row details as tuple
Using the elements of s we will assign value to our variables to display inside edit widgets.
def data_collect(self): # collect data to display for edit
selected=trv.focus() # gets the product id or p_id
if(selected != None ):
query="SELECT * from plus2_products WHERE p_id=%s"
row=my_conn.execute(query,selected)
s = row.fetchone() # row details as tuple
if(s != None):
p_name.set(s[1])
unit.set(s[2])
price.set(s[3])
e_p_cat.set(my_cats[s[4]]) # set the category value
available.set(s[5])
b_update.config(state='normal')
b_update.config(command=lambda:my_update(selected))
else:
b_update.config(state='disabled')
Note the last line inside the if block where we are passing the p_id value ( selected ) to the function my_update(p_id), based on the selection of rows this value will change.
my_update(p_id) : update the table
This function will receive the value of product id (p_id) and reads all the input data as edited by the user.
As the user enters or updates the data, we will be using try except error handling to manage the data updating.
After updating the record, we will reset all the input fields and refresh the Treeview ( trv ) to reflect the new data by calling the show_items() function.
def my_update(p_id): # receives the p_id on button click to update
try:
data=(p_name.get(),unit.get(),price.get(),p_cat.get(),available.get(),p_id )
id=my_conn.execute("UPDATE plus2_products SET p_name=%s,unit=%s,\
price=%s,p_cat=%s, available=%s WHERE p_id=%s",data)
#print(data)
except SQLAlchemyError as e:
error=str(e.__dict__['orig'])
msg_display=error
show_msg(msg_display,'error') # send error message
except tk.TclError as e:
msg_display=e.args[0]
show_msg(msg_display,'error') # send error message
else:
msg_display="Number of records updated: " + str(id.rowcount)
show_msg(msg_display,'normal')# success message
p_name.set('') # remove the product name
unit.set('')
price.set(0)
e_p_cat.set('') # set the category value
available.set(5)
r1_v.set(data[3])
show_items(data[3]) # refresh the treeview with fresh data