Skip to content

A curated collection of practical AI projects implementing OCR systems, RAG, AI agents, and other AI use cases.

9.8 KB
Raw Blame
"""
SQLite e-commerce database schema definition and sample data seeder.
Run this file directly to (re)create the database: python database.py
"""

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "data" / "ecommerce.db"

CREATE_TABLES = [
    """CREATE TABLE IF NOT EXISTS customers (
        id           INTEGER PRIMARY KEY,
        name         TEXT    NOT NULL,
        email        TEXT    NOT NULL,
        city         TEXT,
        country      TEXT,
        signup_date  TEXT
    )""",
    """CREATE TABLE IF NOT EXISTS products (
        id       INTEGER PRIMARY KEY,
        name     TEXT    NOT NULL,
        category TEXT    NOT NULL,
        price    REAL    NOT NULL,
        stock    INTEGER NOT NULL DEFAULT 0
    )""",
    """CREATE TABLE IF NOT EXISTS orders (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL,
        order_date  TEXT    NOT NULL,
        status      TEXT    NOT NULL,
        FOREIGN KEY (customer_id) REFERENCES customers(id)
    )""",
    """CREATE TABLE IF NOT EXISTS order_items (
        id         INTEGER PRIMARY KEY,
        order_id   INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        quantity   INTEGER NOT NULL,
        unit_price REAL    NOT NULL,
        FOREIGN KEY (order_id)   REFERENCES orders(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    )""",
]

CUSTOMERS = [
    (1,  "Alice Johnson",    "[email protected]",    "New York",      "US",  "2022-03-10"),
    (2,  "Bob Martinez",     "[email protected]",      "Los Angeles",   "US",  "2022-05-22"),
    (3,  "Clara Schmidt",    "[email protected]",    "Berlin",        "DE",  "2022-07-14"),
    (4,  "David Chen",       "[email protected]",    "Shanghai",      "CN",  "2022-08-01"),
    (5,  "Eva Rossi",        "[email protected]",      "Milan",         "IT",  "2022-09-30"),
    (6,  "Frank Dubois",     "[email protected]",    "Paris",         "FR",  "2022-11-05"),
    (7,  "Grace Kim",        "[email protected]",    "Seoul",         "KR",  "2023-01-18"),
    (8,  "Henry Brown",      "[email protected]",    "London",        "UK",  "2023-02-27"),
    (9,  "Isabelle Nguyen",  "[email protected]", "Toronto",       "CA",  "2023-04-03"),
    (10, "James Wilson",     "[email protected]",    "Sydney",        "AU",  "2023-05-11"),
    (11, "Karen Davis",      "[email protected]",    "Chicago",       "US",  "2023-06-15"),
    (12, "Luis Fernandez",   "[email protected]",     "Madrid",        "ES",  "2023-07-20"),
    (13, "Mia Tanaka",       "[email protected]",      "Tokyo",         "JP",  "2023-08-08"),
    (14, "Noah Anderson",    "[email protected]",     "Boston",        "US",  "2023-09-14"),
    (15, "Olivia Taylor",    "[email protected]",   "Amsterdam",     "NL",  "2023-10-22"),
    (16, "Paul Müller",      "[email protected]",     "Munich",        "DE",  "2023-11-30"),
    (17, "Quinn Patel",      "[email protected]",    "Mumbai",        "IN",  "2024-01-07"),
    (18, "Rachel Lee",       "[email protected]",   "San Francisco", "US",  "2024-02-19"),
    (19, "Samuel Okafor",    "[email protected]",   "Lagos",         "NG",  "2024-03-03"),
    (20, "Tina Kowalski",    "[email protected]",     "Warsaw",        "PL",  "2024-04-11"),
]

PRODUCTS = [
    # Electronics
    (1,  "Wireless Headphones",     "Electronics",   89.99,  120),
    (2,  "Bluetooth Speaker",       "Electronics",   49.99,  200),
    (3,  "USB-C Hub",               "Electronics",   34.99,  350),
    (4,  "Mechanical Keyboard",     "Electronics",  109.99,   80),
    (5,  "4K Webcam",               "Electronics",   79.99,   90),
    (6,  "Portable Charger",        "Electronics",   29.99,  400),
    # Clothing
    (7,  "Merino Wool Sweater",     "Clothing",      69.99,  160),
    (8,  "Running Shoes",           "Clothing",      94.99,  210),
    (9,  "Waterproof Jacket",       "Clothing",     129.99,   75),
    (10, "Cotton T-Shirt",          "Clothing",      19.99,  500),
    (11, "Slim Fit Chinos",         "Clothing",      54.99,  180),
    # Books
    (12, "Clean Code",              "Books",         35.99,  300),
    (13, "Designing Data-Intensive Applications", "Books", 49.99, 250),
    (14, "The Pragmatic Programmer","Books",         42.99,  280),
    (15, "Deep Learning with Python","Books",        44.99,  190),
    (16, "Atomic Habits",           "Books",         18.99,  450),
    # Home & Garden
    (17, "Bamboo Desk Organizer",   "Home & Garden", 24.99,  310),
    (18, "Ceramic Plant Pot",       "Home & Garden", 14.99,  420),
    (19, "LED Desk Lamp",           "Home & Garden", 39.99,  150),
    (20, "Memory Foam Pillow",      "Home & Garden", 44.99,  200),
    (21, "Stainless Steel Water Bottle","Home & Garden",22.99,380),
    # Sports
    (22, "Yoga Mat",                "Sports",        32.99,  260),
    (23, "Resistance Bands Set",    "Sports",        18.99,  330),
    (24, "Jump Rope",               "Sports",         9.99,  500),
    (25, "Foam Roller",             "Sports",        27.99,  175),
]

ORDERS = [
    # (id, customer_id, order_date, status)
    (1,   1, "2024-01-05", "delivered"),
    (2,   2, "2024-01-08", "delivered"),
    (3,   3, "2024-01-12", "delivered"),
    (4,   4, "2024-01-15", "delivered"),
    (5,   5, "2024-01-20", "delivered"),
    (6,   6, "2024-02-01", "delivered"),
    (7,   7, "2024-02-05", "delivered"),
    (8,   8, "2024-02-10", "delivered"),
    (9,   9, "2024-02-14", "delivered"),
    (10, 10, "2024-02-18", "delivered"),
    (11,  1, "2024-02-22", "delivered"),
    (12,  2, "2024-03-01", "delivered"),
    (13,  3, "2024-03-05", "delivered"),
    (14, 11, "2024-03-10", "delivered"),
    (15, 12, "2024-03-15", "delivered"),
    (16, 13, "2024-03-20", "delivered"),
    (17, 14, "2024-03-25", "delivered"),
    (18, 15, "2024-04-01", "delivered"),
    (19, 16, "2024-04-05", "delivered"),
    (20, 17, "2024-04-10", "shipped"),
    (21,  1, "2024-04-12", "shipped"),
    (22,  5, "2024-04-14", "shipped"),
    (23,  8, "2024-04-16", "processing"),
    (24, 18, "2024-04-18", "processing"),
    (25, 19, "2024-04-19", "processing"),
    (26, 20, "2024-04-20", "pending"),
    (27,  3, "2024-04-21", "pending"),
    (28,  7, "2024-04-22", "pending"),
    (29, 10, "2024-04-23", "pending"),
    (30,  2, "2024-04-23", "cancelled"),
    (31,  6, "2024-03-28", "cancelled"),
    (32,  9, "2024-03-02", "delivered"),
    (33, 11, "2024-03-08", "delivered"),
    (34, 13, "2024-02-25", "delivered"),
    (35, 14, "2024-01-30", "delivered"),
    (36, 15, "2024-02-08", "delivered"),
    (37, 16, "2024-01-22", "delivered"),
    (38, 17, "2024-03-18", "delivered"),
    (39, 18, "2024-02-28", "delivered"),
    (40, 19, "2024-04-02", "shipped"),
]

ORDER_ITEMS = [
    # (id, order_id, product_id, quantity, unit_price)
    (1,  1,  1, 1, 89.99),
    (2,  1,  6, 2, 29.99),
    (3,  2,  8, 1, 94.99),
    (4,  2, 10, 3, 19.99),
    (5,  3,  4, 1,109.99),
    (6,  3, 12, 1, 35.99),
    (7,  4,  2, 2, 49.99),
    (8,  4, 18, 3, 14.99),
    (9,  5,  9, 1,129.99),
    (10, 5, 22, 1, 32.99),
    (11, 6,  3, 2, 34.99),
    (12, 6, 19, 1, 39.99),
    (13, 7,  5, 1, 79.99),
    (14, 7, 15, 1, 44.99),
    (15, 8, 13, 1, 49.99),
    (16, 8, 16, 2, 18.99),
    (17, 9,  7, 2, 69.99),
    (18, 9, 21, 1, 22.99),
    (19,10, 11, 1, 54.99),
    (20,10, 24, 2,  9.99),
    (21,11,  1, 1, 89.99),
    (22,11, 14, 1, 42.99),
    (23,12,  8, 1, 94.99),
    (24,12, 23, 2, 18.99),
    (25,13,  4, 1,109.99),
    (26,13, 17, 2, 24.99),
    (27,14,  2, 1, 49.99),
    (28,14, 20, 1, 44.99),
    (29,15,  9, 1,129.99),
    (30,15, 25, 1, 27.99),
    (31,16,  5, 1, 79.99),
    (32,16, 12, 2, 35.99),
    (33,17,  6, 3, 29.99),
    (34,17, 16, 1, 18.99),
    (35,18,  3, 2, 34.99),
    (36,18, 22, 1, 32.99),
    (37,19, 15, 1, 44.99),
    (38,19, 10, 5, 19.99),
    (39,20,  7, 1, 69.99),
    (40,20, 18, 2, 14.99),
    (41,21,  1, 2, 89.99),
    (42,21,  3, 1, 34.99),
    (43,22,  9, 1,129.99),
    (44,22, 24, 3,  9.99),
    (45,23, 13, 1, 49.99),
    (46,23, 21, 2, 22.99),
    (47,24,  4, 1,109.99),
    (48,24, 19, 1, 39.99),
    (49,25,  8, 1, 94.99),
    (50,25, 11, 2, 54.99),
    (51,26, 16, 3, 18.99),
    (52,26, 23, 1, 18.99),
    (53,27,  2, 1, 49.99),
    (54,27, 17, 1, 24.99),
    (55,28,  5, 1, 79.99),
    (56,28, 20, 1, 44.99),
    (57,29, 14, 2, 42.99),
    (58,29, 25, 1, 27.99),
    (59,30,  6, 2, 29.99),
    (60,31, 12, 1, 35.99),
    (61,32,  7, 1, 69.99),
    (62,32, 10, 4, 19.99),
    (63,33,  1, 1, 89.99),
    (64,33, 22, 2, 32.99),
    (65,34,  9, 1,129.99),
    (66,34, 15, 1, 44.99),
    (67,35,  4, 1,109.99),
    (68,35,  3, 2, 34.99),
    (69,36, 13, 1, 49.99),
    (70,36, 16, 2, 18.99),
    (71,37,  8, 1, 94.99),
    (72,37, 24, 2,  9.99),
    (73,38,  2, 2, 49.99),
    (74,38, 21, 1, 22.99),
    (75,39,  5, 1, 79.99),
    (76,39, 11, 1, 54.99),
    (77,40,  6, 3, 29.99),
    (78,40, 18, 2, 14.99),
]


def init_db():
    """Create tables and seed sample data if the database doesn't exist yet."""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys = ON")
    cursor = conn.cursor()

    for stmt in CREATE_TABLES:
        cursor.execute(stmt)
    conn.commit()

    # Only seed if tables are empty
    if cursor.execute("SELECT COUNT(*) FROM customers").fetchone()[0] == 0:
        cursor.executemany(
            "INSERT INTO customers VALUES (?,?,?,?,?,?)", CUSTOMERS
        )
        cursor.executemany(
            "INSERT INTO products VALUES (?,?,?,?,?)", PRODUCTS
        )
        cursor.executemany(
            "INSERT INTO orders VALUES (?,?,?,?)", ORDERS
        )
        cursor.executemany(
            "INSERT INTO order_items VALUES (?,?,?,?,?)", ORDER_ITEMS
        )

    conn.commit()
    conn.close()


if __name__ == "__main__":
    init_db()
    print(f"✅ Database created at {DB_PATH}")
    conn = sqlite3.connect(DB_PATH)
    for table in ["customers", "products", "orders", "order_items"]:
        count = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
        print(f"   {table}: {count} rows")
    conn.close()