第4-5课：数据库综合项目——库存订单管理
一、本课定位
这是第四阶段的综合项目。本课不再单独讲一个API，而是把前四课知识组合成一个小型业务流程：客户下单后，系统创建订单与订单明细，同时扣减商品库存；只要其中任何一步失败，整张订单和全部库存修改都必须回滚。
项目使用三张独立练习表和DBP-、DBO-数据前缀，不操作其他课程或业务数据。
二、本课目标
完成本课后，你能够：
- 使用SQLAlchemy 2.x映射三张存在关联关系的表；
- 使用Repository（仓储）封装数据访问；
- 使用Service（业务服务）组织下单规则；
- 在调用方统一控制事务提交和回滚；
- 使用
SELECT FOR UPDATE降低并发扣减库存产生的超卖风险； - 使用DTO承载三表联查结果；
- 使用聚合查询统计订单状态；
- 使用本地TOML配置安全连接PostgreSQL。
三、前置知识
- PostgreSQL主键、外键、约束和事务；
- Psycopg连接与参数化查询；
- SQLAlchemy的Engine、连接池和Session；
- 声明式ORM模型及增删改查；
ForeignKey、relationship()、join()和DTO。
第四课练习即使尚未全部完成，也可以先运行本课标准示例；遇到关系映射或DTO不理解时，再回看第四课对应章节。
四、业务模型
Product（商品） 1 ──── N OrderItem（订单明细） N ──── 1 Order（订单）
为什么需要订单明细表？因为一张订单可以包含多个商品，一个商品也可以出现在多张订单中。订单与商品本质上是多对多关系，OrderItem把它拆成两个一对多关系，并额外保存购买数量和成交单价。
成交单价必须保存在订单明细中。商品价格以后可能变化，但历史订单金额不能随商品当前价格改变。
五、项目分层
main() / 事务调用方
↓ 创建同一个Session
OrderService
↓ 调用
ProductRepository + OrderRepository
↓ 操作
SQLAlchemy ORM模型与PostgreSQL
5.1 Repository
Repository负责查询、增加和修改数据库对象，但不决定什么时候提交：
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 事务调用方
with session_factory.begin() as session:
service = create_order_service(session)
service.place_order(...)
正常离开with时提交；异常离开时回滚。ProductRepository和OrderRepository共享同一个Session，因此库存修改、订单主表和订单明细属于同一个数据库事务。
六、成功事务与失败事务
成功订单购买两个键盘和一个鼠标：
验证订单 → 锁定商品 → 扣减库存 → 创建明细 → 创建订单 → 提交
失败订单先扣减一个键盘，随后发现鼠标库存不足：
锁定键盘 → 内存中扣减键盘 → 锁定鼠标 → 库存不足 → 抛出异常 → 全部回滚
回滚必须撤销第一项商品的扣减，也不能留下不完整的订单。不能在处理每项商品后分别提交。
七、为什么使用FOR UPDATE
普通查询后再扣减库存存在并发窗口：两个事务可能同时读到库存5，并各自认为能够购买4件。
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)。
price=Decimal("399.00")
使用字符串创建Decimal，避免先经过不精确的浮点数。
九、DTO三表联查
订单明细页面同时需要订单、商品和明细字段，不适合把某一个ORM实体直接当作查询结果。
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的做法非常接近，而且只查询页面真正需要的字段。
十、完整示例
标准示例位于：
inventory_order_example.py
它包含：
- 三个ORM模型及数据库约束；
- Repository/Service分层；
- 成功下单事务；
- 库存不足事务回滚；
- 悲观锁库存查询；
- 三表联查DTO；
- 订单状态聚合；
- 可重复执行的数据初始化。
十一、安装与配置
如果python-test环境已经完成前两课SQLAlchemy练习，不需要重复安装。确认版本：
conda activate python-test
python -c "import sqlalchemy, psycopg; print(sqlalchemy.__version__); print(psycopg.__version__)"
缺少依赖时执行：
python -m pip install -r requirements.txt
如果第五课继续使用第四课数据库配置，可以在第五课目录执行：
Copy-Item ..\4_4_SQLAlchemy关系映射与工程实践\config.toml .\config.toml
也可以复制模板后自行填写：
Copy-Item config.example.toml config.toml
真实配置只保存在被Git忽略的config.toml中，不写入环境变量、示例文件或Python代码。
十二、运行方法
cd D:\Code\Python\04_数据库\4_5_数据库综合项目
conda activate python-test
python inventory_order_example.py
正常结果应包括：
初始库存：
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继续增长属于正常现象。
十三、关键执行顺序
- 读取本地TOML配置；
- 创建Engine、连接池和Session工厂；
- 创建缺失的练习表；
- 在一个事务中重置练习数据；
- 查询初始库存；
- 在一个事务中执行成功下单；
- 在另一个事务中模拟库存不足并自动回滚；
- 使用DTO查询订单明细；
- 使用聚合查询统计订单状态；
- 释放连接池。
十四、常见错误
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，仍采用前几课的分步形式，只包含题目、预期结果、自查清单和验收标准。请先独立完成，每完成一部分都可以让我验证。
十六、本课小结
- 真实业务写入通常跨越多张表，事务边界应围绕完整业务用例；
- Repository负责数据访问，Service负责业务规则，调用方负责事务；
- 多个Repository必须共享同一个Session才能处于同一个事务；
FOR UPDATE可以在事务中锁定待修改库存；- 金额使用
Decimal与NUMERIC； - 多表列表查询适合使用DTO；
- 失败事务必须既不保留订单，也不保留任何库存修改。
十七、验收标准
- 三张表的约束、外键和ORM关系正确；
- 成功订单保存订单与明细并扣减库存；
- 库存不足时整个事务回滚；
- Repository、Service和事务职责清晰；
- DTO联表查询及状态统计正确；
- 程序可重复运行且不影响其他数据；
- 配置文件不进入Git；
- 能解释本项目与Java中Mapper/Service/
@Transactional/DTO的对应关系。