Load MySQL Products into a Tkinter Invoice Combobox

In the basic Tkinter invoice generator, product details can be entered directly in the GUI. In this version, the available products and prices come from a MySQL product table.

The user selects a product from a ttk Combobox. Python then retrieves the product's database ID and unit price automatically. The user only needs to enter the quantity.

MySQL plus2_product       |       vProduct Combobox       |       +--> Product ID       +--> Product Name       +--> Unit Price       |       vEnter Quantity       |       vInvoice Treeview

MySQL Product Table 🔝

The sample product table contains the product ID, product name and current unit price.

Use DECIMAL rather than FLOAT for monetary values.

CREATE TABLE plus2_product (    p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,    p_name VARCHAR(100) NOT NULL,    price DECIMAL(12,2) NOT NULL,    PRIMARY KEY (p_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO plus2_product (p_name,price) VALUES('Mouse',12.34),('Keyboard',18.76),('Monitor',20.45),('CPU',20.35),('Pen Drive',8.50),('Operating System',10.23);

The database-generated p_id is the permanent identifier for each product.

Load Products from MySQL in tk_invoice_products.py 🔝

The original project uses a separate tk_invoice_products.py module. We can keep that useful separation, but make the module responsible only for loading and preparing product data.

from decimal import Decimalfrom sqlalchemy import textdef load_products(engine):    query=text('''SELECT p_id,p_name,price    FROM plus2_product    ORDER BY p_name,p_id''')    with engine.connect() as conn:        rows=conn.execute(query).mappings().all()    products={}    for row in rows:        label=f"{row['p_name']} [ID {row['p_id']}]"        products[label]={            'p_id':int(row['p_id']),            'name':str(row['p_name']),            'price':Decimal(str(row['price']))        }    return products

The main application imports:

from tk_invoice_products import load_products

The SQLAlchemy Engine remains in the main program, so the database credentials do not have to be repeated in two Python files.

Why Include Product ID in the Combobox Mapping? 🔝

A dictionary can map each visible Combobox label to its complete product information:

{    'Mouse [ID 1]':{        'p_id':1,        'name':'Mouse',        'price':Decimal('12.34')    }}

Including the ID in the internal label also avoids ambiguity if two products happen to have the same name.

The invoice itself still displays the normal product name separately.

Populate the Product Combobox 🔝

Create the Combobox in read-only mode:

product_var=tk.StringVar()cb_product=ttk.Combobox(    root,    textvariable=product_var,    state='readonly',    width=24)

After loading the product dictionary:

product_options=load_products(engine)cb_product['values']=list(product_options)

Using state='readonly' prevents arbitrary product names from being entered.

Get Product ID and Price when the Product Changes 🔝

Use the Combobox selection event:

cb_product.bind('<<ComboboxSelected>>',on_product_selected)

Then retrieve the selected product:

def on_product_selected(event=None):    item=product_options.get(product_var.get())    if item:        product_id_var.set(str(item['p_id']))        price_var.set(f"{item['price']:.2f}")

This is more precise than:

product.trace('w',my_price)

because <<ComboboxSelected>> specifically represents a user's Combobox selection.

Make the Price Read Only

price_entry=ttk.Entry(root,textvariable=price_var,state='readonly')

The price came from the product table, so this application does not require the user to re-enter it manually.

Add the Selected Product to Treeview 🔝

Use the actual product object selected from the database-backed dictionary.

def my_add():    selected=product_options.get(product_var.get())    if not selected:        messagebox.showwarning('Product','Select a product.')        return    try:        qty_value=int(qty_var.get())    except ValueError:        messagebox.showwarning('Quantity','Enter a whole-number quantity.')        return    if qty_value<=0:        messagebox.showwarning('Quantity','Quantity must be greater than zero.')        return    price=selected['price']    line_total=(Decimal(qty_value)*price).quantize(MONEY,rounding=ROUND_HALF_UP)    line_no=len(tree.get_children())+1    tree.insert(        '',        tk.END,        values=(line_no,selected['p_id'],selected['name'],qty_value,f'{price:.2f}',f'{line_total:.2f}')    )    update_totals(reset_fields=True)

The invoice now keeps these separate:

Line NumberProduct IDProduct NameQuantityUnit PriceLine Total

Calculate Subtotal, Tax and Invoice Total 🔝

The line-total column is at index 5:

for item in tree.get_children():    values=tree.item(item,'values')    sub_total+=to_money(values[5])

Then:

tax=(sub_total*TAX_RATE).quantize(MONEY,rounding=ROUND_HALF_UP)invoice_total=(sub_total+tax).quantize(MONEY,rounding=ROUND_HALF_UP)

The tutorial continues to use a 10% sample tax rate:

TAX_RATE=Decimal('0.10')

Change this constant according to the rules required by your application.

Save the Invoice and Product IDs to MySQL 🔝

Each invoice-detail row stores the actual p_id loaded from plus2_product.

details.append({    'p_id':int(values[1]),    'product':values[2],    'qty':int(values[3]),    'price':to_money(values[4]),    'line_total':to_money(values[5])})

The invoice header and all detail rows are stored inside one transaction:

with engine.begin() as conn:    result=conn.execute(header_sql,header_data)    inv_id=result.lastrowid    for row in details:        row['inv_id']=inv_id    conn.execute(detail_sql,details)

This uses the same invoice-table design introduced in the updated invoice generation tutorial.

Complete Invoice Generator with MySQL Product Table 🔝

Save the helper module above as tk_invoice_products.py, then use this main program.

import tkinter as tkfrom tkinter import ttk, messageboxfrom datetime import datefrom decimal import Decimal, InvalidOperation, ROUND_HALF_UPfrom sqlalchemy import create_engine, textfrom sqlalchemy.exc import SQLAlchemyErrorfrom tk_invoice_products import load_productsengine=create_engine('mysql+mysqldb://id:pw@localhost/my_tutorial')TAX_RATE=Decimal('0.10')MONEY=Decimal('0.01')invoice_total=Decimal('0.00')product_options={}root=tk.Tk()root.geometry('950x600')root.title('Invoice with MySQL Products - plus2net')product_var=tk.StringVar()product_id_var=tk.StringVar()qty_var=tk.StringVar(value='1')price_var=tk.StringVar(value='0.00')sub_total_var=tk.StringVar(value='0.00')tax_var=tk.StringVar(value='0.00')total_var=tk.StringVar(value='0.00')msg_var=tk.StringVar()def to_money(value):    return Decimal(str(value).strip()).quantize(MONEY,rounding=ROUND_HALF_UP)def reset_inputs():    product_var.set('')    product_id_var.set('')    qty_var.set('1')    price_var.set('0.00')    cb_product.focus_set()def refresh_products():    global product_options    try:        product_options=load_products(engine)    except SQLAlchemyError as e:        print(e)        product_options={}        cb_product['values']=()        messagebox.showerror('Database Error','Unable to load products.')        return    cb_product['values']=list(product_options)    reset_inputs()def on_product_selected(event=None):    item=product_options.get(product_var.get())    if item:        product_id_var.set(str(item['p_id']))        price_var.set(f"{item['price']:.2f}")def update_totals(reset_fields=False):    global invoice_total    sub_total=Decimal('0.00')    for line_no,item in enumerate(tree.get_children(),start=1):        values=list(tree.item(item,'values'))        values[0]=line_no        tree.item(item,values=values)        sub_total+=to_money(values[5])    sub_total=sub_total.quantize(MONEY,rounding=ROUND_HALF_UP)    tax=(sub_total*TAX_RATE).quantize(MONEY,rounding=ROUND_HALF_UP)    invoice_total=(sub_total+tax).quantize(MONEY,rounding=ROUND_HALF_UP)    sub_total_var.set(f'{sub_total:.2f}')    tax_var.set(f'{tax:.2f}')    total_var.set(f'{invoice_total:.2f}')    if reset_fields:        reset_inputs()def my_add():    selected=product_options.get(product_var.get())    if not selected:        messagebox.showwarning('Product','Select a product.')        return    try:        qty_value=int(qty_var.get())    except ValueError:        messagebox.showwarning('Quantity','Enter a whole-number quantity.')        return    if qty_value<=0:        messagebox.showwarning('Quantity','Quantity must be greater than zero.')        return    price=selected['price']    line_total=(Decimal(qty_value)*price).quantize(MONEY,rounding=ROUND_HALF_UP)    line_no=len(tree.get_children())+1    tree.insert('',tk.END,values=(line_no,selected['p_id'],selected['name'],qty_value,f'{price:.2f}',f'{line_total:.2f}'))    update_totals(reset_fields=True)def selection_changed(event=None):    delete_btn.config(state=tk.NORMAL if tree.selection() else tk.DISABLED)def data_delete():    selected=tree.selection()    if not selected:        return    tree.delete(selected[0])    delete_btn.config(state=tk.DISABLED)    update_totals()def my_reset(clear_message=True):    global invoice_total    for item in tree.get_children():        tree.delete(item)    invoice_total=Decimal('0.00')    sub_total_var.set('0.00')    tax_var.set('0.00')    total_var.set('0.00')    delete_btn.config(state=tk.DISABLED)    if clear_message:        msg_var.set('')    reset_inputs()def insert_data():    if not tree.get_children():        messagebox.showwarning('Invoice','Add at least one product before confirming the invoice.')        return    update_totals()    sub_total=to_money(sub_total_var.get())    tax=to_money(tax_var.get())    details=[]    for item in tree.get_children():        values=tree.item(item,'values')        details.append({            'p_id':int(values[1]),            'product':str(values[2]),            'qty':int(values[3]),            'price':to_money(values[4]),            'line_total':to_money(values[5])        })    header_sql=text('''INSERT INTO plus2_invoice    (sub_total,tax_rate,tax,total,dt)    VALUES (:sub_total,:tax_rate,:tax,:total,:dt)''')    detail_sql=text('''INSERT INTO plus2_invoice_dtl    (inv_id,p_id,product,qty,price,line_total)    VALUES (:inv_id,:p_id,:product,:qty,:price,:line_total)''')    header_data={        'sub_total':sub_total,        'tax_rate':(TAX_RATE*100).quantize(MONEY),        'tax':tax,        'total':invoice_total,        'dt':date.today()    }    try:        with engine.begin() as conn:            result=conn.execute(header_sql,header_data)            inv_id=result.lastrowid            for row in details:                row['inv_id']=inv_id            conn.execute(detail_sql,details)    except SQLAlchemyError as e:        print(e)        messagebox.showerror('Database Error','Unable to save the invoice.')        return    product_count=len(details)    my_reset(clear_message=False)    refresh_products()    msg_var.set(f'Bill No: {inv_id}, Products: {product_count}')    root.after(5000,lambda:msg_var.set(''))font1=('Times',15)font2=('Times',20)tk.Label(root,text='Product',font=font1).grid(row=0,column=0,padx=8,pady=10)cb_product=ttk.Combobox(root,textvariable=product_var,width=24,state='readonly')cb_product.grid(row=0,column=1,padx=5)tk.Label(root,text='Product ID',font=font1).grid(row=0,column=2,padx=5)ttk.Entry(root,textvariable=product_id_var,width=8,state='readonly').grid(row=0,column=3)tk.Label(root,text='Quantity',font=font1).grid(row=1,column=0,padx=8,pady=8)tk.Entry(root,textvariable=qty_var,width=8).grid(row=1,column=1,sticky='w')tk.Label(root,text='Price',font=font1).grid(row=1,column=2,padx=5)ttk.Entry(root,textvariable=price_var,width=10,state='readonly').grid(row=1,column=3)tk.Button(root,text='Add',command=my_add).grid(row=1,column=4,padx=8)tk.Button(root,text='Reload Products',command=refresh_products).grid(row=0,column=4,padx=8)style=ttk.Style(root)if 'clam' in style.theme_names():    style.theme_use('clam')style.configure('InvoiceProduct.Treeview',background='azure2',fieldbackground='lightyellow',foreground='black',rowheight=28)style.configure('InvoiceProduct.Treeview.Heading',background='PowderBlue',foreground='black')tree_frame=ttk.Frame(root)tree_frame.grid(row=2,column=0,columnspan=6,padx=10,pady=8)tree=ttk.Treeview(tree_frame,columns=('line','p_id','product','qty','price','total'),show='headings',selectmode='browse',height=8,style='InvoiceProduct.Treeview')tree.grid(row=0,column=0)ys=ttk.Scrollbar(tree_frame,orient='vertical',command=tree.yview)ys.grid(row=0,column=1,sticky='ns')tree.configure(yscrollcommand=ys.set)tree.column('line',width=55,anchor='center')tree.column('p_id',width=80,anchor='center')tree.column('product',width=250,anchor='w')tree.column('qty',width=80,anchor='center')tree.column('price',width=100,anchor='e')tree.column('total',width=110,anchor='e')tree.heading('line',text='Line')tree.heading('p_id',text='Product ID')tree.heading('product',text='Product')tree.heading('qty',text='Quantity')tree.heading('price',text='Rate')tree.heading('total',text='Line Total')delete_btn=tk.Button(root,text='Delete Selected',state=tk.DISABLED,command=data_delete)delete_btn.grid(row=3,column=0,pady=5)tk.Button(root,text='Delete All',command=my_reset).grid(row=3,column=1,pady=5)tk.Label(root,text='Subtotal:',font=font1).grid(row=3,column=3,sticky='e')tk.Label(root,textvariable=sub_total_var,font=font1).grid(row=3,column=4,sticky='e')tk.Label(root,text='Tax 10%:',font=font1).grid(row=4,column=3,sticky='e')tk.Label(root,textvariable=tax_var,font=font1).grid(row=4,column=4,sticky='e')tk.Label(root,text='Total:',font=font2).grid(row=5,column=3,sticky='e')tk.Label(root,textvariable=total_var,font=font2).grid(row=5,column=4,sticky='e',pady=12)tk.Button(root,text='Confirm Invoice',font=font1,command=insert_data).grid(row=5,column=1,pady=12)tk.Label(root,textvariable=msg_var,fg='green').grid(row=6,column=0,columnspan=5,pady=5)cb_product.bind('<<ComboboxSelected>>',on_product_selected)tree.bind('<<TreeviewSelect>>',selection_changed)refresh_products()root.mainloop()

Optional Logo at the Top of the Invoice 🔝

The original example used an absolute path such as:

H:\top2.png

Keep the image inside the project directory instead:

my_img=tk.PhotoImage(file='top2.png')logo=tk.Label(root,image=my_img)

This makes the application portable to another computer.

Load Only Products with Available Stock 🔝

The project can also calculate available stock from procurement quantity minus sold quantity.

The original page used nested joins. The same logic can be written more clearly by separately aggregating received and sold quantities.

SELECT p.p_id,p.p_name,p.price,       COALESCE(r.received,0)-COALESCE(s.sold,0) AS stockFROM plus2_product AS pLEFT JOIN (    SELECT p_id,SUM(qty) AS received    FROM plus2_product_receive    GROUP BY p_id) AS r ON r.p_id=p.p_idLEFT JOIN (    SELECT p_id,SUM(qty) AS sold    FROM plus2_invoice_dtl    GROUP BY p_id) AS s ON s.p_id=p.p_idWHERE COALESCE(r.received,0)-COALESCE(s.sold,0) > :min_stockORDER BY p.p_name,p.p_id

With:

min_stock=2

only products with more than two units available are offered in the Combobox.

See the existing Plus2net SQL tutorial for the related procurement, sales and stock tables:

Use the Stock Query in tk_invoice_products.py

The simple product query:

SELECT p_id,p_name,priceFROM plus2_product

can be replaced with the stock-aware query when only available products should appear.

Common Product-Invoice Integration Mistakes 🔝

1. Using Engine.execute()

Use:

with engine.connect() as conn:    rows=conn.execute(text(query)).mappings().all()

2. Allowing Unknown Products with p_id=0

Use a read-only Combobox and require a product loaded from the database.

3. Searching the Entire Dictionary on Every Selection

Build the dictionary so the Combobox label directly maps to the complete product object.

4. Assuming Product Names Are Always Unique

Use the database ID in the mapping. The revised example includes the ID in the Combobox label.

5. Letting the User Re-enter a Database Price

If the product-table price is the intended invoice rate, display it in a read-only Entry.

6. Using float for Money

Use Python Decimal and MySQL DECIMAL.

7. Mixing Line Number and Product ID

The Treeview line number is only display order. Store the database p_id separately.

8. Using trace() when ComboboxSelected Is More Specific

<<ComboboxSelected>> runs when the user selects a Combobox option and avoids unnecessary callbacks during ordinary variable resets.

9. Saving Invoice Header and Details Separately

Use one database transaction so the complete invoice succeeds or fails together.

10. Relying on the Product List as a Stock Reservation

Stock can change after products are loaded. Re-check inventory at confirmation time in applications where concurrent sales matter.

Frequently Asked Questions 🔝

MySQL Product Invoice Summary 🔝

• Products are loaded from the MySQL plus2_product table.
• The SQLAlchemy Engine is created once in the main application.
• A helper function returns product information to the GUI.
• The Combobox is read-only so only database products can be selected.
• Product ID and price are retrieved automatically.
• The price field is read-only.
• <<ComboboxSelected>> is used instead of a general StringVar trace.
• Invoice line number and product ID are separate values.
• Duplicate product names can still be distinguished by database ID.
• Python Decimal is used for invoice calculations.
• MySQL DECIMAL is used for product prices.
• Quantity must be a positive integer.
• The invoice detail stores product ID, product name, quantity, sale price and line total.
• The invoice header and all details are saved in one transaction.
• Product lists can be refreshed after an invoice is saved.
• Available stock can be calculated from procurement minus sales.
• Production inventory systems should re-check stock during invoice confirmation.