sql入门：在线零售数据分析平台

一、项目功能

本项目主要包含以下功能：

• 客户管理：添加客户信息

• 产品管理：添加产品并管理库存

• 订单管理：创建订单，并自动减少库存

• 销售报表：统计每个客户的消费情况 & 每月销售情况

• 数据导出：支持 CSV 格式导出报表数据

二、数据库设计

该系统基于 SQLite，主要包含 4 张核心数据表：

1. Customers（客户表）

2. Products（产品表）

3. Orders（订单表）

4. OrderDetails（订单详情表）

5. 此外还设置了一个触发器（Trigger），用以确保订单创建时自动减少库存。

首先插入依赖包

import sqlite3
from datetime import datetime
import csv
import os

​

创建数据库并初始化

DATABASE = 'retail_extended.db'

#这里定义了 connect_db()，用于连接 SQLite 数据库
def connect_db():
    return sqlite3.connect(DATABASE)

• sqlite3 模块：Python 内置模块，用于操作 SQLite 数据库。

• 数据库文件：SQLite 将数据存储在文件中，如果文件不存在，连接时会自动创建。

创建表

def create_tables():
   with connect_db() as conn:
       cursor = conn.cursor()
       #创建客户表
       cursor. execute('''
       CREAT TABLE IF NOT EXISTS Customers(
           customer_id INTEGER RIMARY KEY AUTOINCREMENT,
           customer_name TEXT NOT NULL,
           email TEXT UNIQUE NOT NULL,
           address TEXT
       )
       ''') 
       #创建产品表
       # 创建产品表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS Products (
            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT NOT NULL,
            category TEXT,
            unit_price REAL NOT NULL,
            stock INTEGER NOT NULL
        )
        ''')
        # 创建订单表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS Orders (
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL,
            order_date TEXT NOT NULL,
            total_amount REAL,
            FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
        )
        ''')
        # 创建订单详情表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS OrderDetails (
            order_detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
            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(order_id),
            FOREIGN KEY (product_id) REFERENCES Products(product_id)
        )
        ''') 
# 创建库存更新触发器：每次插入订单详情时自动扣减库存
        cursor.execute('DROP TRIGGER IF EXISTS trg_update_inventory')
        cursor.execute('''
        CREATE TRIGGER trg_update_inventory AFTER INSERT ON OrderDetails
        FOR EACH ROW
        BEGIN
            UPDATE Products
            SET stock = stock - NEW.quantity
            WHERE product_id = NEW.product_id;
        END
        ''')
        conn.commit()
    print("数据表和触发器创建成功！")

• CREATE TABLE IF NOT EXISTS：创建数据表时先判断表是否存在，避免重复创建。

• FOREIGN KEY：在 Orders 和 OrderDetails 表中，我们通过外键关联 Customers 和 Products 表，实现数据关联。

• 触发器：trg_update_inventory 用于在每次新增订单详情时自动扣减产品库存，确保数据一致性。

样例数据插入

def insert_sample_data():
      with connect_db() as conn:
        cursor = conn.cursor()
        # 插入客户数据
        customers = [
            ('Alice', 'alice@example.com', '123 Main St'),
            ('Bob', 'bob@example.com', '456 Elm St'),
            ('Charlie', 'charlie@example.com', '789 Oak St')
        ]
        cursor.executemany('''
        INSERT OR IGNORE INTO Customers (customer_name, email, address)
        VALUES (?, ?, ?)
        ''', customers)

        # 插入产品数据
        products = [
            ('Laptop', 'Electronics', 1200.00, 10),
            ('Smartphone', 'Electronics', 800.00, 20),
            ('Headphones', 'Accessories', 150.00, 50),
            ('Monitor', 'Electronics', 300.00, 15)
        ]
        cursor.executemany('''
        INSERT OR IGNORE INTO Products (product_name, category, unit_price, stock)
        VALUES (?, ?, ?, ?)
        ''', products)
        conn.commit()
    print("样例数据插入成功！")

• executemany() 方法：一次插入多条数据，简化代码。

• INSERT OR IGNORE：当插入数据时，如果违反唯一性约束，则忽略该条数据，防止程序报错。

数据新增操作

def add_customer(customer_name, email, address):
    with connect_db() as conn:
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO Customers (customer_name, email, address)
        VALUES (?, ?, ?)
        ''', (customer_name, email, address))
        conn.commit()
        print(f"添加客户：{customer_name}")



def add_product(product_name, category, unit_price, stock):
    with connect_db() as conn:
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO Products (product_name, category, unit_price, stock)
        VALUES (?, ?, ?, ?)
        ''', (product_name, category, unit_price, stock))
        conn.commit()
        print(f"添加产品：{product_name}")


def add_order(customer_id, order_details):
    """
    添加订单及订单详情
    :param customer_id: 客户ID
    :param order_details: 列表，每个元素为 (product_id, quantity)
    """
    with connect_db() as conn:
        cursor = conn.cursor()
        order_date = datetime.now().strftime("%Y-%m-%d")
        total_amount = 0.0

        # 根据订单详情计算总金额（同时获取单价）
        details = []
        for product_id, quantity in order_details:
            cursor.execute('SELECT unit_price FROM Products WHERE product_id=?', (product_id,))
            result = cursor.fetchone()
            if result:
                unit_price = result[0]
                total_amount += unit_price * quantity
                details.append((product_id, quantity, unit_price))
            else:
                print(f"产品ID {product_id} 不存在，跳过。")

        # 插入订单头
        cursor.execute('''
        INSERT INTO Orders (customer_id, order_date, total_amount)
        VALUES (?, ?, ?)
        ''', (customer_id, order_date, total_amount))
        order_id = cursor.lastrowid

        # 插入订单详情
        for product_id, quantity, unit_price in details:
            cursor.execute('''
            INSERT INTO OrderDetails (order_id, product_id, quantity, unit_price)
            VALUES (?, ?, ?, ?)
            ''', (order_id, product_id, quantity, unit_price))

        conn.commit()
        print(f"订单 {order_id} 添加成功，总金额：{total_amount:.2f}")

• 通过遍历订单详情列表，查询产品单价，并计算订单总金额。

• cursor.lastrowid 用于获取刚刚插入的订单记录的 ID，便于后续插入订单详情。

• 订单详情插入后，触发器会自动更新产品库存。

• 参数化 SQL 语句（使用 ? 占位符）防止 SQL 注入攻击，并提高代码的可读性与可维护性。

报表统计和数据导出

def report_monthly_sales():
    """生成每月销售额统计报告"""
    with connect_db() as conn:
        cursor = conn.cursor()
        cursor.execute('''
        SELECT substr(order_date, 1, 7) AS month, COUNT(order_id) AS order_count, IFNULL(SUM(total_amount), 0) AS total_sales
        FROM Orders
        GROUP BY month
        ORDER BY month
        ''')
        rows = cursor.fetchall()
        print("=== 每月销售额统计 ===")
        for row in rows:
            print(f"月份：{row[0]}，订单数：{row[1]}，销售额：{row[2]:.2f}")
        return rows


def export_report_to_csv(report_data, headers, filename):
    """将报表数据导出到 CSV 文件"""
    with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(headers)
        writer.writerows(report_data)
    print(f"报表数据已导出到 {filename}")

• SQL 聚合函数：使用 SUM() 和 COUNT() 函数对数据进行统计。

• LEFT JOIN：确保所有客户都能显示，即使他们没有订单记录。

• substr() 函数：提取日期的前 7 位（格式为 YYYY-MM），实现按月统计。

• open() 函数：以写入模式打开文件，并指定编码为 UTF-8，避免中文乱码。

• csv.writer：将数据写入 CSV 文件，其中第一行写入表头，其后写入报表数据。

主函数与程序入口

def main():
    # 初始化数据库及表结构
    if os.path.exists(DATABASE):
        os.remove(DATABASE)
    create_tables()
    insert_sample_data()

    # 动态添加客户和产品示例
    add_customer('David', 'david@example.com', '101 Pine St')
    add_product('Keyboard', 'Accessories', 75.00, 30)

    # 添加订单示例
    # 例如：客户ID 1（Alice）购买 1 台 Laptop 和 2 副 Headphones
    add_order(1, [(1, 1), (3, 2)])
    # 另一订单：客户ID 2（Bob）购买 1 部 Smartphone 和 1 个 Monitor
    add_order(2, [(2, 1), (4, 1)])

    # 生成报表
    customer_report = report_customer_spending()
    monthly_report = report_monthly_sales()

    # 导出报表数据到 CSV 文件
    export_report_to_csv(customer_report, ['客户名称', '总消费金额'], 'customer_spending_report.csv')
    export_report_to_csv(monthly_report, ['月份', '订单数', '销售额'], 'monthly_sales_report.csv')


if __name__ == '__main__':
    main()

• os.remove()：在每次运行时删除旧的数据库文件，保证环境干净，有助于调试和测试。

• 主函数将所有功能模块串联，实现系统整体的流程控制。