Load MySQL Products into a Tkinter Invoice Combobox

Tkinter invoice generator loading product ID and price from MySQL

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
       |
       v
Product Combobox
       |
       +--> Product ID
       +--> Product Name
       +--> Unit Price
       |
       v
Enter Quantity
       |
       v
Invoice 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 Decimal
from sqlalchemy import text

def 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.

Integrate a MySQL Product Table with a Tkinter Invoice Generator

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 Number
Product ID
Product Name
Quantity
Unit Price
Line 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 tk
from tkinter import ttk, messagebox
from datetime import date
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from tk_invoice_products import load_products

engine=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 stock
FROM plus2_product AS p
LEFT JOIN (
    SELECT p_id,SUM(qty) AS received
    FROM plus2_product_receive
    GROUP BY p_id
) AS r ON r.p_id=p.p_id
LEFT JOIN (
    SELECT p_id,SUM(qty) AS sold
    FROM plus2_invoice_dtl
    GROUP BY p_id
) AS s ON s.p_id=p.p_id
WHERE COALESCE(r.received,0)-COALESCE(s.sold,0) > :min_stock
ORDER 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:

Product Stock SQL and Sample Tables
Invoice Products Based on Procurement, Sales and Available Stock

Use the Stock Query in tk_invoice_products.py

The simple product query:

SELECT p_id,p_name,price
FROM 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 🔝

Q1: How does the Combobox get products from MySQL?

SQLAlchemy retrieves product ID, name and price from plus2_product. The returned rows are converted into a dictionary and the dictionary labels become the Combobox values.

Q2: How is the price filled automatically?

The <<ComboboxSelected>> event finds the selected product object and places its database price in the read-only price variable.

Q3: Why store p_id in the invoice detail table?

The product ID provides a stable relationship between the invoice line and the product record, even if the product name later changes.

Q4: Why also store the product name and price in the invoice detail?

An invoice normally needs a snapshot of what was sold and at what price. Current product-table values can change later.

Q5: Why use Decimal for product prices?

Decimal provides fixed decimal arithmetic suitable for currency-style calculations and matches MySQL DECIMAL values.

Q6: Can unavailable products be removed from the Combobox?

Yes. Retrieve received and sold quantities, calculate available stock and return only products whose stock exceeds the chosen minimum.

Q7: Is checking stock when the Combobox loads enough?

Not for a multi-user inventory system. Stock should be validated again when the invoice is committed because another transaction may have changed it.

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.
Continue the invoice project: The basic invoice page explains totals and database storage. The next stage can use the procurement table to add received stock and build a complete stock flow.
Invoice Generator Product Procurement

MySQL Records in Treeview Query and Display Records Select, Edit and Update MySQL Records




Subscribe to our YouTube Channel here



plus2net.com







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 Contact us
© 2000-2026 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer