# 第4-5课：数据库综合项目——库存订单管理

## 一、本课定位

这是第四阶段的综合项目。本课不再单独讲一个API，而是把前四课知识组合成一个小型业务流程：客户下单后，系统创建订单与订单明细，同时扣减商品库存；只要其中任何一步失败，整张订单和全部库存修改都必须回滚。

项目使用三张独立练习表和`DBP-`、`DBO-`数据前缀，不操作其他课程或业务数据。

## 二、本课目标

完成本课后，你能够：

1. 使用SQLAlchemy 2.x映射三张存在关联关系的表；
2. 使用Repository（仓储）封装数据访问；
3. 使用Service（业务服务）组织下单规则；
4. 在调用方统一控制事务提交和回滚；
5. 使用`SELECT FOR UPDATE`降低并发扣减库存产生的超卖风险；
6. 使用DTO承载三表联查结果；
7. 使用聚合查询统计订单状态；
8. 使用本地TOML配置安全连接PostgreSQL。

## 三、前置知识

- PostgreSQL主键、外键、约束和事务；
- Psycopg连接与参数化查询；
- SQLAlchemy的Engine、连接池和Session；
- 声明式ORM模型及增删改查；
- `ForeignKey`、`relationship()`、`join()`和DTO。

第四课练习即使尚未全部完成，也可以先运行本课标准示例；遇到关系映射或DTO不理解时，再回看第四课对应章节。

## 四、业务模型

```text
Product（商品） 1 ──── N OrderItem（订单明细） N ──── 1 Order（订单）
```

为什么需要订单明细表？因为一张订单可以包含多个商品，一个商品也可以出现在多张订单中。订单与商品本质上是多对多关系，`OrderItem`把它拆成两个一对多关系，并额外保存购买数量和成交单价。

成交单价必须保存在订单明细中。商品价格以后可能变化，但历史订单金额不能随商品当前价格改变。

## 五、项目分层

```text
main() / 事务调用方
        ↓ 创建同一个Session
OrderService
        ↓ 调用
ProductRepository + OrderRepository
        ↓ 操作
SQLAlchemy ORM模型与PostgreSQL
```

### 5.1 Repository

Repository负责查询、增加和修改数据库对象，但不决定什么时候提交：

```python
class OrderRepository:
    def __init__(self, session):
        self.session = session

    def add(self, order):
        self.session.add(order)
```

它与MyBatis项目中的Mapper/DAO职责相近，但操作的是SQLAlchemy的Session和ORM对象。

### 5.2 Service

Service负责业务规则：验证订单、查询并锁定商品、判断库存、扣减库存、计算金额、组装订单。

它不调用`commit()`。因为一个业务用例可能调用多个Repository，必须保证它们处于同一个事务。

### 5.3 事务调用方

```python
with session_factory.begin() as session:
    service = create_order_service(session)
    service.place_order(...)
```

正常离开`with`时提交；异常离开时回滚。`ProductRepository`和`OrderRepository`共享同一个Session，因此库存修改、订单主表和订单明细属于同一个数据库事务。

## 六、成功事务与失败事务

成功订单购买两个键盘和一个鼠标：

```text
验证订单 → 锁定商品 → 扣减库存 → 创建明细 → 创建订单 → 提交
```

失败订单先扣减一个键盘，随后发现鼠标库存不足：

```text
锁定键盘 → 内存中扣减键盘 → 锁定鼠标 → 库存不足 → 抛出异常 → 全部回滚
```

回滚必须撤销第一项商品的扣减，也不能留下不完整的订单。不能在处理每项商品后分别提交。

## 七、为什么使用FOR UPDATE

普通查询后再扣减库存存在并发窗口：两个事务可能同时读到库存5，并各自认为能够购买4件。

```python
statement = (
    select(Product)
    .where(Product.product_code == product_code)
    .with_for_update()
)
```

PostgreSQL会把它转换为`SELECT ... FOR UPDATE`。当前事务结束前，其他需要修改同一行的事务通常需要等待。

这能解决本项目中的典型并发更新问题，但生产系统还需要考虑锁顺序、死锁重试、事务超时、幂等和高并发架构。本课只要求理解悲观锁的基本作用。

## 八、金额为什么使用Decimal

二进制浮点数`float`不能精确表示很多十进制小数，不适合直接保存货币金额。本项目统一使用：

- Python：`Decimal`；
- PostgreSQL：`NUMERIC(10, 2)`或`NUMERIC(12, 2)`。

```python
price=Decimal("399.00")
```

使用字符串创建`Decimal`，避免先经过不精确的浮点数。

## 九、DTO三表联查

订单明细页面同时需要订单、商品和明细字段，不适合把某一个ORM实体直接当作查询结果。

```python
statement = (
    select(
        Order.order_no,
        Product.product_name,
        OrderItem.quantity,
        OrderItem.unit_price,
    )
    .join(OrderItem, Order.id == OrderItem.order_id)
    .join(Product, OrderItem.product_id == Product.id)
)
```

查询结果再转换为`OrderDetailDTO`。这与MyBatis联表SQL映射到DTO/VO的做法非常接近，而且只查询页面真正需要的字段。

## 十、完整示例

标准示例位于：

```text
inventory_order_example.py
```

它包含：

- 三个ORM模型及数据库约束；
- Repository/Service分层；
- 成功下单事务；
- 库存不足事务回滚；
- 悲观锁库存查询；
- 三表联查DTO；
- 订单状态聚合；
- 可重复执行的数据初始化。

## 十一、安装与配置

如果`python-test`环境已经完成前两课SQLAlchemy练习，不需要重复安装。确认版本：

```powershell
conda activate python-test
python -c "import sqlalchemy, psycopg; print(sqlalchemy.__version__); print(psycopg.__version__)"
```

缺少依赖时执行：

```powershell
python -m pip install -r requirements.txt
```

如果第五课继续使用第四课数据库配置，可以在第五课目录执行：

```powershell
Copy-Item ..\4_4_SQLAlchemy关系映射与工程实践\config.toml .\config.toml
```

也可以复制模板后自行填写：

```powershell
Copy-Item config.example.toml config.toml
```

真实配置只保存在被Git忽略的`config.toml`中，不写入环境变量、示例文件或Python代码。

## 十二、运行方法

```powershell
cd D:\Code\Python\04_数据库\4_5_数据库综合项目
conda activate python-test
python inventory_order_example.py
```

正常结果应包括：

```text
初始库存：
DBP-001|机械键盘|价格：399.00|库存：10
DBP-002|无线鼠标|价格：199.00|库存：5
成功订单提交后：
DBP-001|机械键盘|价格：399.00|库存：8
DBP-002|无线鼠标|价格：199.00|库存：4
失败订单已回滚：商品库存不足：DBP-002
失败订单回滚后：
DBP-001|机械键盘|价格：399.00|库存：8
DBP-002|无线鼠标|价格：199.00|库存：4
```

随后会输出两条`DBO-001`订单明细和一条`CREATED|订单数量：1`。连续运行两次时输出应保持一致；数据库序列生成的内部ID继续增长属于正常现象。

## 十三、关键执行顺序

1. 读取本地TOML配置；
2. 创建Engine、连接池和Session工厂；
3. 创建缺失的练习表；
4. 在一个事务中重置练习数据；
5. 查询初始库存；
6. 在一个事务中执行成功下单；
7. 在另一个事务中模拟库存不足并自动回滚；
8. 使用DTO查询订单明细；
9. 使用聚合查询统计订单状态；
10. 释放连接池。

## 十四、常见错误

### 14.1 Repository中直接commit

这会让成功处理的第一项商品提前提交，后续商品失败时无法完整回滚。

### 14.2 每个Repository创建自己的Session

不同Session通常意味着不同事务。订单新增与库存扣减必须共享调用方传入的同一个Session。

### 14.3 捕获异常后不再抛出

如果在事务`with`内部吞掉库存不足异常，上下文会误以为业务成功并提交。应让异常离开事务上下文，再在外层捕获。

### 14.4 使用float计算金额

可能产生精度问题。金额应使用`Decimal`和数据库`NUMERIC`。

### 14.5 只检查库存但不锁定

单人练习时看似正确，并发请求下可能超卖。本课使用`FOR UPDATE`锁定商品行。

### 14.6 直接用当前商品价格展示历史订单

商品后来改价会污染历史数据。订单明细应保存成交时的`unit_price`。

## 十五、课堂练习

练习位于`practice.py`，仍采用前几课的分步形式，只包含题目、预期结果、自查清单和验收标准。请先独立完成，每完成一部分都可以让我验证。

## 十六、本课小结

1. 真实业务写入通常跨越多张表，事务边界应围绕完整业务用例；
2. Repository负责数据访问，Service负责业务规则，调用方负责事务；
3. 多个Repository必须共享同一个Session才能处于同一个事务；
4. `FOR UPDATE`可以在事务中锁定待修改库存；
5. 金额使用`Decimal`与`NUMERIC`；
6. 多表列表查询适合使用DTO；
7. 失败事务必须既不保留订单，也不保留任何库存修改。

## 十七、验收标准

- 三张表的约束、外键和ORM关系正确；
- 成功订单保存订单与明细并扣减库存；
- 库存不足时整个事务回滚；
- Repository、Service和事务职责清晰；
- DTO联表查询及状态统计正确；
- 程序可重复运行且不影响其他数据；
- 配置文件不进入Git；
- 能解释本项目与Java中Mapper/Service/`@Transactional`/DTO的对应关系。