Install MySQL Tables for Tkinter Restaurant Project

This installation utility uses Tkinter buttons to create and manage the MySQL tables required by our Restaurant Management project. Python connects to MySQL through SQLAlchemy, while Python exception handling displays database errors without terminating the GUI.

The project uses three tables:

• plus2_products stores restaurant menu items.
• plus2_bill stores one record for each completed bill.
• plus2_sell stores the individual products belonging to each bill.

The installation program can also add sample product data and generate recent sample sales for testing the Restaurant Management report module.

Installation utility
       |
       +--> Create plus2_products
       |        |
       |        +--> Insert sample products
       |
       +--> Create plus2_bill
       |
       +--> Create plus2_sell
       |        |
       |        +--> Generate recent sample sales
       |
       +--> Delete test records
       |
       +--> Drop test tables

Files Used by the Installation Program Top

Keep these files in the same project folder:

install_restaurant.py
my_connect.py
product_data.py

install_restaurant.py contains the Tkinter interface and installation functions.

my_connect.py creates the SQLAlchemy Engine.

product_data.py contains sample restaurant products that can be inserted after creating plus2_products.

MySQL Connection with SQLAlchemy Top

The database Engine is kept in my_connect.py. For more connection examples, see MySQL connection strings and connecting MySQL with SQLAlchemy.

my_connect.py

from sqlalchemy import create_engine

engine=create_engine(
    'mysql+mysqldb://root:pw@localhost/db_name'
)

Only create_engine is required in this file.

The main installation program imports text because that is where the SQL statements are created:

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from my_connect import engine

Create the Restaurant Product Table Top

The plus2_products table stores the menu used in Restaurant Management V-2 and Restaurant Management V-3.

The main fields are:

• p_id: unique product ID.
• p_name: product name.
• unit: Plate, Packet, Number or another unit.
• price: unit price.
• p_cat: meal category.
• available: 1 to show the product and 0 to hide it.

We use DECIMAL(10,2) for the price rather than FLOAT.

CREATE_PRODUCTS_SQL='''CREATE TABLE IF NOT EXISTS plus2_products (
    p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    p_name VARCHAR(100) NOT NULL,
    unit VARCHAR(30) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    p_cat TINYINT UNSIGNED NOT NULL,
    available TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (p_id),
    INDEX idx_product_category_available (p_cat,available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

Create the Table from Python

def create_product_table():
    try:
        with engine.begin() as conn:
            conn.execute(text(CREATE_PRODUCTS_SQL))
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg('Product table is ready.','normal')

This replaces the older:

my_conn.execute(query)

pattern.

Insert Sample Product Data Top

The sample menu remains in a separate product_data.py file. The records are stored in a Python dictionary-based structure so each parameter has a descriptive name.

INSERT_PRODUCT_SQL='''INSERT INTO plus2_products
(p_id,p_name,unit,price,p_cat,available)
VALUES (:p_id,:p_name,:unit,:price,:p_cat,:available)'''

SQLAlchemy can insert all the dictionaries in one executemany operation:

def insert_product_data():
    from product_data import product_data

    try:
        with engine.begin() as conn:
            result=conn.execute(
                text(INSERT_PRODUCT_SQL),
                product_data
            )
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            f'Rows added: {result.rowcount}',
            'normal'
        )

If you click Add Product Data a second time without first deleting the existing rows, MySQL reports duplicate primary-key values. The error is caught and displayed in the Tkinter window.

Delete Product Records Top

DELETE FROM plus2_products removes the rows while keeping the table itself.

DELETE_PRODUCTS_SQL='DELETE FROM plus2_products'

Because this is a destructive operation, the revised interface asks for confirmation before running it.

if not messagebox.askyesno(
    'Confirm Delete',
    'Delete all product records?',
    parent=root
):
    return

The number of deleted rows can be read through:

result.rowcount

DELETE and TRUNCATE Are Different

DELETE FROM plus2_products removes rows and provides a useful affected-row count.

TRUNCATE TABLE plus2_products is a different operation. In MySQL it also resets the table's AUTO_INCREMENT counter and behaves more like a table-level data-definition operation.

For this teaching utility we use DELETE.

Drop the Product Table Top

DROP TABLE removes both the records and the table structure.

DROP_PRODUCTS_SQL='DROP TABLE IF EXISTS plus2_products'

The application asks for confirmation because the table must be created again before product data can be added.

Display MySQL Success and Error Messages Top

The original program already had a useful idea: display a database message for a few seconds and then clear it.

The revised function keeps the same behavior.

def show_msg(message,msg_type='normal'):
    colour='green' if msg_type=='normal' else 'red'
    status_label.config(text=message,fg=colour)

    root.after(
        5000,
        lambda:status_label.config(text='')
    )

Read a SQLAlchemy Error

The old code used:

e.__dict__['orig']

That depends on internal object details. Instead:

def get_error(error):
    original=getattr(error,'orig',None)
    return str(original) if original else str(error)

This displays the original database-driver error when it is available.

Install plus2_bill and plus2_sell Tables Top

The bill and sale tables support the invoice workflow explained in Restaurant Management V-3.

plus2_bill

CREATE_BILL_SQL='''CREATE TABLE IF NOT EXISTS plus2_bill (
    bill_no INT UNSIGNED NOT NULL AUTO_INCREMENT,
    bill_date DATE NOT NULL,
    total DECIMAL(12,2) NOT NULL,
    tax DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (bill_no),
    INDEX idx_bill_date (bill_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

plus2_sell

CREATE_SELL_SQL='''CREATE TABLE IF NOT EXISTS plus2_sell (
    s_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    p_id INT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    bill_no INT UNSIGNED NOT NULL,
    bill_date DATE NOT NULL,
    PRIMARY KEY (s_id),
    INDEX idx_sell_bill (bill_no),
    INDEX idx_sell_product (p_id),
    INDEX idx_sell_date (bill_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

Create Both Transaction Tables

def create_transaction_tables():
    try:
        with engine.begin() as conn:
            conn.execute(text(CREATE_BILL_SQL))
            conn.execute(text(CREATE_SELL_SQL))
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            'Bill and sale tables are ready.',
            'normal'
        )

Generate Recent Sample Sales Top

The report page works best when the database contains records for today and the previous few days.

If we permanently store dates such as those from the original 2022 example, a current-day report returns no data. The revised installation program therefore calculates the dates when the script is run.

bill_date=date.today()-timedelta(days=days_ago)

The sample-data generator:

1. loads available products from MySQL;
2. selects a few products randomly;
3. creates random quantities;
4. calculates the subtotal and sample tax;
5. inserts the bill header;
6. gets the new bill number;
7. inserts the related product rows;
8. repeats the process for several recent days.

The bill and its product rows are stored together:

with engine.begin() as conn:
    result=conn.execute(bill_sql,bill_data)
    bill_no=result.lastrowid

    conn.execute(sell_sql,sale_rows)

This gives the report module fresh data to analyze.

Complete Tkinter Restaurant Installation Program Top

import random
import tkinter as tk
from tkinter import messagebox
from datetime import date, timedelta
from decimal import Decimal, ROUND_HALF_UP
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from my_connect import engine

MONEY=Decimal('0.01')
TAX_RATE=Decimal('0.10')

CREATE_PRODUCTS_SQL='''CREATE TABLE IF NOT EXISTS plus2_products (
    p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    p_name VARCHAR(100) NOT NULL,
    unit VARCHAR(30) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    p_cat TINYINT UNSIGNED NOT NULL,
    available TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (p_id),
    INDEX idx_product_category_available (p_cat,available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

CREATE_BILL_SQL='''CREATE TABLE IF NOT EXISTS plus2_bill (
    bill_no INT UNSIGNED NOT NULL AUTO_INCREMENT,
    bill_date DATE NOT NULL,
    total DECIMAL(12,2) NOT NULL,
    tax DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (bill_no),
    INDEX idx_bill_date (bill_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

CREATE_SELL_SQL='''CREATE TABLE IF NOT EXISTS plus2_sell (
    s_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    p_id INT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    bill_no INT UNSIGNED NOT NULL,
    bill_date DATE NOT NULL,
    PRIMARY KEY (s_id),
    INDEX idx_sell_bill (bill_no),
    INDEX idx_sell_product (p_id),
    INDEX idx_sell_date (bill_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''

INSERT_PRODUCT_SQL='''INSERT INTO plus2_products
(p_id,p_name,unit,price,p_cat,available)
VALUES (:p_id,:p_name,:unit,:price,:p_cat,:available)'''

SELECT_PRODUCTS_SQL='''SELECT p_id,price
FROM plus2_products
WHERE available=:available
ORDER BY p_id'''

INSERT_BILL_SQL='''INSERT INTO plus2_bill
(total,tax,bill_date)
VALUES (:total,:tax,:bill_date)'''

INSERT_SELL_SQL='''INSERT INTO plus2_sell
(p_id,price,quantity,bill_no,bill_date)
VALUES (:p_id,:price,:quantity,:bill_no,:bill_date)'''

root=tk.Tk()
root.geometry('760x420')
root.title('Restaurant Database Installation - plus2net')

def get_error(error):
    original=getattr(error,'orig',None)
    return str(original) if original else str(error)

def show_msg(message,msg_type='normal'):
    colour='green' if msg_type=='normal' else 'red'
    status_label.config(text=message,fg=colour)

    root.after(
        5000,
        lambda:status_label.config(text='')
    )

def create_product_table():
    try:
        with engine.begin() as conn:
            conn.execute(text(CREATE_PRODUCTS_SQL))
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg('Product table is ready.')

def insert_product_data():
    from product_data import product_data

    try:
        with engine.begin() as conn:
            result=conn.execute(
                text(INSERT_PRODUCT_SQL),
                product_data
            )
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            f'Product rows added: {result.rowcount}'
        )

def delete_product_records():
    if not messagebox.askyesno(
        'Confirm Delete',
        'Delete all product records?',
        parent=root
    ):
        return

    try:
        with engine.begin() as conn:
            result=conn.execute(
                text('DELETE FROM plus2_products')
            )
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            f'Product rows removed: {result.rowcount}'
        )

def drop_product_table():
    if not messagebox.askyesno(
        'Confirm Drop',
        'Drop plus2_products table?',
        parent=root
    ):
        return

    try:
        with engine.begin() as conn:
            conn.execute(
                text('DROP TABLE IF EXISTS plus2_products')
            )
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg('Product table removed.')

def create_transaction_tables():
    try:
        with engine.begin() as conn:
            conn.execute(text(CREATE_BILL_SQL))
            conn.execute(text(CREATE_SELL_SQL))
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg('Bill and sale tables are ready.')

def insert_sample_sales():
    try:
        with engine.connect() as conn:
            rows=conn.execute(
                text(SELECT_PRODUCTS_SQL),
                {'available':1}
            ).mappings().all()
    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
        return

    products=[
        {
            'p_id':row['p_id'],
            'price':Decimal(str(row['price']))
        }
        for row in rows
    ]

    if not products:
        show_msg(
            'Add product data before creating sample sales.',
            'error'
        )
        return

    bills_created=0

    try:
        with engine.begin() as conn:

            for days_ago in range(6):
                bill_date=date.today()-timedelta(days=days_ago)

                for bill_index in range(2):
                    item_count=min(
                        random.randint(2,4),
                        len(products)
                    )

                    selected=random.sample(
                        products,
                        item_count
                    )

                    subtotal=Decimal('0.00')
                    sale_rows=[]

                    for product in selected:
                        quantity=random.randint(1,5)

                        line_total=(
                            product['price']*quantity
                        ).quantize(
                            MONEY,
                            rounding=ROUND_HALF_UP
                        )

                        subtotal+=line_total

                        sale_rows.append({
                            'p_id':product['p_id'],
                            'price':product['price'],
                            'quantity':quantity,
                            'bill_date':bill_date
                        })

                    subtotal=subtotal.quantize(
                        MONEY,
                        rounding=ROUND_HALF_UP
                    )

                    tax=(subtotal*TAX_RATE).quantize(
                        MONEY,
                        rounding=ROUND_HALF_UP
                    )

                    result=conn.execute(
                        text(INSERT_BILL_SQL),
                        {
                            'total':subtotal,
                            'tax':tax,
                            'bill_date':bill_date
                        }
                    )

                    bill_no=result.lastrowid

                    for sale_row in sale_rows:
                        sale_row['bill_no']=bill_no

                    conn.execute(
                        text(INSERT_SELL_SQL),
                        sale_rows
                    )

                    bills_created+=1

    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            f'Sample bills created: {bills_created}'
        )

def delete_sales_data():
    if not messagebox.askyesno(
        'Confirm Delete',
        'Delete all bill and sale test data?',
        parent=root
    ):
        return

    try:
        with engine.begin() as conn:
            sell_result=conn.execute(
                text('DELETE FROM plus2_sell')
            )

            bill_result=conn.execute(
                text('DELETE FROM plus2_bill')
            )

    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            f'Removed {sell_result.rowcount} sale rows and {bill_result.rowcount} bills.'
        )

def drop_transaction_tables():
    if not messagebox.askyesno(
        'Confirm Drop',
        'Drop plus2_sell and plus2_bill tables?',
        parent=root
    ):
        return

    try:
        with engine.begin() as conn:
            conn.execute(
                text('DROP TABLE IF EXISTS plus2_sell')
            )

            conn.execute(
                text('DROP TABLE IF EXISTS plus2_bill')
            )

    except SQLAlchemyError as e:
        show_msg(get_error(e),'error')
    else:
        show_msg(
            'Bill and sale tables removed.'
        )

title_label=tk.Label(
    root,
    text='Restaurant Management\nDatabase Installation',
    fg='blue',
    font=('Times',24,'bold')
)
title_label.grid(
    row=0,
    column=0,
    columnspan=4,
    padx=5,
    pady=20
)

tk.Button(
    root,
    text='Create Product Table',
    command=create_product_table
).grid(
    row=1,
    column=0,
    padx=5,
    pady=5,
    sticky='ew'
)

tk.Button(
    root,
    text='Add Product Data',
    command=insert_product_data
).grid(
    row=1,
    column=1,
    padx=5,
    pady=5,
    sticky='ew'
)

tk.Button(
    root,
    text='Delete Product Records',
    command=delete_product_records
).grid(
    row=1,
    column=2,
    padx=5,
    pady=5,
    sticky='ew'
)

tk.Button(
    root,
    text='Drop Product Table',
    command=drop_product_table
).grid(
    row=1,
    column=3,
    padx=5,
    pady=5,
    sticky='ew'
)

tk.Button(
    root,
    text='Create Bill/Sale Tables',
    command=create_transaction_tables
).grid(
    row=2,
    column=0,
    padx=5,
    pady=15,
    sticky='ew'
)

tk.Button(
    root,
    text='Add Recent Sample Sales',
    command=insert_sample_sales
).grid(
    row=2,
    column=1,
    padx=5,
    pady=15,
    sticky='ew'
)

tk.Button(
    root,
    text='Delete Bill/Sale Data',
    command=delete_sales_data
).grid(
    row=2,
    column=2,
    padx=5,
    pady=15,
    sticky='ew'
)

tk.Button(
    root,
    text='Drop Bill/Sale Tables',
    command=drop_transaction_tables
).grid(
    row=2,
    column=3,
    padx=5,
    pady=15,
    sticky='ew'
)

status_label=tk.Label(
    root,
    font=('Times',12,'normal'),
    wraplength=700
)
status_label.grid(
    row=3,
    column=0,
    columnspan=4,
    padx=10,
    pady=20
)

for column in range(4):
    root.columnconfigure(
        column,
        weight=1
    )

root.mainloop()

product_data.py Top

The product data is kept separate from the installation logic.

from decimal import Decimal

product_data=[
    {'p_id':1,'p_name':'Item-1-BF','unit':'Number','price':Decimal('20.00'),'p_cat':1,'available':1},
    {'p_id':2,'p_name':'Item-2-BF','unit':'Number','price':Decimal('30.00'),'p_cat':1,'available':1},
    {'p_id':3,'p_name':'Item-3-BF','unit':'Number','price':Decimal('30.40'),'p_cat':1,'available':1},
    {'p_id':4,'p_name':'Item-4-BF','unit':'Number','price':Decimal('45.80'),'p_cat':1,'available':1},
    {'p_id':5,'p_name':'Item-5-BF','unit':'Number','price':Decimal('15.07'),'p_cat':1,'available':1},
    {'p_id':6,'p_name':'Item-6-BF','unit':'Number','price':Decimal('45.70'),'p_cat':1,'available':1},
    {'p_id':7,'p_name':'Item-7-BF','unit':'Number','price':Decimal('78.00'),'p_cat':1,'available':1},
    {'p_id':8,'p_name':'Item-8-BF','unit':'Number','price':Decimal('87.00'),'p_cat':1,'available':1},

    {'p_id':9,'p_name':'Item-9-Lunch','unit':'Packet','price':Decimal('150.00'),'p_cat':2,'available':1},
    {'p_id':10,'p_name':'Item-10-Lunch','unit':'One Plate','price':Decimal('180.00'),'p_cat':2,'available':1},
    {'p_id':11,'p_name':'Item-11-Lunch','unit':'Plate','price':Decimal('50.00'),'p_cat':2,'available':1},
    {'p_id':12,'p_name':'Item-12-Lunch','unit':'Plate','price':Decimal('90.00'),'p_cat':2,'available':1},
    {'p_id':13,'p_name':'Item-13-Lunch','unit':'Packet','price':Decimal('90.00'),'p_cat':2,'available':1},
    {'p_id':14,'p_name':'Item-14-Lunch','unit':'Plate','price':Decimal('110.00'),'p_cat':2,'available':1},
    {'p_id':15,'p_name':'Item-15-Lunch','unit':'Plate','price':Decimal('120.00'),'p_cat':2,'available':1},
    {'p_id':16,'p_name':'Item-16-Lunch','unit':'Packet','price':Decimal('200.00'),'p_cat':2,'available':1},

    {'p_id':17,'p_name':'Item-17-Dinner','unit':'Plate','price':Decimal('545.00'),'p_cat':3,'available':1},
    {'p_id':18,'p_name':'Item-18-Dinner','unit':'Packet','price':Decimal('500.00'),'p_cat':3,'available':1},
    {'p_id':19,'p_name':'Item-19-Dinner','unit':'Number','price':Decimal('80.00'),'p_cat':3,'available':1},
    {'p_id':20,'p_name':'Item-20-Dinner','unit':'Number','price':Decimal('110.00'),'p_cat':3,'available':1},
    {'p_id':21,'p_name':'Item-21-Dinner','unit':'Plate','price':Decimal('200.00'),'p_cat':3,'available':1},
    {'p_id':22,'p_name':'Item-22-Dinner','unit':'Packet','price':Decimal('500.00'),'p_cat':3,'available':1},
    {'p_id':23,'p_name':'Item-23-Dinner','unit':'Plate','price':Decimal('120.00'),'p_cat':3,'available':1},
    {'p_id':24,'p_name':'Item-24-Dinner','unit':'Plate','price':Decimal('180.00'),'p_cat':3,'available':1}
]

Important Database Notes Top

Create the Tables in the Correct Order

A simple installation sequence is:

1. Create Product Table
2. Add Product Data
3. Create Bill/Sale Tables
4. Add Recent Sample Sales
5. Open the Report Module

Delete Sale Rows Before Bill Rows

When clearing transaction data, the program deletes plus2_sell first and plus2_bill second. This also prepares the design for a future version where foreign-key relationships may be added.

DDL and MySQL Transactions

The examples use engine.begin() for consistent SQLAlchemy connection handling. However, MySQL implicitly commits many data-definition statements such as CREATE TABLE and DROP TABLE. Do not depend on rollback to undo these DDL operations.

Use the Installation Utility on a Test Database

The Delete and Drop buttons are deliberately included because this is an installation and learning utility. Do not provide destructive database-management controls like these to ordinary users of a production restaurant application.

Frequently Asked Questions Top

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: