369 lines
12 KiB
Markdown
369 lines
12 KiB
Markdown
# 第4-4课：SQLAlchemy关系映射与工程实践
|
||
|
||
## 一、本课定位
|
||
|
||
上一课把一张商品表映射成了Python类，并使用Session完成增删改查。本课进入真实业务中更常见的多表场景：一名客户有多张订单，需要同时查询客户信息和订单信息。
|
||
|
||
你已经学习过数据库和Java，因此本课不会重新讲解主键、外键和`JOIN`的基础语法，而是重点说明SQLAlchemy如何表达这些概念，以及它与JPA、MyBatis、MyBatis-Plus之间的差异。
|
||
|
||
## 二、本课目标
|
||
|
||
完成本课后，你能够：
|
||
|
||
1. 使用`ForeignKey`建立数据库外键；
|
||
2. 使用`relationship()`建立Python对象之间的关系；
|
||
3. 映射一对多和多对一关系；
|
||
4. 使用`join()`完成显式联表查询；
|
||
5. 使用数据传输对象（Data Transfer Object，DTO）承载多表查询结果；
|
||
6. 使用`selectinload()`避免N+1查询；
|
||
7. 使用`func.count()`和`group_by()`完成聚合查询；
|
||
8. 理解Repository与事务边界的基本职责。
|
||
|
||
## 三、SQLAlchemy能否实现多表查询
|
||
|
||
可以。SQLAlchemy主要提供两种多表查询方式。
|
||
|
||
### 3.1 查询ORM实体及其关系
|
||
|
||
```python
|
||
statement = (
|
||
select(Customer)
|
||
.options(selectinload(Customer.orders))
|
||
)
|
||
customers = session.scalars(statement).all()
|
||
```
|
||
|
||
查询结果是`Customer`对象，每个客户可以通过`customer.orders`访问订单集合。这种方式类似JPA实体关系查询，适合后续业务逻辑需要完整实体对象的场景。
|
||
|
||
### 3.2 查询指定列并组装DTO
|
||
|
||
```python
|
||
statement = (
|
||
select(Order.order_no, Customer.customer_name, Order.amount)
|
||
.join(Customer, Order.customer_id == Customer.id)
|
||
)
|
||
rows = session.execute(statement).all()
|
||
```
|
||
|
||
这种方式只查询需要的列，再把结果转换成DTO。它更接近MyBatis中编写联表SQL并映射到DTO或VO。
|
||
|
||
两者没有绝对优劣：需要修改完整业务实体时使用ORM实体；列表、报表、统计接口通常更适合DTO投影。
|
||
|
||
## 四、与Java技术体系对照
|
||
|
||
| Python与SQLAlchemy | Java中的近似概念 | 说明 |
|
||
| --- | --- | --- |
|
||
| `ForeignKey` | 数据库外键、JPA `@JoinColumn` | 定义数据库层面的引用约束 |
|
||
| `relationship()` | JPA `@OneToMany`、`@ManyToOne` | 定义对象之间如何导航 |
|
||
| `select()`、`join()` | MyBatis SQL、JPA Criteria/JPQL | 构造查询 |
|
||
| `Session` | JPA `EntityManager` | 管理实体状态和事务工作单元 |
|
||
| `@dataclass` DTO | Java DTO/VO/record | 承载查询输出，不负责持久化 |
|
||
| `selectinload()` | ORM批量预加载 | 减少逐条加载关系产生的查询 |
|
||
|
||
SQLAlchemy不是MyBatis-Plus的完全对应物。它的ORM部分更接近JPA/Hibernate，同时也允许像SQL构造器一样明确选择表、列、连接条件和聚合表达式。
|
||
|
||
## 五、ForeignKey与relationship的区别
|
||
|
||
这是本课最重要的区别。
|
||
|
||
```python
|
||
customer_id: Mapped[int] = mapped_column(
|
||
ForeignKey("course_orm_customer.id"),
|
||
nullable=False,
|
||
)
|
||
|
||
customer: Mapped[Customer] = relationship(back_populates="orders")
|
||
```
|
||
|
||
`ForeignKey`作用在数据库层：它让`order.customer_id`引用`customer.id`，数据库可以阻止无效的客户编号。
|
||
|
||
`relationship()`作用在Python对象层：它让代码可以写成`order.customer`或`customer.orders`。它不会代替数据库外键，也不是数据库中的新列。
|
||
|
||
简化理解：
|
||
|
||
- `customer_id`保存关系；
|
||
- `ForeignKey`约束关系；
|
||
- `relationship()`方便Python代码使用关系。
|
||
|
||
## 六、一对多双向关系
|
||
|
||
父对象的一方：
|
||
|
||
```python
|
||
orders: Mapped[list["Order"]] = relationship(
|
||
back_populates="customer",
|
||
cascade="all, delete-orphan",
|
||
)
|
||
```
|
||
|
||
子对象的一方：
|
||
|
||
```python
|
||
customer: Mapped[Customer] = relationship(back_populates="orders")
|
||
```
|
||
|
||
`back_populates`明确指出两个属性互为反向关系。当执行下面的代码时，SQLAlchemy能够维护两端对象的一致性：
|
||
|
||
```python
|
||
customer.orders.append(order)
|
||
```
|
||
|
||
### 6.1 cascade的含义
|
||
|
||
示例中的`cascade="all, delete-orphan"`表示：
|
||
|
||
- 保存客户时，可以级联保存订单集合中的新订单；
|
||
- 订单从所属客户的集合中移除且不再属于其他父对象时，可以将其删除。
|
||
|
||
级联删除有数据副作用，生产项目中必须结合业务规则决定，不能看到一对多就固定照抄。
|
||
|
||
## 七、DTO是否是查询要件
|
||
|
||
DTO不是联表查询的强制要求。SQLAlchemy可以返回：
|
||
|
||
1. 完整ORM实体；
|
||
2. 多个ORM实体组成的行；
|
||
3. 指定字段组成的`Row`；
|
||
4. 自己构造的`dataclass`、普通类或字典。
|
||
|
||
本课使用不可变`dataclass`定义DTO：
|
||
|
||
```python
|
||
@dataclass(frozen=True)
|
||
class OrderSummaryDTO:
|
||
order_no: str
|
||
customer_name: str
|
||
amount: Decimal
|
||
```
|
||
|
||
DTO适合下列场景：
|
||
|
||
- 页面列表只需要少数字段；
|
||
- 返回结果来自多张表，无法自然归属于单个实体；
|
||
- 统计、分组和报表查询；
|
||
- 希望隔离数据库模型与对外接口模型。
|
||
|
||
DTO不应该调用`session.add()`进行持久化，因为它只是查询结果载体，不是ORM映射实体。
|
||
|
||
## 八、显式联表查询
|
||
|
||
```python
|
||
statement = (
|
||
select(Order.order_no, Customer.customer_name, Order.amount)
|
||
.join(Customer, Order.customer_id == Customer.id)
|
||
.where(Customer.customer_code.like("ORM-C-%"))
|
||
.order_by(Order.order_no)
|
||
)
|
||
rows = session.execute(statement).all()
|
||
```
|
||
|
||
执行顺序可以按SQL理解：
|
||
|
||
1. `select()`决定返回哪些列；
|
||
2. `join()`决定关联哪张表以及关联条件；
|
||
3. `where()`限制数据范围；
|
||
4. `order_by()`决定结果顺序；
|
||
5. `session.execute()`执行语句；
|
||
6. `all()`取得全部结果。
|
||
|
||
这里没有使用字符串拼接，SQLAlchemy会把Python值绑定为SQL参数。
|
||
|
||
## 九、N+1查询问题
|
||
|
||
N+1查询是指：先用1条SQL查询N个客户，随后为了读取每名客户的订单，又追加N条SQL，总计执行N+1条查询。
|
||
|
||
直接访问延迟加载的关系可能出现这个问题：
|
||
|
||
```python
|
||
customers = session.scalars(select(Customer)).all()
|
||
for customer in customers:
|
||
print(customer.orders)
|
||
```
|
||
|
||
本课使用`selectinload()`预加载：
|
||
|
||
```python
|
||
statement = select(Customer).options(selectinload(Customer.orders))
|
||
```
|
||
|
||
它通常先查询客户，再使用一条带`IN`条件的SQL批量查询这些客户的订单，不会为每名客户分别查询一次。
|
||
|
||
常见关系加载策略还有`joinedload()`，它通过连接查询加载关系。集合关系使用连接加载时可能扩大结果行数，因此本课先掌握更直观的`selectinload()`。
|
||
|
||
## 十、聚合查询
|
||
|
||
```python
|
||
statement = (
|
||
select(Customer.customer_name, func.count(Order.id))
|
||
.join(Order, Customer.id == Order.customer_id)
|
||
.group_by(Customer.id, Customer.customer_name)
|
||
)
|
||
```
|
||
|
||
`func.count()`会生成SQL的`COUNT()`，`group_by()`生成`GROUP BY`。统计工作由数据库完成，Python只接收统计结果，不应先查询全部订单再在内存中计数。
|
||
|
||
## 十一、Repository与事务边界
|
||
|
||
Repository（仓储）负责封装数据访问细节，例如查询客户、查询订单摘要。Service（业务服务）负责组织业务流程和决定事务成功或失败。
|
||
|
||
推荐的职责划分：
|
||
|
||
```text
|
||
Service或调用方：开始事务 → 调用多个Repository方法 → 提交或回滚
|
||
Repository：执行查询、增加、修改、删除 → 不擅自commit
|
||
```
|
||
|
||
这与Java项目中`@Transactional`通常放在Service层的思路一致。如果每个Repository方法都自行提交，那么一个跨多个数据操作的业务事务就会被割裂。
|
||
|
||
本课标准示例没有为了展示分层而增加大量类，但其中的数据访问函数都不调用`commit()`，事务由`session_factory.begin()`统一管理。
|
||
|
||
## 十二、完整示例
|
||
|
||
本课完整示例位于：
|
||
|
||
```text
|
||
relationship_query_example.py
|
||
```
|
||
|
||
示例包含：
|
||
|
||
1. `Customer`与`Order`双向关系；
|
||
2. 外键和级联配置；
|
||
3. 可重复执行的数据初始化；
|
||
4. ORM关系对象查询；
|
||
5. DTO显式联表查询；
|
||
6. 分组聚合查询；
|
||
7. 配置异常和数据库异常的分类处理。
|
||
|
||
## 十三、安装与配置
|
||
|
||
如果`python-test`环境已经安装上一课依赖，不需要重复安装。可以先确认：
|
||
|
||
```powershell
|
||
conda activate python-test
|
||
python -c "import sqlalchemy, psycopg; print(sqlalchemy.__version__); print(psycopg.__version__)"
|
||
```
|
||
|
||
如未安装，推荐使用当前解释器对应的pip：
|
||
|
||
```powershell
|
||
python -m pip install -r requirements.txt
|
||
```
|
||
|
||
也可以使用conda安装：
|
||
|
||
```powershell
|
||
conda install -c conda-forge sqlalchemy psycopg
|
||
```
|
||
|
||
注意：激活`python-test`后不要再写`-n base`，否则会尝试修改无权限的公共base环境。
|
||
|
||
复制配置模板：
|
||
|
||
```powershell
|
||
Copy-Item config.example.toml config.toml
|
||
```
|
||
|
||
随后只修改本地`config.toml`。该文件已由项目`.gitignore`忽略，不使用环境变量，也不要把真实密码写入`config.example.toml`或Python代码。
|
||
|
||
## 十四、运行方法与预期结果
|
||
|
||
进入本课目录：
|
||
|
||
```powershell
|
||
cd D:\Code\Python\04_数据库\4_4_SQLAlchemy关系映射与工程实践
|
||
conda activate python-test
|
||
python relationship_query_example.py
|
||
```
|
||
|
||
正常情况下会看到类似结果：
|
||
|
||
```text
|
||
关系对象查询：
|
||
张三
|
||
ORM-O-001|金额：299.00
|
||
ORM-O-002|金额：99.00
|
||
李四
|
||
ORM-O-003|金额：599.00
|
||
DTO联表查询：
|
||
ORM-O-001|张三|金额：299.00
|
||
ORM-O-002|张三|金额：99.00
|
||
ORM-O-003|李四|金额：599.00
|
||
聚合查询：
|
||
张三|订单数量：2
|
||
李四|订单数量：1
|
||
```
|
||
|
||
数据库自动生成的主键可能继续增长，这是序列的正常行为，不代表练习数据发生重复。
|
||
|
||
## 十五、关键代码执行顺序
|
||
|
||
1. 读取本地TOML配置；
|
||
2. 创建`Engine`和连接池；
|
||
3. 创建`sessionmaker`；
|
||
4. `create_all()`创建不存在的练习表；
|
||
5. 在一个事务中清理本课前缀数据并重新新增；
|
||
6. 在独立Session中查询关系对象；
|
||
7. 执行联表查询并构造DTO；
|
||
8. 执行分组统计；
|
||
9. 关闭Session并释放Engine连接池。
|
||
|
||
## 十六、常见错误
|
||
|
||
### 16.1 只写relationship而不写ForeignKey
|
||
|
||
SQLAlchemy通常需要外键判断两张表如何关联。`relationship()`不能代替数据库外键。
|
||
|
||
### 16.2 Session关闭后触发延迟加载
|
||
|
||
关系数据尚未加载就关闭Session，之后访问`customer.orders`可能出现对象已脱离Session的错误。应在Session有效期间使用关系，或提前预加载并转换成DTO。
|
||
|
||
### 16.3 循环中产生N+1查询
|
||
|
||
查询列表后逐个访问延迟加载集合，会产生大量SQL。列表场景应根据需要使用`selectinload()`或明确的联表DTO查询。
|
||
|
||
### 16.4 对DTO执行session.add
|
||
|
||
只有继承声明式基类并完成表映射的ORM实体才能持久化。DTO没有表映射，只负责传输数据。
|
||
|
||
### 16.5 Repository内部随意commit
|
||
|
||
这会破坏上层业务事务。Repository可以执行`flush()`以提前同步SQL，但是否提交应由事务调用方决定。
|
||
|
||
### 16.6 删除父记录时违反外键约束
|
||
|
||
需要先删除子记录，或明确配置数据库/ORM级联规则。级联策略必须符合业务要求。
|
||
|
||
## 十七、课堂练习
|
||
|
||
练习要求位于`practice.py`。你需要独立完成“课程分类—课程”一对多模型，并实现：
|
||
|
||
1. 关系对象查询；
|
||
2. DTO联表查询；
|
||
3. 分类课程数量统计；
|
||
4. 可重复运行的数据初始化；
|
||
5. 清晰的事务边界。
|
||
|
||
本课不在练习文件中提供代码骨架。需要帮助时，可以先询问具体概念或把已完成部分交给我验证。
|
||
|
||
## 十八、本课小结
|
||
|
||
1. `ForeignKey`负责数据库约束，`relationship()`负责对象导航；
|
||
2. SQLAlchemy既能查询完整关联实体，也能显式联表并构造DTO；
|
||
3. DTO不是强制要求，但非常适合列表、报表和跨表结果；
|
||
4. `selectinload()`可以避免常见的N+1查询；
|
||
5. 聚合应尽量交给数据库完成；
|
||
6. Repository负责数据访问，事务边界通常由Service或调用方管理。
|
||
|
||
## 十九、验收标准
|
||
|
||
- 能解释`ForeignKey`与`relationship()`的区别；
|
||
- 能建立一对多双向关系；
|
||
- 能使用关联属性查询子对象集合；
|
||
- 能使用`join()`查询多张表；
|
||
- 能把指定列转换成DTO；
|
||
- 能使用`selectinload()`预加载集合；
|
||
- 能完成分组统计；
|
||
- 程序连续运行两次结果一致且没有重复练习数据；
|
||
- 配置保存在被Git忽略的本地TOML中。
|