毕业项目 shop-db 设计与验收 · Python 数据库实战

第 17 章 · 毕业项目 shop-db 设计与验收

本章目标：独立交付 shop-db 数据库毕业项目：完整 ER 图、DDL、Alembic 迁移链、SQLAlchemy 2.0 models 与 seed；全链路贯彻 slug + is_published + price（分） 契约；按 100 分验收表自评；编写 docs/ 衔接 django-web / flask-web / fastapi-web 的 ORM 对照说明；形成可放入作品集的数据库设计文档。

学时建议：6～8 小时（含 4 小时建模与迁移）

前置：完成 ch01～ch16；db-demo 综合练习已通过；建议浏览三 Web 模块商品模型章节。

17.1 项目背景与边界

shop-db 是虚构商城 贤紫优选 的 数据库层毕业交付，为后续 Web 框架毕业项目提供 唯一真相源（SSOT）：

┌─────────────────────────────────────────────────────────┐
│                    shop-db（本章）                         │
│  ER · DDL · Alembic · SQLAlchemy models · seed · docs   │
└───────────────────────────┬─────────────────────────────┘
                            │ 同 ER / 同字段契约
        ┌───────────────────┼───────────────────┐
        ▼                   ▼                   ▼
  django-web           flask-web           fastapi-web
  shop-demo            api-demo            svc-demo
  Django ORM           Flask-SQLAlchemy    SQLAlchemy async

模块	shop-db 必须	不做（扩展）
用户	users 表 + slug	OAuth 第三方表
商品	products slug/is_published/price分	SKU 矩阵多规格
分类	categories 树形	无限级性能优化
订单	orders + order_items 快照	分库分表落地
购物车	cart_items	分布式购物车
迁移	Alembic 全链	多 DB 联邦

严禁真实连接串、生产域名入库。统一 user-demo、api.example.com、本地 MySQL。

17.2 交付物清单

#	交付物	说明
1	`docs/ER.md`	ER 图（mermaid 或图片）+ 文字说明
2	`sql/schema.sql`	可独立执行的 DDL
3	`alembic/versions/*.py`	≥2 个 revision（初始 + 增量）
4	`shop_db/models/*.py`	SQLAlchemy 2.0 模型
5	`scripts/seed.py`	样本数据
6	`docs/ORM_BRIDGE.md`	三框架 ORM 对照
7	`docs/SELF_REVIEW.md`	100 分自评表
8	`README.md`	安装、迁移、seed 命令

17.3 推荐目录结构

~/python-learn/shop-db/
├── .env.example
├── alembic.ini
├── alembic/
│   ├── env.py
│   └── versions/
│       ├── 001_initial_schema.py
│       └── 002_add_product_view_count.py
├── shop_db/
│   ├── __init__.py
│   ├── base.py
│   ├── models/
│   │   ├── user.py
│   │   ├── catalog.py
│   │   ├── order.py
│   │   └── cart.py
│   └── session.py
├── sql/
│   └── schema.sql
├── scripts/
│   └── seed.py
├── docs/
│   ├── ER.md
│   ├── ORM_BRIDGE.md
│   ├── SCALE.md          ← ch14 扩展预案
│   ├── CACHE.md          ← ch15
│   └── SELF_REVIEW.md
├── requirements.txt
└── README.md

17.4 ER 模型总览

17.4.1 实体关系（mermaid）

erDiagram
    users ||--o{ orders : places
    users ||--o{ cart_items : owns
    categories ||--o{ products : contains
    products ||--o{ cart_items : referenced
    orders ||--|{ order_items : contains
    products ||--o{ order_items : snapshot

    users {
        bigint id PK
        varchar slug UK
        varchar email UK
        varchar password_hash
        datetime created_at
    }
    categories {
        bigint id PK
        varchar slug UK
        varchar name
        bigint parent_id FK
    }
    products {
        bigint id PK
        varchar slug UK
        varchar name
        bigint price "分"
        bool is_published
        bigint category_id FK
        bigint view_count
    }
    orders {
        bigint id PK
        varchar order_no UK
        varchar user_slug
        varchar status
        bigint total_amount "分"
    }
    order_items {
        bigint id PK
        bigint order_id FK
        varchar product_slug
        bigint price "分"
        int quantity
    }
    cart_items {
        bigint id PK
        varchar user_slug
        bigint product_id FK
        int quantity
    }

17.4.2 设计原则

原则	实现
价格	全程 BIGINT 分
商品标识	slug 对外；id 对内 FK
上下架	is_published bool
订单快照	order_items 冗余 slug/name/price
用户	user_slug 便于与 Web JWT 对齐

17.5 完整 DDL（schema.sql 摘要）

CREATE DATABASE IF NOT EXISTS shop_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE shop_db;

CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_users_slug (slug),
    UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE categories (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(128) NOT NULL,
    name VARCHAR(128) NOT NULL,
    parent_id BIGINT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_categories_slug (slug),
    KEY idx_categories_parent (parent_id),
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(128) NOT NULL,
    name VARCHAR(256) NOT NULL,
    description TEXT NULL,
    price BIGINT NOT NULL COMMENT '价格-分',
    is_published TINYINT(1) NOT NULL DEFAULT 0,
    category_id BIGINT NULL,
    view_count BIGINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_products_slug (slug),
    KEY idx_products_published_id (is_published, id DESC),
    KEY idx_products_category_published (category_id, is_published, id DESC),
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_slug VARCHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'pending',
    total_amount BIGINT NOT NULL COMMENT '订单总额-分',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_orders_order_no (order_no),
    KEY idx_orders_user_created (user_slug, created_at DESC),
    KEY idx_orders_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_slug VARCHAR(128) NOT NULL,
    product_name VARCHAR(256) NOT NULL,
    price BIGINT NOT NULL COMMENT '成交单价-分',
    quantity INT NOT NULL,
    KEY idx_order_items_order (order_id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

CREATE TABLE cart_items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_slug VARCHAR(64) NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cart_user_product (user_slug, product_id),
    KEY idx_cart_user (user_slug),
    CONSTRAINT fk_cart_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

17.6 SQLAlchemy 2.0 Models

17.6.1 base.py

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import func, DateTime
from datetime import datetime

class Base(DeclarativeBase):
    pass

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now(), onupdate=func.now()
    )

17.6.2 catalog.py — Product 核心

from sqlalchemy import BigInteger, Boolean, ForeignKey, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship
from shop_db.base import Base, TimestampMixin

class Category(Base):
    __tablename__ = "categories"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    slug: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    parent_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("categories.id"))
    products: Mapped[list["Product"]] = relationship(back_populates="category")

class Product(Base, TimestampMixin):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    slug: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    price: Mapped[int] = mapped_column(BigInteger, nullable=False)  # 分
    is_published: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    category_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("categories.id"))
    view_count: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    category: Mapped[Category | None] = relationship(back_populates="products")

17.6.3 order.py

class Order(Base, TimestampMixin):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    order_no: Mapped[str] = mapped_column(String(32), unique=True, nullable=False)
    user_slug: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="pending")
    total_amount: Mapped[int] = mapped_column(BigInteger, nullable=False)
    items: Mapped[list["OrderItem"]] = relationship(back_populates="order")

class OrderItem(Base):
    __tablename__ = "order_items"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    order_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("orders.id"))
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_slug: Mapped[str] = mapped_column(String(128), nullable=False)
    product_name: Mapped[str] = mapped_column(String(256), nullable=False)
    price: Mapped[int] = mapped_column(BigInteger, nullable=False)
    quantity: Mapped[int] = mapped_column(nullable=False)
    order: Mapped[Order] = relationship(back_populates="items")

17.7 Alembic 迁移链

cd shop-db
alembic init alembic   # 若未初始化
# env.py 中 target_metadata = Base.metadata
alembic revision --autogenerate -m "initial schema"
alembic revision --autogenerate -m "add product view_count"
alembic upgrade head

001_initial_schema.py 应创建 全部 6 表；002 可合并 ch16 的 view_count（若已在 001 含则可改为 add_index 增量）。

验收：

alembic current
alembic history --verbose
mysql -e "SHOW TABLES FROM shop_db;"