database.py

from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    String,
    Integer,
    Float,
    insert,
    text,
)

# Use a persistent SQLite database file
engine = create_engine("sqlite:///database.db")
metadata_obj = MetaData()

# Define 'receipts' table
receipts = Table(
    "receipts",
    metadata_obj,
    Column("receipt_id", Integer, primary_key=True),
    Column("customer_name", String(16)),
    Column("price", Float),
    Column("tip", Float),
)

# Create the table if it doesn't exist
metadata_obj.create_all(engine)

# Function to insert rows into the table
def insert_rows_into_table(rows, table):
    with engine.begin() as connection:
        connection.execute(insert(table), rows)

# Insert sample data if table is empty
with engine.connect() as conn:
    result = conn.execute(text("SELECT COUNT(*) FROM receipts"))
    count = result.scalar()
    if count == 0:
        rows = [
            {"receipt_id": 1, "customer_name": "Alan Payne", "price": 12.06, "tip": 1.20},
            {"receipt_id": 2, "customer_name": "Alex Mason", "price": 23.86, "tip": 0.24},
            {"receipt_id": 3, "customer_name": "Woodrow Wilson", "price": 53.43, "tip": 5.43},
            {"receipt_id": 4, "customer_name": "Margaret James", "price": 21.11, "tip": 1.00},
        ]
        insert_rows_into_table(rows, receipts)