Installing tables to database with sample data for Inserting Product sales data with with Bill number to generate Invoice- Restaurant Management

Restaurant Management Installation of table error message

Creating MySQL product table then adding records deleting and dropping table from Tkinter

Adding product table with data

We will use error handling in python to read and display the message returned by MySQL after executing the query.
MySQL Installing
MySQL SQLAlchemy with Error reporting
On click of the button to create product table , the function my_p_table() is executed. Inside this function the query to create table is executed. Error message if any is posted to show_msg() to display the information.

def my_p_table(): # 
    query="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 "
    try:
        rs=my_conn.execute(query) # run query to create table
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error') # send error message 
    else:
        msg_display="Product Table is Created  "
        show_msg(msg_display,'normal')# success message

Restaurant Management table create message

Adding data to product table

There are some sample data kept in a separate file product_data.py here inside the file data is stored inside a list and the same is called from the main file install-p-table-data.py
product_data.py

product_data=[(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),
----
----
(23, 'Item-23-Dinner', 'Plate', 120.00, 3, 1),
(24, 'Item-24-Dinner', 'Plate', 180.00, 3, 1)]

This data is collected in our main file install-p-table-data.py and inserted to table by using query.

def my_p_data():
    from product_data import product_data
    query="INSERT INTO `plus2_products`\
         (`p_id`, `p_name`, `unit`, `price`, `p_cat`, `available`) \
        VALUES (%s,%s,%s,%s,%s,%s)"
    try:
        rs=my_conn.execute(query,product_data)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="Number of rows added :" + str(rs.rowcount)
        show_msg(msg_display,'normal')

Number of records inserted to the table is displayed by using rowcount

Deleting records from table

Inside the function delete_recs() two types of queries we can use. The result is same but by using DELETE FROM table we can display number of records deleted.

def delete_recs():
    #query="TRUNCATE table plus2_products"
    query="DELETE FROM  plus2_products"
    try:
        rs=my_conn.execute(query)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="Number of records removed :" + str(rs.rowcount)
        show_msg(msg_display,'normal')

Deleting table

The table with data can be removed while deleting table.

def delete_table(): 
    query="DROP TABLE  plus2_products"
    try:
        rs=my_conn.execute(query)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="product table deleted " 
        show_msg(msg_display,'normal')

All the above functions we trigger by using different buttons. In all functions we call the message function show_msg() with parameters to display error or sucess messages to user.

Displaying Messages with time delay

We will show message for 3 seconds and then remove the same ( check the last line using after()). Here we have used red colour font ( fg='red') to show error messages and green colour font ( fg='green') for normal messages returned by MySQL database.

def show_msg(msg_display,type): # to show message to user 
    if(type=='normal'):
        l1.config(text=msg_display,fg='green')
    else:
        l1.config(text=msg_display,fg='red')
    my_w.after(3000,lambda:l1.config(text=''))

install-p-table-data.py

import tkinter as tk
from my_connect import my_conn
from sqlalchemy.exc import SQLAlchemyError
my_w = tk.Tk()
my_w.geometry("400x250")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title
def my_p_table(): # 
    query="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 "
    try:
        rs=my_conn.execute(query) # run query to create table
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error') # send error message 
    else:
        msg_display="Product Table is Created  "
        show_msg(msg_display,'normal')# success message

def my_p_data():
    from product_data import product_data
    query="INSERT INTO `plus2_products`\
         (`p_id`, `p_name`, `unit`, `price`, `p_cat`, `available`) \
        VALUES (%s,%s,%s,%s,%s,%s)"
    try:
        rs=my_conn.execute(query,product_data)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="Number of rows added :" + str(rs.rowcount)
        show_msg(msg_display,'normal')
def delete_recs():
    #query="TRUNCATE table plus2_products"
    query="DELETE FROM  plus2_products"
    try:
        rs=my_conn.execute(query)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="Number of records removed :" + str(rs.rowcount)
        show_msg(msg_display,'normal')
def delete_table(): 
    query="DROP TABLE  plus2_products"
    try:
        rs=my_conn.execute(query)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        msg_display=error
        show_msg(msg_display,'error')
    else:
        msg_display="product table deleted " 
        show_msg(msg_display,'normal')
def show_msg(msg_display,type): # to show message to user 
    if(type=='normal'):
        l1.config(text=msg_display,fg='green')
    else:
        l1.config(text=msg_display,fg='red')
    my_w.after(3000,lambda:l1.config(text=''))
l_top=tk.Label(my_w,text='Restaurant Management : \n Installation of Tables ',
        fg='blue',font=('Times',24,'bold'))
l_top.grid(row=0,column=0,columnspan=4,padx=1,pady=20)
b1=tk.Button(my_w,text='Create Product table',command=my_p_table)
b1.grid(row=1,column=0,padx=1,pady=5)

b2=tk.Button(my_w,text='Add Data',command=my_p_data)
b2.grid(row=1,column=1,padx=1,pady=5)

b3=tk.Button(my_w,text='Delete records ',command=delete_recs)
b3.grid(row=1,column=2,padx=1,pady=5)

b4=tk.Button(my_w,text='Delete Tables ',command=delete_table)
b4.grid(row=1,column=3,padx=1,pady=5)

l1=tk.Label(my_w,font=('Times',12,'normal'))
l1.grid(row=3,column=0,padx=0,columnspan=4)

my_w.mainloop()  # Keep the window open

my_connect.py

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

product_data.py

product_data=[(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)]

Installing plus2_sell & plus2_bill tables

Restaurant Management Installation of bill and sell tables

Bills & sale table installation with sample data for the date column in Restaurant Management script

Using the similar concepts we can add few more buttons to add two more tables. These tables ( plus2_sell and plus2_bill ) are having date columns to store the transaction date.

While creating these tables and adding data, we will be using today's date and previous 5 or 6 days date to the table. If you are running the code after few days of installation then you will not get any data for current day or yesterday data.

Always take a fresh data after deleting previous days data to run the report module.
MySQL Installing
MySQL Connecting string
MySQL connection using SQLAlchemy

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

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Installation of Tables in Restaurant Management System