Select, Edit and Update MySQL Records with Tkinter Treeview

This restaurant-management project displays products from the MySQL plus2_products table inside a Tkinter Treeview. Products can be filtered by Breakfast, Lunch or Dinner.

When the user selects a row, the corresponding product details are loaded into an edit form. After validation, Python updates the MySQL record and reloads the Treeview so the changed data appears immediately.

Select category
      |
      v
Load MySQL products
      |
      v
Select Treeview row
      |
      v
Load selected product into form
      |
      v
Edit + validate
      |
      v
UPDATE MySQL
      |
      v
Reload Treeview

Functions Used in the Project 🔝

MySQL Connection 🔝

The restaurant project can keep its database connection details in my_connect.py, but that module should expose a SQLAlchemy Engine rather than one permanently open connection.

my_connect.py

from sqlalchemy import create_engine

engine=create_engine('mysql+mysqldb://id:pw@localhost/my_db')

Import the Engine in the main application:

from my_connect import engine

See the existing Restaurant Management V-3 and table-installation tutorial for the project database structure.

Map Product Categories 🔝

The database stores category IDs:

CATEGORIES={
    1:'Breakfast',
    2:'Lunch',
    3:'Dinner'
}

Create the reverse mapping:

CATEGORY_ID={
    name:cat_id
    for cat_id,name in CATEGORIES.items()
}

The edit Combobox displays names such as Breakfast, while the UPDATE query stores the corresponding integer category ID.

Create the Product Treeview 🔝

The product ID is shown as a normal data column and is also used internally as the Treeview iid.

tree=ttk.Treeview(
    left_frame,
    columns=('id','name','unit','price','category','available'),
    show='headings',
    selectmode='browse',
    height=15
)

Using show='headings' is simpler here because this is flat tabular data and the special #0 tree column is not required.

show_items(cat): Load Products by Category 🔝

First clear the existing rows:

for item in tree.get_children():
    tree.delete(item)

Then retrieve only the required columns:

query=text('''SELECT p_id,p_name,unit,price,p_cat,available
FROM plus2_products
WHERE p_cat=:cat
ORDER BY p_name,p_id''')

with engine.connect() as conn:
    rows=conn.execute(query,{'cat':cat}).mappings().all()

Insert each row:

for row in rows:
    tree.insert(
        '',
        tk.END,
        iid=str(row['p_id']),
        values=(
            row['p_id'],
            row['p_name'],
            row['unit'],
            row['price'],
            CATEGORIES.get(row['p_cat'],row['p_cat']),
            'Yes' if row['available'] else 'No'
        )
    )

data_collect(): Read the Selected Product 🔝

The older page used:

selected=trv.focus()

For a selection-driven editor, use:

selected=tree.selection()

if not selected:
    clear_form()
    return

p_id=int(selected[0])

The Treeview iid represents the database product ID.

Retrieve the Latest MySQL Values

query=text('''SELECT p_id,p_name,unit,price,p_cat,available
FROM plus2_products
WHERE p_id=:p_id''')

with engine.connect() as conn:
    row=conn.execute(query,{'p_id':p_id}).mappings().first()

Retrieving the record again ensures that the edit form receives the current database values rather than relying only on the displayed Treeview snapshot.

Populate the Edit Form 🔝

selected_id_var.set(str(row['p_id']))
name_var.set(row['p_name'])
unit_var.set(row['unit'])
price_var.set(f"{Decimal(str(row['price'])):.2f}")
category_var.set(CATEGORIES.get(row['p_cat'],''))
available_var.set(int(row['available']))
update_btn.config(state=tk.NORMAL)

The product ID is displayed in a read-only Entry so the user can see which database record is being edited.

Validate the Edited Product Data 🔝

Product name and unit should not be blank:

product_name=name_var.get().strip()
product_unit=unit_var.get().strip()

if not product_name:
    show_msg('Enter a product name.',False)
    return

Validate Price with Decimal

try:
    product_price=Decimal(price_var.get().strip()).quantize(Decimal('0.01'))
except InvalidOperation:
    show_msg('Enter a valid price.',False)
    return

Reject negative prices:

if product_price<0:
    show_msg('Price cannot be negative.',False)
    return

Validate Category

category_id=CATEGORY_ID.get(category_var.get())

if category_id is None:
    show_msg('Select a category.',False)
    return

my_update(): Update the MySQL Product 🔝

The UPDATE statement uses named parameters:

query=text('''UPDATE plus2_products
SET p_name=:name,
    unit=:unit,
    price=:price,
    p_cat=:category,
    available=:available
WHERE p_id=:p_id''')

Execute it inside a transaction:

with engine.begin() as conn:
    result=conn.execute(query,data)

If no exception occurs, the transaction is committed automatically.

Refresh the Correct Category after Update 🔝

If the user moves the product from Breakfast to Lunch, the product should disappear from the Breakfast list and appear under Lunch.

After a successful update:

filter_var.set(category_id)
show_items(category_id)

This moves the view to the updated product's category and reloads fresh database data.

Complete Restaurant Product Edit and Update Program 🔝

import tkinter as tk
from tkinter import ttk
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from my_connect import engine

CATEGORIES={1:'Breakfast',2:'Lunch',3:'Dinner'}
CATEGORY_ID={name:cat_id for cat_id,name in CATEGORIES.items()}
MONEY=Decimal('0.01')

root=tk.Tk()
root.geometry('1000x620')
root.title('Restaurant Product Editor - plus2net')
root.rowconfigure(0,weight=1)
root.columnconfigure(0,weight=3)
root.columnconfigure(1,weight=2)

left_frame=ttk.Frame(root,padding=10)
left_frame.grid(row=0,column=0,sticky='nsew')
left_frame.rowconfigure(0,weight=1)
left_frame.columnconfigure(0,weight=1)

right_frame=ttk.Frame(root,padding=15)
right_frame.grid(row=0,column=1,sticky='nsew')

bottom_frame=ttk.Frame(root,padding=10)
bottom_frame.grid(row=1,column=0,columnspan=2,sticky='ew')

style=ttk.Style(root)
if 'clam' in style.theme_names():
    style.theme_use('clam')
style.configure('Restaurant.Treeview',rowheight=27)
style.configure('Restaurant.Treeview.Heading',font=('Times',11,'bold'))

tree=ttk.Treeview(left_frame,columns=('id','name','unit','price','category','available'),show='headings',selectmode='browse',height=15,style='Restaurant.Treeview')
tree.grid(row=0,column=0,sticky='nsew')

ys=ttk.Scrollbar(left_frame,orient='vertical',command=tree.yview)
ys.grid(row=0,column=1,sticky='ns')

xs=ttk.Scrollbar(left_frame,orient='horizontal',command=tree.xview)
xs.grid(row=1,column=0,sticky='ew')

tree.configure(yscrollcommand=ys.set,xscrollcommand=xs.set)

tree.column('id',width=55,anchor='center')
tree.column('name',width=190,anchor='w')
tree.column('unit',width=100,anchor='w')
tree.column('price',width=90,anchor='e')
tree.column('category',width=100,anchor='center')
tree.column('available',width=90,anchor='center')

tree.heading('id',text='ID')
tree.heading('name',text='Product')
tree.heading('unit',text='Unit')
tree.heading('price',text='Price')
tree.heading('category',text='Category')
tree.heading('available',text='Available')

selected_id_var=tk.StringVar()
name_var=tk.StringVar()
unit_var=tk.StringVar()
price_var=tk.StringVar()
category_var=tk.StringVar()
available_var=tk.IntVar(value=1)
filter_var=tk.IntVar(value=1)
message_var=tk.StringVar()

font1=('Times',13)

ttk.Label(right_frame,text='Edit Product',font=('Times',18,'bold')).grid(row=0,column=0,columnspan=3,pady=(0,15))

ttk.Label(right_frame,text='Product ID',font=font1).grid(row=1,column=0,sticky='w',pady=5)
ttk.Entry(right_frame,textvariable=selected_id_var,state='readonly',width=22).grid(row=1,column=1,columnspan=2,sticky='ew')

ttk.Label(right_frame,text='Product Name',font=font1).grid(row=2,column=0,sticky='w',pady=5)
name_entry=ttk.Entry(right_frame,textvariable=name_var,width=22)
name_entry.grid(row=2,column=1,columnspan=2,sticky='ew')

ttk.Label(right_frame,text='Unit',font=font1).grid(row=3,column=0,sticky='w',pady=5)
ttk.Entry(right_frame,textvariable=unit_var,width=22).grid(row=3,column=1,columnspan=2,sticky='ew')

ttk.Label(right_frame,text='Price',font=font1).grid(row=4,column=0,sticky='w',pady=5)
ttk.Entry(right_frame,textvariable=price_var,width=22).grid(row=4,column=1,columnspan=2,sticky='ew')

ttk.Label(right_frame,text='Category',font=font1).grid(row=5,column=0,sticky='w',pady=5)
category_box=ttk.Combobox(right_frame,textvariable=category_var,values=list(CATEGORIES.values()),state='readonly',width=20)
category_box.grid(row=5,column=1,columnspan=2,sticky='ew')

ttk.Label(right_frame,text='Available',font=font1).grid(row=6,column=0,sticky='w',pady=5)
ttk.Radiobutton(right_frame,text='Yes',variable=available_var,value=1).grid(row=6,column=1,sticky='w')
ttk.Radiobutton(right_frame,text='No',variable=available_var,value=0).grid(row=6,column=2,sticky='w')

update_btn=tk.Button(right_frame,text='Update Product',state=tk.DISABLED,command=lambda:my_update())
update_btn.grid(row=7,column=1,columnspan=2,pady=15)

message_label=tk.Label(right_frame,textvariable=message_var,font=('Times',11),wraplength=300)
message_label.grid(row=8,column=0,columnspan=3,pady=5)

def show_msg(message,success=True):
    message_var.set(message)
    message_label.config(fg='green' if success else 'red')
    root.after(3000,lambda:message_var.set(''))

def clear_form():
    selected_id_var.set('')
    name_var.set('')
    unit_var.set('')
    price_var.set('')
    category_var.set('')
    available_var.set(1)
    update_btn.config(state=tk.DISABLED)

def show_items(cat):
    clear_form()

    for item in tree.get_children():
        tree.delete(item)

    query=text('''SELECT p_id,p_name,unit,price,p_cat,available
    FROM plus2_products
    WHERE p_cat=:cat
    ORDER BY p_name,p_id''')

    try:
        with engine.connect() as conn:
            rows=conn.execute(query,{'cat':cat}).mappings().all()
    except SQLAlchemyError as e:
        print(e)
        show_msg('Unable to load products.',False)
        return

    for row in rows:
        tree.insert('',tk.END,iid=str(row['p_id']),values=(row['p_id'],row['p_name'],row['unit'],row['price'],CATEGORIES.get(row['p_cat'],row['p_cat']),'Yes' if row['available'] else 'No'))

def data_collect(event=None):
    selected=tree.selection()

    if not selected:
        clear_form()
        return

    try:
        p_id=int(selected[0])
    except ValueError:
        clear_form()
        return

    query=text('''SELECT p_id,p_name,unit,price,p_cat,available
    FROM plus2_products
    WHERE p_id=:p_id''')

    try:
        with engine.connect() as conn:
            row=conn.execute(query,{'p_id':p_id}).mappings().first()
    except SQLAlchemyError as e:
        print(e)
        clear_form()
        show_msg('Unable to load the selected product.',False)
        return

    if not row:
        clear_form()
        show_msg('Product was not found. Reload the list.',False)
        return

    selected_id_var.set(str(row['p_id']))
    name_var.set(row['p_name'])
    unit_var.set(row['unit'])
    price_var.set(f"{Decimal(str(row['price'])):.2f}")
    category_var.set(CATEGORIES.get(row['p_cat'],''))
    available_var.set(int(row['available']))
    update_btn.config(state=tk.NORMAL)
    name_entry.focus_set()

def my_update():
    selected_id=selected_id_var.get()

    if not selected_id:
        show_msg('Select a product to update.',False)
        return

    product_name=name_var.get().strip()
    product_unit=unit_var.get().strip()
    category_id=CATEGORY_ID.get(category_var.get())

    if not product_name:
        show_msg('Enter a product name.',False)
        return

    if not product_unit:
        show_msg('Enter the product unit.',False)
        return

    if category_id is None:
        show_msg('Select a product category.',False)
        return

    try:
        p_id=int(selected_id)
        product_price=Decimal(price_var.get().strip()).quantize(MONEY,rounding=ROUND_HALF_UP)
    except (ValueError,InvalidOperation):
        show_msg('Enter a valid product ID and price.',False)
        return

    if product_price<0:
        show_msg('Price cannot be negative.',False)
        return

    query=text('''UPDATE plus2_products
    SET p_name=:name,unit=:unit,price=:price,p_cat=:category,available=:available
    WHERE p_id=:p_id''')

    data={'name':product_name,'unit':product_unit,'price':product_price,'category':category_id,'available':available_var.get(),'p_id':p_id}

    try:
        with engine.begin() as conn:
            result=conn.execute(query,data)
    except SQLAlchemyError as e:
        print(e)
        show_msg('Database update failed.',False)
        return

    filter_var.set(category_id)
    show_items(category_id)
    show_msg(f'Product {p_id} updated. Database rowcount: {result.rowcount}')

def change_category(cat):
    filter_var.set(cat)
    show_items(cat)

for column,(cat_id,name) in enumerate(CATEGORIES.items()):
    ttk.Radiobutton(bottom_frame,text=name,variable=filter_var,value=cat_id,command=lambda c=cat_id:change_category(c)).grid(row=0,column=column,padx=12)

tree.bind('<<TreeviewSelect>>',data_collect)

show_items(1)
root.mainloop()

Optional Restaurant Header Image 🔝

The original code contains a machine-specific path:

G:\My Drive\testing\plus2_restaurant_v1\images\

For a portable project, store the image inside the application folder:

header_image=tk.PhotoImage(file='images/restaurant-3.png')
header_label=tk.Label(root,image=header_image)

The complete example omits the decorative image so the database editor runs without requiring an additional file.

Common Treeview Update Mistakes 🔝

1. Using focus() Instead of the Current Selection

For a row-selection editor, use:

tree.selection()

and verify that an item exists.

2. Keeping One Database Connection Open

Create the Engine once and use short-lived connection or transaction contexts.

3. Using Engine.execute()

Use:

with engine.begin() as conn:
    conn.execute(...)

4. Building SQL with Positional Placeholders

Named parameters make the UPDATE easier to read:

WHERE p_id=:p_id

5. Assigning a New Lambda to the Update Button for Every Selection

Keep the selected product ID in a variable and use one permanent Update command.

6. Using DoubleVar for Money

Use text input plus Decimal validation for database price values.

7. Allowing Free Text in the Category Combobox

Use:

state='readonly'

so only valid restaurant categories can be selected.

8. Setting available to 5

The existing project uses availability as a Yes/No field, so valid values are:

1 = Yes
0 = No

9. Not Clearing the Edit Form after Changing the Filter

A product selected under Breakfast should not remain editable after the Treeview has switched to Dinner.

10. Not Reloading after an Update

Retrieve fresh database data after updating so Treeview reflects the persistent record.

11. Assuming rowcount=0 Always Means Failure

An unchanged UPDATE may report zero affected rows depending on the database driver and configuration. A zero value is not always equivalent to an SQL error.

Frequently Asked Questions 🔝

Treeview MySQL Update Summary 🔝

• Products are filtered by Breakfast, Lunch or Dinner.
• Treeview is populated from the MySQL plus2_products table.
• The MySQL p_id is also used as Treeview iid.
• Use selection() to identify the selected record.
• The latest selected product is retrieved from MySQL before editing.
• The product ID is displayed as a read-only value.
• The Update button remains disabled until a valid record is selected.
• Product name and unit are validated before update.
• Price is validated with Python Decimal.
• The category Combobox is read-only.
• Availability uses only 1 for Yes and 0 for No.
• The UPDATE query uses named parameters.
• engine.begin() handles commit and rollback.
• Treeview is reloaded from MySQL after an update.
• If the category changes, the application reloads the product's new category.
• Changing the category filter clears any previously selected edit record.
• Vertical and horizontal scrollbars support larger product lists.
• A relative image path should replace machine-specific paths if a logo is used.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: