Invoice Generator using Tkinter Treeview and MySQL

This project uses Tkinter Treeview to build a simple invoice-generation application. The user selects a product, enters quantity and unit price, and adds the item to the invoice.

For every line we calculate:

line total = quantity * unit price

The application then recalculates the subtotal, tax and final invoice total whenever a product is added or removed.

When the user confirms the invoice, the invoice header is stored in one MySQL table and the individual product lines are stored in a second table linked by the generated invoice ID.

Invoice Workflow 🔝

1. Select a product.
2. Enter quantity.
3. Enter the unit price.
4. Calculate quantity multiplied by price.
5. Add the row to Treeview.
6. Recalculate subtotal.
7. Calculate tax.
8. Calculate final invoice total.
9. Repeat for additional products.
10. Delete rows when required.
11. Confirm the invoice and save all rows to MySQL.

Product IDs and the Combobox 🔝

For this introductory version, product information is kept in a Python dictionary.

PRODUCTS={    1:'Monitor',    2:'Mouse',    3:'Keyboard',    4:'Pen Drive',    5:'CPU',    6:'Power Unit'}

Create a reverse mapping so the selected product name can return its product ID:

product_id_by_name={name:p_id for p_id,name in PRODUCTS.items()}

The Combobox displays product names:

cb_product=ttk.Combobox(root, values=list(PRODUCTS.values()), textvariable=product, state='readonly')

The next project replaces this hard-coded dictionary with records from a MySQL product table.

Use Decimal for Invoice Money 🔝

Currency calculations should avoid depending on ordinary binary floating-point arithmetic.

from decimal import Decimal, ROUND_HALF_UPMONEY=Decimal('0.01')TAX_RATE=Decimal('0.10')def to_money(value):    return Decimal(str(value).strip()).quantize(MONEY, rounding=ROUND_HALF_UP)

For a quantity of 3 and a unit price of 125.50:

line_total=(Decimal(3)*Decimal('125.50')).quantize(MONEY)

Result:

376.50

my_add(): Add a Product to Treeview 🔝

The function validates the selected product, quantity and price before inserting the row.

def my_add():    name=product.get().strip()    if name not in product_id_by_name:        messagebox.showwarning('Product', 'Select a product.')        return    try:        qty_value=int(qty.get())        price_value=to_money(prc.get())    except (ValueError, InvalidOperation):        messagebox.showwarning('Input', 'Enter a valid quantity and price.')        return    if qty_value<=0:        messagebox.showwarning('Quantity', 'Quantity must be greater than zero.')        return    if price_value<0:        messagebox.showwarning('Price', 'Price cannot be negative.')        return    p_id=product_id_by_name[name]    line_no=len(trv.get_children())+1    line_total=(Decimal(qty_value)*price_value).quantize(MONEY, rounding=ROUND_HALF_UP)    trv.insert('', tk.END, values=(line_no,p_id,name,qty_value,f'{price_value:.2f}',f'{line_total:.2f}'))    my_upd(reset_fields=True)

Treeview generates its own item ID. The invoice line number and product ID are stored as normal values.

my_upd(): Recalculate Subtotal, Tax and Total 🔝

def my_upd(reset_fields=False):    global invoice_total    sub_total=Decimal('0.00')    for line_no,item in enumerate(trv.get_children(), start=1):        values=list(trv.item(item,'values'))        values[0]=line_no        trv.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()

The same function also renumbers invoice lines after a row is deleted.

Delete the Selected Product 🔝

Enable the Delete button only when a Treeview row is selected:

def my_select(event=None):    b2.config(state=tk.NORMAL if trv.selection() else tk.DISABLED)

Delete safely:

def data_delete():    selected=trv.selection()    if not selected:        return    trv.delete(selected[0])    b2.config(state=tk.DISABLED)    my_upd()

This avoids the current page's possible:

IndexError

from directly using selection()[0] when nothing is selected.

Reset the Invoice 🔝

def my_reset(clear_message=True):    global invoice_total    for item in trv.get_children():        trv.delete(item)    invoice_total=Decimal('0.00')    sub_total_var.set('0.00')    tax_var.set('0.00')    total_var.set('0.00')    b2.config(state=tk.DISABLED)    if clear_message:        msg_var.set('')    reset_inputs()

insert_data(): Save Invoice and Product Details 🔝

The invoice header and detail rows are related by inv_id.

First prepare the details:

details=[]for item in trv.get_children():    values=trv.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])    })

The SQL uses named parameters:

header_sql=text('''INSERT INTO plus2_invoice(sub_total,tax_rate,tax,total,dt)VALUES (:sub_total,:tax_rate,:tax,:total,:dt)''')

Save the Whole Invoice in One Transaction 🔝

The invoice header and its detail rows belong together. We should not save the invoice header successfully and then leave the invoice incomplete if inserting a product row fails.

with engine.begin() as conn:    result=conn.execute(header_sql,header_data)    inv_id=result.lastrowid    conn.execute(detail_sql,details)

If the block finishes normally, the transaction is committed. If an exception occurs, the transaction is rolled back.

MySQL Tables for Invoice Header and Details 🔝

For money values, use fixed-point DECIMAL columns rather than FLOAT.

CREATE TABLE plus2_invoice (    inv_id INT UNSIGNED NOT NULL AUTO_INCREMENT,    sub_total DECIMAL(12,2) NOT NULL,    tax_rate DECIMAL(5,2) NOT NULL,    tax DECIMAL(12,2) NOT NULL,    total DECIMAL(12,2) NOT NULL,    dt DATE NOT NULL,    PRIMARY KEY (inv_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE plus2_invoice_dtl (    dtl_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,    inv_id INT UNSIGNED NOT NULL,    p_id INT UNSIGNED NOT NULL,    product VARCHAR(100) NOT NULL,    qty INT UNSIGNED NOT NULL,    price DECIMAL(12,2) NOT NULL,    line_total DECIMAL(12,2) NOT NULL,    PRIMARY KEY (dtl_id),    KEY idx_invoice (inv_id),    CONSTRAINT fk_plus2_invoice_dtl_invoice        FOREIGN KEY (inv_id) REFERENCES plus2_invoice(inv_id)        ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The invoice table stores summary values, while plus2_invoice_dtl stores one row for every product.

Complete Tkinter Invoice Generator 🔝

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 SQLAlchemyErrorengine=create_engine('mysql+mysqldb://id:pw@localhost/my_db')TAX_RATE=Decimal('0.10')MONEY=Decimal('0.01')PRODUCTS={1:'Monitor',2:'Mouse',3:'Keyboard',4:'Pen Drive',5:'CPU',6:'Power Unit'}product_id_by_name={name:p_id for p_id,name in PRODUCTS.items()}root=tk.Tk()root.geometry('900x560')root.title('Invoice Generation - plus2net')product=tk.StringVar()qty=tk.StringVar(value='1')prc=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()invoice_total=Decimal('0.00')def to_money(value):    return Decimal(str(value).strip()).quantize(MONEY, rounding=ROUND_HALF_UP)def reset_inputs():    product.set('')    qty.set('1')    prc.set('0.00')    cb_product.focus_set()def my_upd(reset_fields=False):    global invoice_total    sub_total=Decimal('0.00')    for line_no,item in enumerate(trv.get_children(),start=1):        values=list(trv.item(item,'values'))        values[0]=line_no        trv.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():    name=product.get().strip()    if name not in product_id_by_name:        messagebox.showwarning('Product','Select a product.')        return    try:        qty_value=int(qty.get())        price_value=to_money(prc.get())    except (ValueError,InvalidOperation):        messagebox.showwarning('Input','Enter a whole-number quantity and a valid price.')        return    if qty_value<=0:        messagebox.showwarning('Quantity','Quantity must be greater than zero.')        return    if price_value<0:        messagebox.showwarning('Price','Price cannot be negative.')        return    p_id=product_id_by_name[name]    line_no=len(trv.get_children())+1    line_total=(Decimal(qty_value)*price_value).quantize(MONEY,rounding=ROUND_HALF_UP)    trv.insert('',tk.END,values=(line_no,p_id,name,qty_value,f'{price_value:.2f}',f'{line_total:.2f}'))    my_upd(reset_fields=True)def my_select(event=None):    b2.config(state=tk.NORMAL if trv.selection() else tk.DISABLED)def data_delete():    selected=trv.selection()    if not selected:        return    trv.delete(selected[0])    b2.config(state=tk.DISABLED)    my_upd()def my_reset(clear_message=True):    global invoice_total    for item in trv.get_children():        trv.delete(item)    invoice_total=Decimal('0.00')    sub_total_var.set('0.00')    tax_var.set('0.00')    total_var.set('0.00')    b2.config(state=tk.DISABLED)    if clear_message:        msg_var.set('')    reset_inputs()def insert_data():    if not trv.get_children():        messagebox.showwarning('Invoice','Add at least one product before confirming the invoice.')        return    my_upd()    sub_total=to_money(sub_total_var.get())    tax=to_money(tax_var.get())    details=[]    for item in trv.get_children():        values=trv.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)''')    try:        with engine.begin() as conn:            result=conn.execute(header_sql,{'sub_total':sub_total,'tax_rate':TAX_RATE*100,'tax':tax,'total':invoice_total,'dt':date.today()})            inv_id=result.lastrowid            for row in details:                row['inv_id']=inv_id            conn.execute(detail_sql,details)    except SQLAlchemyError as e:        messagebox.showerror('Database Error',str(e))        return    my_reset(clear_message=False)    msg_var.set(f'Bill No: {inv_id}, Products: {len(details)}')    root.after(3000,lambda:msg_var.set(''))font1=('Times',16,'normal')font2=('Times',22,'normal')tk.Label(root,text='Product',font=font1).grid(row=0,column=0,padx=10,pady=10)cb_product=ttk.Combobox(root,values=list(PRODUCTS.values()),textvariable=product,width=14,state='readonly')cb_product.grid(row=0,column=1)tk.Label(root,text='Quantity',font=font1).grid(row=0,column=2,padx=10,pady=10)tk.Entry(root,textvariable=qty,width=6).grid(row=0,column=3)tk.Label(root,text='Price',font=font1).grid(row=0,column=4,padx=10,pady=10)tk.Entry(root,textvariable=prc,width=10).grid(row=0,column=5)tk.Button(root,text='Add',command=my_add).grid(row=0,column=6,padx=5)style=ttk.Style(root)if 'clam' in style.theme_names():    style.theme_use('clam')style.configure('Invoice.Treeview',background='azure2',fieldbackground='lightyellow',foreground='black',font=font1,rowheight=30)style.configure('Invoice.Treeview.Heading',background='PowderBlue',font=('Times',12,'bold'))trv=ttk.Treeview(root,columns=('line','p_id','product','qty','price','total'),show='headings',selectmode='browse',height=8,style='Invoice.Treeview')trv.grid(row=1,column=0,columnspan=7,padx=10,pady=5)trv.column('line',width=55,anchor='center')trv.column('p_id',width=75,anchor='center')trv.column('product',width=230,anchor='w')trv.column('qty',width=80,anchor='center')trv.column('price',width=100,anchor='e')trv.column('total',width=110,anchor='e')trv.heading('line',text='Line')trv.heading('p_id',text='Product ID')trv.heading('product',text='Product')trv.heading('qty',text='Quantity')trv.heading('price',text='Rate')trv.heading('total',text='Line Total')b2=tk.Button(root,text='Delete',state=tk.DISABLED,command=data_delete)b2.grid(row=2,column=6,pady=3)tk.Button(root,text='Delete All',command=my_reset).grid(row=3,column=6,pady=3)tk.Label(root,text='Sub Total:',font=font1).grid(row=2,column=4,sticky='e')tk.Label(root,textvariable=sub_total_var,font=font1).grid(row=2,column=5,sticky='e')tk.Label(root,text='Tax 10%:',font=font1).grid(row=3,column=4,sticky='e')tk.Label(root,textvariable=tax_var,font=font1).grid(row=3,column=5,sticky='e')tk.Label(root,text='Total:',font=font2).grid(row=4,column=4,sticky='e')tk.Label(root,textvariable=total_var,font=font2).grid(row=4,column=5,sticky='e',pady=15)tk.Button(root,text='Confirm',font=font1,command=insert_data).grid(row=4,column=2)tk.Label(root,textvariable=msg_var,font=('Times',12),fg='red').grid(row=4,column=0,columnspan=2)trv.bind('<<TreeviewSelect>>',my_select)cb_product.focus_set()root.mainloop()

Optional Header Image 🔝

The original project also displays a logo at the top of the application. Keep the image in the project directory rather than using a path tied to one computer.

my_img=tk.PhotoImage(file='top2.png')image_label=tk.Label(root,image=my_img)image_label.grid(row=0,column=0)

If this image is added, shift the remaining GUI rows accordingly.

Integrate the Product Table 🔝

The current project uses this dictionary:

PRODUCTS={...}

In the next stage, product ID, product name and price can come directly from the MySQL product table. The user then needs to select the product and enter only the quantity.

Common Invoice Project Mistakes 🔝

Using Serial Number as Product ID

A Treeview line number represents display order. A product ID identifies the product. Keep them separate.

Using float for Database Currency Columns

Use fixed-point DECIMAL columns for invoice amounts.

Saving Header and Details Separately without a Transaction

If the detail insert fails after the invoice header is saved, an incomplete invoice can remain. Save both operations in one transaction.

Deleting selection()[0] without Checking Selection

Always test whether selection() contains an item first.

Allowing Zero or Negative Quantity

Validate quantity before inserting the Treeview row.

Allowing Invalid Price Input

Convert the input safely and handle invalid numeric values.

Hard-Coding Tax in Several Functions

Keep one constant:

TAX_RATE=Decimal('0.10')

Using Engine.execute()

Use a SQLAlchemy connection or transaction context for current SQLAlchemy code.

Frequently Asked Questions 🔝

Tkinter Invoice Project Summary 🔝

• Treeview stores the invoice lines.
• Each line has a display line number and a separate product ID.
• Quantity multiplied by unit price gives the line total.
• Decimal is used for money calculations.
• Adding or deleting a row recalculates subtotal, tax and total.
• Invoice lines are renumbered after deletion.
• Input is validated before adding a product.
• The invoice header stores subtotal, tax and final total.
• The detail table stores each product line.
• The generated inv_id connects header and detail records.
• A SQLAlchemy transaction saves the whole invoice atomically.
• MySQL DECIMAL columns are used for monetary values.
• The next project can replace the hard-coded product dictionary with a MySQL product table.