SQLAlchemy CRUD 完全指南：从入门到精通的 ORM 操作大全

  【个人主页：玄同765】

大语言模型（LLM）开发工程师｜中国传媒大学·数字媒体技术（智能交互与游戏设计）

深耕领域：大语言模型开发 / RAG知识库 / AI Agent落地 / 模型微调

技术栈：Python / LangChain/RAG（Dify+Redis+Milvus）| SQL/NumPy | FastAPI+Docker ️

工程能力：专注模型工程化部署、知识库构建与优化，擅长全流程解决方案 

      

专栏传送门：LLM大模型开发 项目实战指南、Python 从真零基础到纯文本 LLM 全栈实战、​​​​​从零学 SQL + 大模型应用落地、大模型开发小白专属：从 0 入门 Linux&Shell

     

「让AI交互更智能，让技术落地更高效」

欢迎技术探讨/项目合作！ 关注我，解锁大模型与智能交互的无限可能！

在 Python 后端开发中，SQLAlchemy 是最受欢迎的 ORM（对象关系映射）框架之一 —— 它将数据库表映射为 Python 对象，让你用 Python 语法替代原生 SQL，实现跨数据库的 CRUD（增删改查）操作，同时保留 SQL 的灵活性。本文将从环境搭建、模型定义、基础 CRUD、进阶技巧、性能优化五个维度，带你掌握 SQLAlchemy 的所有常用 CRUD 用法，覆盖从新手到进阶的所有场景。

一、前置准备：环境搭建与基础配置

1. 安装 SQLAlchemy

pip install sqlalchemy

如果需要连接特定数据库（如 MySQL、PostgreSQL），还需安装对应的驱动：

• MySQL：pip install pymysql
• PostgreSQL：pip install psycopg2-binary
• SQLite：无需额外驱动，SQLAlchemy 内置支持（适合测试）

2. 核心组件初始化

我们以 SQLite 为例，演示完整的初始化流程：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 1. 创建数据库引擎：SQLite使用文件路径，MySQL/PostgreSQL使用连接字符串
# MySQL示例：mysql+pymysql://user:password@localhost/dbname
# PostgreSQL示例：postgresql+psycopg2://user:password@localhost/dbname
engine = create_engine('sqlite:///example.db', echo=True)  # echo=True会打印SQL语句，适合调试

# 2. 定义模型基类：所有模型都继承自Base
Base = declarative_base()

# 3. 创建会话工厂：会话是与数据库交互的核心对象
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 4. 创建数据库表（仅第一次运行需要）
Base.metadata.create_all(bind=engine)

3. 定义示例模型

我们定义一对多关联的User（用户）和Address（地址）模型，后续所有 CRUD 操作都基于这两个模型：

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'users'  # 数据库表名
    id = Column(Integer, primary_key=True, index=True)  # 主键，自动递增
    name = Column(String(50), index=True)  # 普通字段，添加索引加速查询
    email = Column(String(100), unique=True, index=True)  # 唯一约束，避免重复邮箱

    # 一对多关联：一个用户可以有多个地址
    addresses = relationship("Address", back_populates="user", cascade="all, delete-orphan")

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True, index=True)
    street = Column(String(200))
    city = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))  # 外键，关联users表的id

    # 反向关联：一个地址属于一个用户
    user = relationship("User", back_populates="addresses")

二、增（Create）：添加数据的 5 种常用方式

1. 基础用法：添加单条数据

# 获取会话
db = SessionLocal()

# 创建用户对象
user = User(name="Alice", email="alice@example.com")

# 添加到会话并提交
db.add(user)
db.commit()

# 刷新对象，获取数据库自动生成的id
db.refresh(user)
print(f"创建用户：ID={user.id}, 姓名={user.name}")

# 关闭会话
db.close()

2. 批量添加：一次性插入多条数据

db = SessionLocal()

# 创建多个用户对象
users = [
    User(name="Bob", email="bob@example.com"),
    User(name="Charlie", email="charlie@example.com")
]

# 批量添加
db.add_all(users)
db.commit()

# 刷新所有对象
for user in users:
    db.refresh(user)
    print(f"创建用户：ID={user.id}, 姓名={user.name}")

db.close()

3. 高效批量插入：bulk_save_objects（适合大量数据）

当需要插入上万条数据时，bulk_save_objects比add_all更高效（减少 SQL 语句的次数）：

db = SessionLocal()

# 生成100个测试用户
users = [User(name=f"User_{i}", email=f"user_{i}@example.com") for i in range(100)]

# 高效批量插入
db.bulk_save_objects(users)
db.commit()

print(f"批量创建100个用户成功")

db.close()

4. 关联数据添加：同时插入主表与关联表数据

利用relationship的级联特性，添加用户时可同时添加关联的地址：

db = SessionLocal()

# 创建用户并关联地址
user = User(name="David", email="david@example.com")
user.addresses.append(Address(street="123 Main St", city="New York"))
user.addresses.append(Address(street="456 Oak Ave", city="Los Angeles"))

# 添加用户，地址会自动级联插入
db.add(user)
db.commit()

db.refresh(user)
print(f"创建用户：ID={user.id}, 地址数量={len(user.addresses)}")

db.close()

5. 事务处理：保证数据一致性

当多个操作需要同时成功或失败时，使用事务：

db = SessionLocal()
try:
    # 多个操作在同一个事务中
    user1 = User(name="Eve", email="eve@example.com")
    user2 = User(name="Frank", email="frank@example.com")
    db.add_all([user1, user2])
    
    # 模拟异常：比如重复邮箱
    user3 = User(name="Eve", email="eve@example.com")
    db.add(user3)
    
    db.commit()
except Exception as e:
    # 出现异常时回滚事务
    db.rollback()
    print(f"事务失败，回滚：{e}")
finally:
    db.close()

三、查（Read）：查询数据的 10 种常用技巧

查询是 SQLAlchemy 中最灵活的部分，以下是从基础到进阶的常用查询方式：

1. 基础查询：获取所有数据或单条数据

db = SessionLocal()

# 查询所有用户
all_users = db.query(User).all()
print(f"所有用户：{[user.name for user in all_users]}")

# 查询第一条数据
first_user = db.query(User).first()
print(f"第一个用户：{first_user.name}")

# 根据主键查询
user_by_id = db.query(User).get(1)  # get()仅支持主键查询
print(f"ID=1的用户：{user_by_id.name}")

# 查询唯一数据（不存在或存在多个会抛出异常）
try:
    unique_user = db.query(User).filter(User.email == "alice@example.com").one()
    print(f"唯一用户：{unique_user.name}")
except Exception as e:
    print(f"查询唯一数据失败：{e}")

# 查询唯一数据（不存在返回None，存在多个抛出异常）
user_or_none = db.query(User).filter(User.email == "nonexistent@example.com").one_or_none()
print(f"查询结果：{user_or_none}")

db.close()

2. 条件查询：filter 与 filter_by 的区别

• filter()：支持复杂条件（类属性比较、逻辑运算），更灵活。
• filter_by()：仅支持关键字参数，语法更简洁，适合简单条件。

db = SessionLocal()

# filter()示例：复杂条件
users_filter = db.query(User).filter(
    User.name.like("A%"),  # 姓名以A开头
    User.id > 1
).all()
print(f"filter查询结果：{[user.name for user in users_filter]}")

# filter_by()示例：简单条件
users_filter_by = db.query(User).filter_by(name="Alice").all()
print(f"filter_by查询结果：{[user.name for user in users_filter_by]}")

# 逻辑运算：and_、or_、not_
from sqlalchemy import and_, or_, not_
users_logic = db.query(User).filter(
    or_(User.name == "Alice", User.city == "New York")  # 姓名是Alice或城市是New York
).all()

db.close()

3. 排序与分页：处理大量数据

db = SessionLocal()

# 排序：asc()升序，desc()降序
sorted_users = db.query(User).order_by(User.name.desc()).all()
print(f"按姓名降序：{[user.name for user in sorted_users]}")

# 分页：offset(跳过的数量)，limit(每页数量)
page_users = db.query(User).offset(2).limit(3).all()
print(f"第2页（每页3条）：{[user.name for user in page_users]}")

db.close()

4. 聚合查询：统计、求和、平均值

使用func模块执行聚合函数：

from sqlalchemy import func

db = SessionLocal()

# 统计用户总数
user_count = db.query(func.count(User.id)).scalar()  # scalar()获取单个值
print(f"用户总数：{user_count}")

# 统计每个城市的用户数量（分组查询）
city_count = db.query(Address.city, func.count(User.id)).join(User).group_by(Address.city).all()
print(f"各城市用户数量：{city_count}")

# 计算用户ID的平均值
avg_id = db.query(func.avg(User.id)).scalar()
print(f"用户ID平均值：{avg_id:.2f}")

db.close()

5. 关联查询：JOIN 与避免 N+1 问题

• 默认懒加载：访问关联属性时会触发额外查询（N+1 问题）。
• 立即加载：用joinedload或subqueryload一次性加载关联数据，避免 N+1。

from sqlalchemy.orm import joinedload

db = SessionLocal()

# 错误示例：N+1查询（1次查询用户，N次查询地址）
users = db.query(User).all()
for user in users:
    print(f"用户：{user.name}，地址数量：{len(user.addresses)}")  # 每次访问addresses都会触发SQL查询

# 正确示例：用joinedload一次性加载用户与地址
users_joined = db.query(User).options(joinedload(User.addresses)).all()
for user in users_joined:
    print(f"用户：{user.name}，地址数量：{len(user.addresses)}")  # 无额外查询

# 条件关联查询：查询地址在New York的用户
users_in_ny = db.query(User).join(Address).filter(Address.city == "New York").all()
print(f"地址在New York的用户：{[user.name for user in users_in_ny]}")

db.close()

6. 子查询：嵌套查询

db = SessionLocal()

# 子查询：查询地址数量大于1的用户
subquery = db.query(Address.user_id, func.count(Address.id).label("address_count"))\
             .group_by(Address.user_id)\
             .having(func.count(Address.id) > 1)\
             .subquery()

users_with_many_addresses = db.query(User).join(subquery, User.id == subquery.c.user_id).all()
print(f"地址数量大于1的用户：{[user.name for user in users_with_many_addresses]}")

db.close()

四、改（Update）：更新数据的 4 种方式

1. 基础更新：先查询再修改

db = SessionLocal()

# 查询用户
user = db.query(User).filter(User.name == "Alice").first()
if user:
    # 修改属性
    user.email = "alice_updated@example.com"
    # 提交事务
    db.commit()
    # 刷新对象
    db.refresh(user)
    print(f"更新后用户邮箱：{user.email}")

db.close()

2. 批量更新：直接更新符合条件的所有数据

db = SessionLocal()

# 批量更新所有姓名以A开头的用户邮箱后缀
update_count = db.query(User).filter(User.name.like("A%"))\
                 .update({User.email: User.email + ".updated"}, synchronize_session=False)
db.commit()
print(f"更新了{update_count}条数据")

db.close()

• synchronize_session=False：表示不刷新会话中的对象（适合批量更新，提升性能）。

3. 关联数据更新：更新主对象同时更新关联对象

db = SessionLocal()

# 查询用户并更新关联的地址
user = db.query(User).filter(User.name == "David").first()
if user:
    # 更新第一个地址
    user.addresses[0].city = "Chicago"
    db.commit()
    print(f"更新后地址城市：{user.addresses[0].city}")

db.close()

4. 表达式更新：基于字段值的计算更新

db = SessionLocal()

# 模拟用户积分增加100
update_count = db.query(User).update({User.id: User.id + 100}, synchronize_session=False)
db.commit()
print(f"所有用户ID增加100，更新了{update_count}条数据")

db.close()

五、删（Delete）：删除数据的 3 种方式

1. 基础删除：先查询再删除

db = SessionLocal()

# 查询用户并删除
user = db.query(User).filter(User.name == "Frank").first()
if user:
    db.delete(user)
    db.commit()
    print(f"删除用户：{user.name}")

db.close()

2. 批量删除：直接删除符合条件的所有数据

db = SessionLocal()

# 批量删除所有姓名以User_开头的测试用户
delete_count = db.query(User).filter(User.name.like("User_%")).delete(synchronize_session=False)
db.commit()
print(f"删除了{delete_count}条测试数据")

db.close()

3. 级联删除：删除主对象同时删除关联对象

利用模型定义时的cascade="all, delete-orphan"参数，删除用户时会自动删除关联的地址：

db = SessionLocal()

# 删除用户，关联的地址会被级联删除
user = db.query(User).filter(User.name == "David").first()
if user:
    db.delete(user)
    db.commit()
    print(f"删除用户后，地址数量：{db.query(Address).filter(Address.user_id == user.id).count()}")  # 输出0

db.close()

4. 软删除：标记删除而非物理删除

在实际项目中，通常不直接物理删除数据，而是用is_deleted字段标记：

# 先修改模型，添加is_deleted字段
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), index=True)
    email = Column(String(100), unique=True, index=True)
    is_deleted = Column(Boolean, default=False)  # 软删除标记

# 软删除：更新is_deleted为True
db = SessionLocal()
update_count = db.query(User).filter(User.name == "Eve").update({User.is_deleted: True})
db.commit()
print(f"软删除用户：{update_count}条")

# 查询时过滤已删除的数据
active_users = db.query(User).filter(User.is_deleted == False).all()
print(f"活跃用户数量：{len(active_users)}")

db.close()

六、进阶技巧：提升开发效率的实用功能

1. 原生 SQL 执行：处理复杂查询

当 ORM 无法满足需求时，可直接执行原生 SQL：

db = SessionLocal()

# 执行原生查询
result = db.execute("SELECT name, email FROM users WHERE name LIKE :name", {"name": "A%"})
for row in result:
    print(f"原生SQL查询结果：姓名={row.name}, 邮箱={row.email}")

# 执行原生更新/删除
db.execute("UPDATE users SET email = :email WHERE name = :name", {"email": "alice_sql@example.com", "name": "Alice"})
db.commit()

db.close()

2. 上下文管理器：简化会话操作

使用with语句自动管理会话的创建与关闭：

def get_user_by_name(name):
    with SessionLocal() as db:
        return db.query(User).filter(User.name == name).first()

user = get_user_by_name("Alice")
print(f"通过上下文管理器查询到的用户：{user.name}")

3. 查询优化：避免常见性能问题

• 避免 N+1 查询：用joinedload或subqueryload预加载关联数据。
• 使用索引：在常用查询字段上添加index=True（如name、email）。
• 限制返回字段：仅查询需要的字段，减少数据传输：

  # 仅查询姓名和邮箱，不返回整个User对象
  user_data = db.query(User.name, User.email).filter(User.name == "Alice").first()
  

七、总结：SQLAlchemy CRUD 最佳实践

1. 优先使用 ORM：ORM 语法简洁，跨数据库兼容，适合大多数场景；复杂 SQL 用 Core 或原生 SQL 补充。
2. 事务优先：所有修改操作（增删改）都要在事务中执行，保证数据一致性。
3. 查询优化：用joinedload避免 N+1，用索引加速查询，限制返回字段。
4. 软删除替代物理删除：保留数据历史，便于恢复和审计。
5. 代码规范：用上下文管理器管理会话，避免资源泄漏；复杂查询封装为函数，提高可读性。

SQLAlchemy 的 CRUD 操作覆盖了从简单到复杂的所有场景，掌握这些用法后，你可以高效处理各种数据库需求，从小型项目到大型企业应用都能轻松应对。