Restaurant management with database integration using Tkinter Version -2

Restaurant Management V-2 with MySQL database

Restaurant Management script using Tkinter with product table from MySQL database for backend

The Part -2 of the script should have these added features

Connect to MySQL database and retrieve data based one of the selected category of products.
The products which are available ( available column in table should have value =1 ) only , should be displayed.
While displaying the product, the price of the product should be shown.
On selection of one of the category option ( by radio buttons ) the product ( menu items ) list should change to reflect the selected category.
Based on the number of products returned by database table the rows and columns should adjust itself.
Number of columns in rows should be easily managed at a central place.

main-db-1.py

import tkinter as tk
from tkinter import *
from tkinter import ttk
from tkinter import BOTH, END, LEFT
from my_connect import my_conn

sb=[]

my_w = tk.Tk()
my_w.geometry("1000x800")

my_w.columnconfigure(0,weight=8)
my_w.columnconfigure(1,weight=2)
my_w.rowconfigure(0, weight=1) 
my_w.rowconfigure(1, weight=14) # change weight to 4
my_w.rowconfigure(2, weight=2)

frame_top=tk.Frame(my_w,bg='white')
frame_bottom=tk.Frame(my_w,bg='white')

frame_m_right=tk.Frame(my_w,bg='#f8fab4')
frame_m_left=tk.Frame(my_w,bg='#284474')

#placing in grid
frame_top.grid(row=0,column=0,sticky='WENS',columnspan=2)
frame_m_left.grid(row=1,column=0,sticky='WENS')
frame_m_right.grid(row=1,column=1,sticky='WENS')
frame_bottom.grid(row=2,column=0,sticky='WENS',columnspan=2)
trv = ttk.Treeview(frame_m_right, selectmode ='browse')
trv.grid(row=0,column=0,columnspan=2,padx=3,pady=2)

# column identifiers 
trv["columns"] = ("1", "2","3")
trv.column("#0", width = 80, anchor ='w')
trv.column("1", width = 60, anchor ='w')
trv.column("2", width =50 , anchor ='c')
trv.column("3", width = 50, anchor ='c')
  
# Headings  
# respective columns
trv.heading("#0", text ="Item",anchor='w')
trv.heading("1", text ="Price",anchor='w')
trv.heading("2", text ="qty",anchor='c')
trv.heading("3", text ="Total",anchor='c')
def my_reset():
    for item in trv.get_children():
        trv.delete(item)
    #for i in range(len(sb)):
    #    sb[i].config(textvariable=0)    # reset spinbox 
    l1=[]
    for i in range(8):
        l1.append(tk.IntVar(value=0))
    for i in range(len(sb)):
        sb[i].config(textvariable=l1[i])

for w in frame_m_right.grid_slaves(1):
        w.grid_remove()
    for w in frame_m_right.grid_slaves(2):
        w.grid_remove()    
    for w in frame_m_right.grid_slaves(3):
        w.grid_remove()
    
def my_bill():
    total=0
    #print(my_menu)
    for item in trv.get_children():
        trv.delete(item)
    for i in range(len(sb)):
        if(int(sb[i].get())>0): 
            price=int(sb[i].get())*my_menu[i][1]
            total=round(total+price,2)
            my_str1=(str(my_menu[i][1]), str(sb[i].get()), str(price))
            trv.insert("",'end',iid=i,text=my_menu[i][0],values=my_str1)
    lr1=tk.Label(frame_m_right,text='Total',font=font1)
    lr1.grid(row=1,column=0,sticky='nw')
    lr2=tk.Label(frame_m_right,text=str(total),font=font1)
    lr2.grid(row=1,column=1,sticky='nw')
    lr21=tk.Label(frame_m_right,text='Tax 10%',font=font1)
    lr21.grid(row=2,column=0,sticky='nw')
    tax=round(0.1*total,2)
    lr22=tk.Label(frame_m_right,text=str(tax),font=font1)
    lr22.grid(row=2,column=1,sticky='nw')
    lr31=tk.Label(frame_m_right,text='Total',font=font2)
    lr31.grid(row=3,column=0,sticky='nw')
    final=round(total+tax,2)
    lr32=tk.Label(frame_m_right,text=str(final),font=font2)
    lr32.grid(row=3,column=1,sticky='nw')
    
        
# Layout is over , sart placing buttons 
path_image="G:\\My Drive\\testing\\plus2_restaurant_v1\\images\\"
font1=('Times',14,'normal')
font2=('Times',32,'bold')
pdx,pdy=1,5
img_top = tk.PhotoImage(file = path_image+"restaurant-3.png")
bg=tk.PhotoImage(file=path_image+'bg2.png')

c1 = tk.Canvas(frame_m_left,width=1000,height=500)
c1.grid(row=0,column=0,columnspan=4,rowspan=4,sticky='nw',padx=0)
c1.create_image(0,0,image=bg,anchor='nw')

img_l1 = tk.Label(frame_top,  image=img_top)
img_l1.grid(row=0,column=0,sticky='nw',pady=1)

img_menu1=tk.PhotoImage(file=path_image+"food-item-11.png")
img_menu2=tk.PhotoImage(file=path_image+"food-item-21.png")
img_menu3=tk.PhotoImage(file=path_image+"food-item-31.png")
img_menu4=tk.PhotoImage(file=path_image+"food-item-41.png")
img_menu5=tk.PhotoImage(file=path_image+"food-item-51.png")
img_menu6=tk.PhotoImage(file=path_image+"food-item-61.png")
img_menu7=tk.PhotoImage(file=path_image+"food-item-71.png")
img_menu8=tk.PhotoImage(file=path_image+"food-item-81.png")
my_menu={} # Dictionary to store items with price
sb=[]
r,c,i=0,0,0
def show_items(cat):
    global r,c,i,my_menu
    my_menu.clear() # remove all items
    sb.clear() 
    r,c,i=0,0,0
    r_set=my_conn.execute("SELECT * FROM plus2_products WHERE \
       available=1 and  p_cat="+str(cat))
    
    for item in r_set: 
        menu=tk.Label(frame_m_left,text=item[1]+'('+str(item[3])+')',font=font1)
        menu.grid(row=r,column=c,padx=pdx,pady=0)    
        r1=r+1
        sbox = tk.Spinbox(frame_m_left,from_=0,to_=5,font=font2,width=1)
        sbox.grid(row=r1,column=c,padx=pdx,pady=0)
        sb.append(sbox)  
        my_menu[i]=[item[1],item[3]]
        i=i+1
        if(c>2): # add one more row 
            c=0
            r=r+2
        else:
            c=c+1

show_items(1)
r=r+1
r1_v = tk.IntVar(value=1) # We used integer variable here

r1 = tk.Radiobutton(frame_bottom, text='Breakfast', variable=r1_v, value=1,command=lambda:show_items(1))
r1.grid(row=r,column=0)

r2 = tk.Radiobutton(frame_bottom, text='Lunch', variable=r1_v, value=0,command=lambda:show_items(2))
r2.grid(row=r,column=1)

r3 = tk.Radiobutton(frame_bottom, text='Dinner', variable=r1_v, value=5,command=lambda:show_items(3))
r3.grid(row=r,column=2)

b1=tk.Button(frame_bottom,text='Get Bill',command=my_bill)
b1.grid(row=r,column=3,padx=10)
b2=tk.Button(frame_bottom,text='Confirm ( Reset)',command=my_reset)
b2.grid(row=r,column=4,padx=10)
my_w.mainloop()

my_connect.py

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:pw@localhost/db_name")

MySQL Installing
MySQL Connecting string
MySQL connection using SQLAlchemy

To create product table plus2_products and to insert sample data, use this sql dump.

CREATE TABLE IF NOT EXISTS `plus2_products` (
  `p_id` int(3) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(100) NOT NULL,
  `unit` varchar(20) NOT NULL,
  `price` float(8,2) NOT NULL,
  `p_cat` int(1) NOT NULL,
  `available` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `plus2_products`
--

INSERT INTO `plus2_products` (`p_id`, `p_name`, `unit`, `price`, `p_cat`, `available`) VALUES
(1, 'Item-1-BF', 'Number', 20.00, 1, 1),
(2, 'Item-2-BF', 'Number', 30.00, 1, 1),
(3, 'Item-3-BF', 'Number', 30.40, 1, 1),
(4, 'Item-4-BF', 'Number', 45.80, 1, 1),
(5, 'Item-5-BF', 'Number', 15.07, 1, 1),
(6, 'Item-6-BF', 'Number', 45.70, 1, 1),
(7, 'Item-7-BF', 'Number', 78.00, 1, 1),
(8, 'Item-8-BF', 'Number', 87.00, 1, 1),
(9, 'Item-9-Lunch', 'Packet', 150.00, 2, 1),
(10, 'Item-10-Lunch', 'One Plate', 180.00, 2, 1),
(11, 'Item-11-Lunch', 'Plate', 50.00, 2, 1),
(12, 'Item-12-Lunch', 'Plate', 90.00, 2, 1),
(13, 'Item-13-Lunch', 'Packet', 90.00, 2, 1),
(14, 'Item-14-Lunch', 'Plate', 110.00, 2, 1),
(15, 'Item-15-Lunch', 'Plate', 120.00, 2, 1),
(16, 'Item-16-Lunch', 'Packet', 200.00, 2, 1),
(17, 'Item-17-Dinner', 'Plate', 545.00, 3, 1),
(18, 'Item-18-Dinner', 'Packet', 500.00, 3, 1),
(19, 'Item-19-Dinner', 'Number', 80.00, 3, 1),
(20, 'Item-20-Dinner', 'Number', 110.00, 3, 1),
(21, 'Item-21-Dinner', 'Plate', 200.00, 3, 1),
(22, 'Item-22-Dinner', 'Packet', 500.00, 3, 1),
(23, 'Item-23-Dinner', 'Plate', 120.00, 3, 1),
(24, 'Item-24-Dinner', 'Plate', 180.00, 3, 1);

Restaurant Management V-1 Restaurant Management V-3
More Projects using Tkinter

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

Restaurant Management System V-2 ( Database Integration)

The Part -2 of the script should have these added features

Subhendu Mohapatra