Installation of Tables in Restaurant Management System

Tkinter Restaurant Management V-1 V-2 V-3 Report

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 messaage 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


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

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.

Allways 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
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





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