SQLAlchemy 技术研究性教程（二）

最新推荐文章于 2025-12-02 20:26:41 发布

原创最新推荐文章于 2025-12-02 20:26:41 发布 · 432 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

原创文章同时被 3 个专栏收录

540 篇文章

订阅专栏

渗透测试

448 篇文章

订阅专栏

数据引擎

336 篇文章

订阅专栏

为面向对象编程思维而生的数据库优雅访问：SQLAlchemy

本文章仅提供学习，切勿将其用于不法手段！

——从“基础 CRUD”到“数据库迁移 & 性能调优”的高阶实战

前言：为啥要学这篇续篇？

上一篇文章我们搞定了 SQLAlchemy 的“增删改查”“关系映射”，能用 Python 类优雅地操作数据库了。但实际开发中，还有两个“拦路虎”：

表结构会变：今天加个字段，明天改个类型，手动写 ALTER TABLE 太麻烦，还容易出错；
数据量变大：用户从 100 涨到 10 万，原来秒开的查询突然卡成“PPT”，咋优化？

这篇续篇就来解决这两个问题——用 Alembic 做数据库迁移（让表结构变更“自动化”），用 SQLAlchemy 性能调优技巧（让查询“飞”起来）。依然是大白话+实战例子，包教包会！

第六章：数据库迁移神器 Alembic——表结构变更不求人

6.1 什么是数据库迁移？为什么需要它？

假设你刚上线一个项目，用户表 user 只有 id 和 name。运行一个月后，产品经理说：“加个 email 字段吧，要存用户邮箱！”

如果没有迁移工具，你得：

手动连数据库执行 ALTER TABLE user ADD COLUMN email VARCHAR(100);
改 User 类的代码（加 email = Column(String(100))）
确保线上、测试环境的数据库都改一遍（漏了一个环境就完蛋）

如果有迁移工具（比如 Alembic），你只需要：

改 User 类代码（加 email 字段）；
敲一行命令生成“迁移脚本”；
敲一行命令同步到所有数据库。

迁移工具的核心作用：记录表结构的“变更历史”，让团队在不同环境（开发、测试、生产）中保持表结构一致，还能一键“回滚”到旧版本（比如加字段后发现 bug，能快速撤掉）。

6.2 Alembic 安装与初始化

Alembic 是 SQLAlchemy 官方推荐的迁移工具，安装超简单：

pip install alembic

然后需要在项目根目录初始化 Alembic（生成配置文件）：

# 初始化（会在当前目录创建 alembic 文件夹和 alembic.ini 文件）  
alembic init alembic

6.3 配置 Alembic：让它认识你的数据库和模型

初始化后，需要改两个关键配置：

（1）改 `alembic.ini`：数据库连接地址

打开 alembic.ini，找到 sqlalchemy.url，改成你的数据库连接字符串（和 create_engine 的参数一样）：

# 原配置（可能是空的）  
sqlalchemy.url = driver://user:pass@localhost/dbname  

# 改成你的数据库（比如 SQLite）  
sqlalchemy.url = sqlite:///test.db  

# 如果是 MySQL（记得先装 pymysql）  
# sqlalchemy.url = mysql+pymysql://root:123456@localhost/test_db

（2）改 `alembic/env.py`：让 Alembic 识别你的 ORM 模型

Alembic 需要知道你的 Base 类（ORM 模型的基类）才能生成迁移脚本。打开 alembic/env.py，找到 target_metadata 这一行，替换成你的 Base.metadata：

# 原代码（可能是 None）  
# target_metadata = None  

# 替换成你的 Base 元数据（从 demo.py 里导入 Base）  
from your_demo_module import Base  # 假设你的 demo.py 里定义了 Base，这里要写正确的导入路径  
target_metadata = Base.metadata

注意：如果你的 demo.py 和 alembic 文件夹在同一目录，可以直接 from demo import Base；如果在子目录，需要调整 Python 路径（比如 sys.path.append("../")）。

6.4 生成第一个迁移脚本：“加个 email 字段”

现在我们来实战：给 user 表加个 email 字段。

步骤1：改 ORM 模型（User 类）

先修改 demo.py 里的 User 类，加 email 字段：

class User(Base):  
    __tablename__ = "user"  
    id = Column(Integer, primary_key=True, autoincrement=True)  
    name = Column(String(50), nullable=False)  
    age = Column(Integer)  
    email = Column(String(100), unique=True)  # 新增：邮箱，唯一  

    def __repr__(self):  
        return f"<User(id={self.id}, name='{self.name}', age={self.age}, email='{self.email}')>"

步骤2：生成迁移脚本（Alembic 自动对比差异）

在终端执行：

alembic revision --autogenerate -m "add email column to user table"

--autogenerate：让 Alembic 自动对比 target_metadata（你的模型）和数据库当前表结构，生成变更脚本；
-m：备注这次迁移的目的（必填，方便以后看历史）。

执行成功后，alembic/versions 文件夹会多一个 .py 文件（比如 xxxx_add_email_column_to_user_table.py），里面记录了“要做什么变更”。

步骤3：检查迁移脚本（重要！避免踩坑）

打开生成的脚本，重点看 upgrade() 和 downgrade() 函数：

def upgrade():  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.add_column('user', sa.Column('email', sa.String(length=100), nullable=True, unique=True))  
    # ### end Alembic commands ###  

def downgrade():  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.drop_column('user', 'email')  
    # ### end Alembic commands ###

upgrade()：升级时执行（比如加字段）；
downgrade()：回滚时执行（比如删字段）。

注意：如果 nullable=True 不符合你的预期（比如你想让 email 非空），需要手动改脚本（把 nullable=True 改成 nullable=False），否则迁移可能失败（已有数据的表不能加非空字段）。

步骤4：执行迁移（同步到数据库）

确认脚本没问题后，执行：

alembic upgrade head

head 表示“最新的迁移版本”，即执行所有未应用的迁移脚本。

执行成功后，打开数据库看 user 表，已经多了 email 字段！

6.5 回滚迁移：出错了怎么办？

如果加字段后发现 bug，想回滚到之前的状态，执行：

# 回滚到上一个版本（比如刚加 email 的前一个版本）  
alembic downgrade -1  

# 回滚到指定版本（比如版本号是 xxxx）  
alembic downgrade xxxx

本章重点：

Alembic 是 SQLAlchemy 的迁移工具，解决表结构变更的“自动化”和“版本控制”问题。
核心流程：改模型 → 生成脚本 → 检查脚本 → 执行迁移 → 必要时回滚。
必须检查自动生成的脚本（尤其是 nullable 等约束），避免数据丢失或迁移失败！

第七章：性能调优——当查询变慢时，这么做就对了

数据量小的时候，ORM 随便查都快；但数据量上去了（比如 10 万+用户），原来的 session.query(User).all() 可能 3 秒才返回结果。这时候需要从“SQL 层面”和“ORM 层面”双管齐下优化。

7.1 先诊断：到底是哪一步慢了？

优化前要先“定位瓶颈”，推荐两个工具：

（1）SQLAlchemy 自带的“SQL 日志”

打开 create_engine 的日志功能，能看到 SQLAlchemy 实际执行的 SQL：

import logging  
logging.basicConfig()  
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)  # 打印所有 SQL  

engine = create_engine("sqlite:///test.db", echo=False)  # echo=True 也能打印，但 logging 更灵活

运行代码后，终端会输出类似 SELECT * FROM user 的 SQL，你可以复制到数据库客户端（如 Navicat）执行，看耗时。

（2）数据库自带的“慢查询日志”

比如 MySQL 可以开启慢查询日志，记录执行时间超过阈值（如 1 秒）的 SQL，精准定位“拖后腿”的查询。

7.2 常见性能问题与解决方案

问题1：查询全表数据（N+1 查询）

场景：查 100 个用户，每个用户又查一次他的订单（user.orders），结果发了 101 次 SQL（1 次查用户 + 100 次查订单）。

现象：SQL 日志里一堆 SELECT * FROM order WHERE user_id=?。

解决：用 joinedload 预加载关联数据（“连表查”代替“多次单查”）：

from sqlalchemy.orm import joinedload  

# 优化前（N+1 查询）  
users = session.query(User).all()  
for user in users:  
    print(user.orders)  # 每次循环都查一次 order 表  

# 优化后（1 次 JOIN 查询）  
users = session.query(User).options(joinedload(User.orders)).all()  # 预加载 orders  
for user in users:  
    print(user.orders)  # 不再发额外 SQL

问题2：查询不必要的字段（SELECT *）

场景：只需要用户的 name 和 age，却查了所有字段（id、name、age、email）。

现象：SQL 日志里是 SELECT id, name, age, email FROM user。

解决：用 with_entities 指定需要的字段（“按需取数”）：

# 优化前（查所有字段）  
users = session.query(User).all()  

# 优化后（只查 name 和 age）  
users = session.query(User.name, User.age).all()  # 返回的是元组列表：(name, age)  
# 或者保留对象形式（但只加载指定字段）  
users = session.query(User).options(load_only("name", "age")).all()

问题3：索引缺失（查询条件没索引）

场景：经常按 email 查用户（WHERE email='xxx'），但 email 字段没索引，导致全表扫描。

现象：数据库执行 EXPLAIN SELECT * FROM user WHERE email='a@b.com' 时，显示 type: ALL（全表扫描）。

解决：给常用查询字段加索引（Alembic 迁移时加）：

# 1. 改 User 类，给 email 加索引  
class User(Base):  
    __tablename__ = "user"  
    id = Column(Integer, primary_key=True, autoincrement=True)  
    name = Column(String(50), nullable=False)  
    age = Column(Integer)  
    email = Column(String(100), unique=True, index=True)  # 加 index=True（简单索引）  

# 2. 用 Alembic 生成迁移脚本（会生成 CREATE INDEX 语句）  
alembic revision --autogenerate -m "add index to user.email"  
alembic upgrade head

复杂索引（如联合索引）可以用 Index 类显式定义：
from sqlalchemy import Index  
Index('idx_age_name', User.age, User.name)  # 联合索引：按 age 和 name 排序  

问题4：频繁的小事务（Commit 太碎）

场景：循环中每次新增一条数据就 commit()，导致频繁写磁盘。

现象：SQL 日志里一堆 COMMIT，数据库压力大。

解决：批量操作后统一 commit()（“攒一波再写”）：

# 优化前（循环 commit）  
for i in range(1000):  
    user = User(name=f"user{i}", age=20)  
    session.add(user)  
    session.commit()  # 每次 add 都 commit，效率低  

# 优化后（批量 commit）  
users = [User(name=f"user{i}", age=20) for i in range(1000)]  
session.add_all(users)  
session.commit()  # 一次 commit，效率提升几十倍

7.3 终极武器：原生 SQL + 数据库特性

如果 ORM 实在搞不定（比如复杂的窗口函数、分区表查询），直接用原生 SQL（配合 text()），并利用数据库的特性优化：

# 比如 MySQL 的分页优化（用 LIMIT  offset 会慢，改用 WHERE id > last_id）  
last_id = 100  
result = session.execute(  
    text("SELECT * FROM user WHERE id > :last_id ORDER BY id LIMIT 20"),  
    {"last_id": last_id}  
)

本章重点：

优化前先“诊断”：用 SQL 日志和慢查询日志定位瓶颈。
常见问题：N+1 查询（用 joinedload）、查多余字段（用 with_entities）、缺索引（用 index=True）、频繁 commit（批量操作）。
终极方案：原生 SQL + 数据库特性（不硬扛 ORM 的限制）。

第八章：实战案例——用 SQLAlchemy 搭一个“迷你博客系统”

学了这么多，咱们用一个小案例串起来：实现一个“用户-文章”的博客系统（用户写文章，文章属于用户）。

8.1 需求分析

用户表（User）：id、username（用户名）、password（密码）；
文章表（Article）：id、title（标题）、content（内容）、user_id（作者 ID，外键关联 User）；
功能：用户注册、发布文章、查看某用户的所有文章。

8.2 代码实现（精简版）

（1）定义模型（User 和 Article 一对多）

from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import sessionmaker, relationship  

Base = declarative_base()  

class User(Base):  
    __tablename__ = "user"  
    id = Column(Integer, primary_key=True, autoincrement=True)  
    username = Column(String(50), unique=True, nullable=False)  
    password = Column(String(100), nullable=False)  

    # 一对多：一个用户有多篇文章  
    articles = relationship("Article", back_populates="author", cascade="all, delete-orphan")  

class Article(Base):  
    __tablename__ = "article"  
    id = Column(Integer, primary_key=True, autoincrement=True)  
    title = Column(String(100), nullable=False)  
    content = Column(Text)  
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)  

    # 多对一：一篇文章属于一个作者  
    author = relationship("User", back_populates="articles")  

# 连接数据库  
engine = create_engine("sqlite:///blog.db")  
Base.metadata.create_all(engine)  

# 创建 Session  
Session = sessionmaker(bind=engine)  
session = Session()

（2）用户注册与发布文章

# 注册用户  
def register_user(username, password):  
    user = User(username=username, password=password)  # 实际项目要加密密码（如 bcrypt）  
    session.add(user)  
    session.commit()  
    return user  

# 发布文章  
def publish_article(user_id, title, content):  
    article = Article(title=title, content=content, user_id=user_id)  
    session.add(article)  
    session.commit()  
    return article  

# 查看某用户的所有文章  
def get_user_articles(user_id):  
    user = session.get(User, user_id)  
    if user:  
        return user.articles  # 直接用关系访问，自动预加载（如果配置了）  
    return []  

# 测试  
user = register_user("zhangsan", "123456")  
publish_article(user.id, "我的第一篇博客", "Hello, SQLAlchemy!")  
print(get_user_articles(user.id))  # [<Article(id=1, title='我的第一篇博客', ...)>]