agentlearning_repo/01_dataanalyst/database_build.py

"""
数据库初始化脚本：创建 analytics_demo 库和表结构，插入演示数据。

使用方式：python database_build.py
"""

import mysql.connector
from config import DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME


def init_database():
    """创建数据库、表结构并插入演示数据。"""
    # 先连接 MySQL（不指定数据库），确保目标库存在
    conn = mysql.connector.connect(
        host=DB_HOST,
        port=int(DB_PORT),
        user=DB_USER,
        password=DB_PASSWORD,
    )
    cursor = conn.cursor()
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME} DEFAULT CHARSET utf8mb4")
    conn.database = DB_NAME

    # ---- 建表 ----
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS employees (
            id          INT PRIMARY KEY AUTO_INCREMENT,
            name        VARCHAR(50)  NOT NULL,
            department  VARCHAR(50)  NOT NULL,
            salary      DECIMAL(10,2) NOT NULL,
            hire_date   DATE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id           INT PRIMARY KEY AUTO_INCREMENT,
            product_name VARCHAR(100) NOT NULL,
            category     VARCHAR(50)  NOT NULL,
            price        DECIMAL(10,2) NOT NULL,
            stock        INT DEFAULT 0
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id           INT PRIMARY KEY AUTO_INCREMENT,
            employee_id  INT NOT NULL,
            product_id   INT NOT NULL,
            quantity     INT NOT NULL,
            order_date   DATE NOT NULL,
            FOREIGN KEY (employee_id) REFERENCES employees(id),
            FOREIGN KEY (product_id)  REFERENCES products(id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)

    # ---- 插入演示数据 ----
    employees_data = [
        (1, "张三", "技术部",   20000.00, "2023-01-15"),
        (2, "李四", "销售部",   11000.00, "2023-02-20"),
        (3, "王五", "技术部",   16000.00, "2022-11-10"),
        (4, "赵六", "人力资源", 5000.00, "2023-03-01"),
        (5, "钱七", "销售部",   17000.00, "2022-12-05"),
    ]

    products_data = [
        (1, "笔记本电脑", "电子产品", 6999.00, 500),
        (2, "机械键盘",   "电子产品", 399.00,  1000),
        (3, "办公椅",     "办公用品", 499.00,  300),
        (4, "显示器",     "电子产品", 1200.00, 400),
    ]

    orders_data = [
        (1, 1, 1, 2,  "2024-01-15"),
        (2, 2, 2, 15, "2024-01-16"),
        (3, 3, 1, 10, "2024-01-17"),
        (4, 5, 3, 6,  "2024-01-18"),
        (5, 2, 4, 5,  "2024-01-19"),
    ]

    cursor.executemany("INSERT IGNORE INTO employees VALUES (%s,%s,%s,%s,%s)", employees_data)
    cursor.executemany("INSERT IGNORE INTO products  VALUES (%s,%s,%s,%s,%s)", products_data)
    cursor.executemany("INSERT IGNORE INTO orders    VALUES (%s,%s,%s,%s,%s)", orders_data)

    conn.commit()
    conn.close()

    print("✅ 数据库初始化完成")
    print("   - employees 表：5 条员工记录")
    print("   - products  表：4 条产品记录")
    print("   - orders    表：5 条订单记录")


if __name__ == "__main__":
    init_database()