Treeview Select and Update MySql Product table record


Youtube Live session on Tkinter

Tkinter Restaurant Management V-1 V-2 V-3 Report

Restaurant Management select and edit product



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.
my_update()Reads the inputs and update MySQL table.
show_msg()Display Message to user with time delay ( details of show_msg() )

Connection to MySQL and creating tables

Read the connection details and download the SQL dump to create tables from Restaurant-V-3

show_item(cat) : Display the records in Treeview

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

Full code is here
Restaurant Management V-1 V-2 (Database integration) V-3(invoice Generation) Combobox for selection of items Day wise Report Installing Tables
More Projects using Tkinter
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer