Creating invoice generation system by selecting products and storing details in Database

Adding products to generate Invoice using Tkinter Treeview

User can select Product from dropdown combobx and then enter quantity and price per unit. On click of the Add button one row with row wise total price ( Unit price x Quantity ) will be inserted as a row in the treeview. At the same time sub-total price and tax will be updated and Sum will be reflected as total price.

Above step will be repeated for each addition or removal of product row from the bill ( Treeview )

my_add()

Increment the Serial number by one and calculate the total by multiplying quantity with unit price. Insert the details to Treeview

def my_add():
    global iid
    iid=iid+1  # Serial number to display 
    total=round(qty.get()*prc.get(),2) # row wise total 
    trv.insert("", 'end',iid=iid, values =(iid,product.get(),qty.get(),prc.get(),total))
    my_upd(trv)

my_upd()

Loop through all rows and calculate the sub total price by adding row wise total. Shows to final price after adding tax of % . This function is called each time if new row ( product ) added or delated.

def my_upd(trv):
    global total 
    total,sub_total=0,0
    for child in trv.get_children():
        sub_total=round(sub_total+float(trv.item(child)["values"][4]),2)
    l6.config(text=str(sub_total)) # shows sub total 
    tax=round(0.1*sub_total,2)  # 10 % tax rate, update here
    l8.config(text=str(tax))  # tax amount is displayed 
    total=round(sub_total+tax,2) # tax added to sub total 
    l10.config(text=str(total))  # Final price is displayed
    product.set('') # reset the combobox 
    qty.set(1)  # reset quantity to 1
    prc.set(0.0) # reset price to 0.0

my_select()

Once a row in Treeview is selected by user this function enables the delete button.

def my_select(self):
    b2.config(state='active')

Data_delete()

Reads the user selected row id and removes the same from the Treeview and call my_upd() to re-calculate the totals.

def data_delete():
    p_id = trv.selection()[0] # collect selected row id
    trv.delete(p_id)
    b2['state']='disabled'
    my_upd(trv) # Update the total

my_reset()

Removes all the rows from Treeview, set all total and taxes to zero and update the displays to zero value.

def my_reset():
    for item in trv.get_children():
        trv.delete(item) # remove row 
    global total
    total=0
    product.set('') # reset combobox
    qty.set(1) # Update quantity to 1 
    prc.set(0.0) # Update price to 0.0
    l6.config(text='0')  # Update display sub total
    l8.config(text='0')  # Update display for tax
    l10.config(text='0') # Update display for total

Insert_data()

Insert the total and todays date to plus2_invoice table. Collects the unique invoice id from the mysql database table and then use the same to add all the products with details like quantity , unity price to plus2_invoice_dtl table.

This function only requires database connection and executes when Confirm button is clicked.
Copy the SQL dump to create the two tables ( plus2_invoice , plus2_invoice_dtl )

def insert_data():
    global total 
    dt = date.today() # Today's date 
    data=(total,dt) # Data for parameterized query
    query="INSERT INTO plus2_invoice ( total, dt) values(%s,%s)"
    #print(query)
    id=my_conn.execute(query,data)
    inv_id=id.lastrowid # get the bill or invoice number after adding data
    
    query="INSERT INTO  plus2_invoice_dtl (inv_id,p_id,product,qty,price) \
	VALUES(%s,%s,%s,%s,%s)"
    my_data=[] # list to store multiple rows of data
    # In all rows inventory id is same
    for line in trv.get_children():
        my_list=trv.item(line)['values']
        my_data.append([inv_id,my_list[0],my_list[1],my_list[2],my_list[3]])
    id=my_conn.execute(query,my_data) # adding list of products to table
    #print("Rows Added  = ",id.rowcount)
    l_msg.config(text='Bill No:'+str(inv_id)+',Products:'+str(id.rowcount))
    l_msg.after(3000, lambda: l_msg.config(text='') )
    my_reset() # reset function

Generating Invoice by adding product, price and quantity using Treeview and storing data in database

SQL Dump of Sample tables

CREATE TABLE `plus2_invoice` (
  `inv_id` int(4) NOT NULL,
  `total` float NOT NULL,
  `dt` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `plus2_invoice_dtl`
--

CREATE TABLE `plus2_invoice_dtl` (
  `dtl_id` int(11) NOT NULL,
  `inv_id` int(6) NOT NULL,
  `p_id` int(4) NOT NULL,
  `product` varchar(50) NOT NULL,
  `qty` int(3) NOT NULL,
  `price` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `plus2_invoice`
--
ALTER TABLE `plus2_invoice`
  ADD PRIMARY KEY (`inv_id`);

--
-- Indexes for table `plus2_invoice_dtl`
--
ALTER TABLE `plus2_invoice_dtl`
  ADD PRIMARY KEY (`dtl_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `plus2_invoice`
--
ALTER TABLE `plus2_invoice`
  MODIFY `inv_id` int(4) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `plus2_invoice_dtl`
--
ALTER TABLE `plus2_invoice_dtl`
  MODIFY `dtl_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

from tkinter import ttk
import tkinter as tk
from  datetime import date
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")   
# Creating tkinter my_w
my_w = tk.Tk()
my_w.geometry("730x500") 
my_w.title("www.plus2net.com") 
font1=['Times',16,'normal'] # font size and style 
font2=['Times',22,'normal']
my_img = tk.PhotoImage(file = "E:\\top2.png") # Use your path
l_img = tk.Label(my_w,  image=my_img ) # show image 
l_img.grid(row=0,column=0,padx=10,pady=5)
l1=tk.Label(my_w,text='Product',font=font1)
l1.grid(row=1,column=0,padx=10,pady=5)
p_list=['Moniter','Mouse','Keyboard','Pen Drive','CPU','Power Unit'] # product list
product=tk.StringVar(my_w)
cb_product = ttk.Combobox(my_w, values=p_list,textvariable=product,width=10)
cb_product.grid(row=1,column=1)
##
l2=tk.Label(my_w,text='Quantity',font=font1)
l2.grid(row=1,column=2,padx=20,pady=10)
qty=tk.IntVar(value=1)
quantity = tk.Entry(my_w, textvariable=qty,width=5)
quantity.grid(row=1,column=3)
##
l3=tk.Label(my_w,text='Price',font=font1)
l3.grid(row=1,column=4,padx=20,pady=10)
prc=tk.DoubleVar()
price = tk.Entry(my_w, textvariable=prc,width=10)
price.grid(row=1,column=5)
##
b1=tk.Button(my_w,text='Add',font=14,command=lambda:my_add())
b1.grid(row=1,column=6)
##
style = ttk.Style(my_w) 
style.theme_use("clam") # set theam to clam
style.configure("Treeview", background="azure2", 
                fieldbackground="lightyellow", foreground="black",font=font1)
style.configure('Treeview.Heading', background="PowderBlue") 
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=2,column=0,columnspan=7,rowspan=2,padx=10,pady=2)
# number of columns
trv["columns"] = ("1", "2", "3","4","5")
trv['show'] = 'headings'
trv.column("1", width = 40, anchor ='c') # width & alignment
trv.column("2", width = 250, anchor ='w')
trv.column("3", width = 70, anchor ='c')
trv.column("4", width = 90, anchor ='e')
trv.column("5", width = 100, anchor ='e')
trv.heading("1", text ="Sl No") # Heading text 
trv.heading("2", text ="Product")
trv.heading("3", text ="Quantity")
trv.heading("4", text ="Rate")  
trv.heading("5", text ="Total")  

l5=tk.Label(my_w,text='Total :',fg='blue',font=font1,anchor='e')
l5.grid(row=4,column=4)
l6=tk.Label(my_w,text='0',fg='blue',font=font1,anchor='e')
l6.grid(row=4,column=5)
l7=tk.Label(my_w,text='Tax 10 % :',fg='blue',font=font1,anchor='e')
l7.grid(row=5,column=4)
l8=tk.Label(my_w,text='0',fg='blue',font=font1,anchor='e')
l8.grid(row=5,column=5)
l9=tk.Label(my_w,text='Total :',fg='red',font=font2,anchor='e')
l9.grid(row=6,column=4)
l10=tk.Label(my_w,text='0',fg='red',font=font2,anchor='e')
l10.grid(row=6,column=5,pady=20)
    
b2=tk.Button(my_w,text='Delete',state='disabled',command=lambda:data_delete())
b2.grid(row=2,column=6)
b3=tk.Button(my_w,text='Del All',command=lambda:my_reset())
b3.grid(row=3,column=6,padx=1)
b4=tk.Button(my_w,text='Confirm ',font=font2,bg='lightyellow',command=lambda:insert_data())
b4.grid(row=6,column=2)
l_msg=tk.Label(my_w,text='',fg='red',font=12)
l_msg.grid(row=6,column=0,columnspan=2)

total,iid=0,0

def my_add():
    global iid
    iid=iid+1  # Serial number to display 
    total=round(qty.get()*prc.get(),2) # row wise total 
    trv.insert("", 'end',iid=iid, values =(iid,product.get(),qty.get(),prc.get(),total))
    my_upd(trv)

def my_upd(trv):
    global total 
    total,sub_total=0,0
    for child in trv.get_children():
        sub_total=round(sub_total+float(trv.item(child)["values"][4]),2)
    l6.config(text=str(sub_total)) # shows sub total 
    tax=round(0.1*sub_total,2)  # 10 % tax rate, update here
    l8.config(text=str(tax))  # tax amount is displayed 
    total=round(sub_total+tax,2) # tax added to sub total 
    l10.config(text=str(total))  # Final price is displayed
    product.set('') # reset the combobox 
    qty.set(1)  # reset quantity to 1
    prc.set(0.0) # reset price to 0.0 
def my_select(self):
    b2.config(state='active') # Delete button is active now 
def data_delete():
    p_id = trv.selection()[0] # collect selected row id
    trv.delete(p_id)  # remove the selected row from Treeview
    b2['state']='disabled' # disable the button 
    my_upd(trv) # Update the total 
def my_reset():
    for item in trv.get_children():
        trv.delete(item) # remove row 
    global total
    total=0
    product.set('') # reset combobox
    qty.set(1) # Update quantity to 1 
    prc.set(0.0) # Update price to 0.0
    l6.config(text='0')  # Update display sub total
    l8.config(text='0')  # Update display for tax
    l10.config(text='0') # Update display for total

def insert_data():
    global total 
    dt = date.today() # Today's date 
    data=(total,dt) # Data for parameterized query
    query="INSERT INTO plus2_invoice ( total, dt) values(%s,%s)"
    #print(query)
    id=my_conn.execute(query,data)
    inv_id=id.lastrowid # get the bill or invoice number after adding data
    
    query="INSERT INTO  plus2_invoice_dtl (inv_id,p_id,product,qty,price) \
         VALUES(%s,%s,%s,%s,%s)"
    my_data=[] # list to store multiple rows of data
    # In all rows inventory id is same
    for line in trv.get_children():
        my_list=trv.item(line)['values']
        my_data.append([inv_id,my_list[0],my_list[1],my_list[2],my_list[3]])
    id=my_conn.execute(query,my_data) # adding list of products to table
    #print("Rows Added  = ",id.rowcount)
    l_msg.config(text='Bill No:'+str(inv_id)+',Products:'+str(id.rowcount))
    l_msg.after(3000, lambda: l_msg.config(text='') )
    my_reset() # reset function 
trv.bind("<<TreeviewSelect>>", my_select)  # User selection of row
my_w.mainloop()

Integration of product table

While selecting the options of the Combobox we can get data from a Product table. Once the user selects the product, the price and the product id ( p_id) is collected. User has to enter the quantity only.
Integration of MySQL Product table to Combobox of Invoice Generation Inserting product details to procurement table

Display MySQL records in Treeview Pagination of MySQL records in Treeview
Displaying MySQL records using Entry or Label Delete MySQL record
Treeview Treeview insert Treeview parent child node Select -Edit-update MySQL Product table using Treeview Query window & displaying records in Treeview

Python Tkinter Dictionary Python- Tutorials

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

Invoice Generation system