3.3 过滤、排序与分页

真实业务的查询都长这样："搜索北京的、年龄 18~35 的用户，按注册时间倒序，每页 20 条"。本节把 where / order_by / limit·offset 全家桶学齐。

前置：使用 3.1 节的 models.py。

一、where：过滤条件

比较运算符（全部被重载为 SQL 表达式）

from sqlalchemy import select

select(User).where(User.age == 25)      # 等于       WHERE age = 25
select(User).where(User.age != 25)      # 不等于     WHERE age != 25
select(User).where(User.age > 18)       # 大于
select(User).where(User.age >= 18)      # 大于等于
select(User).where(User.age < 60)       # 小于
select(User).where(User.age.between(18, 35))   # 区间  WHERE age BETWEEN 18 AND 35

⚠️ 必须用 == 而不是 is：User.age == 25 是在构建 SQL 表达式，is 无法重载。

NULL 判断

select(User).where(User.age.is_(None))       # WHERE age IS NULL
select(User).where(User.age.is_not(None))    # WHERE age IS NOT NULL

📌 判断 NULL 用 .is_(None) / .is_not(None)，不要写 == None（虽然能用但 lint 会警告）。

IN / NOT IN

select(User).where(User.city.in_(["北京", "上海"]))       # WHERE city IN ('北京','上海')
select(User).where(User.city.not_in(["北京", "上海"]))

模糊匹配 LIKE

select(User).where(User.name.like("张%"))      # 张开头（%匹配任意多字符）
select(User).where(User.name.like("%三%"))     # 包含"三"
select(User).where(User.email.ilike("%QQ%"))   # ilike = 忽略大小写的 like

# 更语义化的快捷方法：
select(User).where(User.name.startswith("张"))
select(User).where(User.name.endswith("三"))
select(User).where(User.name.contains("三"))

二、组合条件：与、或、非

AND：最简单——多个条件直接并列

# 写法1：多个参数（推荐，最清晰）
select(User).where(User.city == "北京", User.age >= 18)

# 写法2：链式 where
select(User).where(User.city == "北京").where(User.age >= 18)

# 写法3：显式 and_
from sqlalchemy import and_
select(User).where(and_(User.city == "北京", User.age >= 18))

三种完全等价，生成 WHERE city = '北京' AND age >= 18。

OR：必须用 or_

from sqlalchemy import or_

# 城市是北京 或 上海
select(User).where(or_(User.city == "北京", User.city == "上海"))

混合嵌套

from sqlalchemy import and_, or_

# (北京 且 age>30) 或 (上海 且 age>25)
stmt = select(User).where(
    or_(
        and_(User.city == "北京", User.age > 30),
        and_(User.city == "上海", User.age > 25),
    )
)

NOT

from sqlalchemy import not_
select(User).where(not_(User.city == "北京"))
# 简单场景直接用 != 更直观

⚠️ 大坑预警：不要用 Python 的 and / or / not 关键字！
select(User).where(User.city == "北京" and User.age > 18)   # ❌ 错！
Python 的 and 无法重载，这行实际只剩 User.age > 18 一个条件，不报错但结果悄悄不对。务必用 and_() / or_() 或多参数写法。

三、动态拼接条件（真实业务必备）

搜索接口的条件通常是"用户传了才过滤"。select 语句每次调用 .where() 都返回新语句，所以可以自由拼接：

def search_users(session, city: str | None = None,
                 min_age: int | None = None,
                 keyword: str | None = None):
    stmt = select(User)

    if city:
        stmt = stmt.where(User.city == city)        # 注意要接住返回值！
    if min_age is not None:
        stmt = stmt.where(User.age >= min_age)
    if keyword:
        stmt = stmt.where(User.name.contains(keyword))

    return session.scalars(stmt).all()

⚠️ 新手常错：写成 stmt.where(...) 不接返回值——语句对象是不可变的，.where() 不会原地修改，必须 stmt = stmt.where(...)。

四、order_by：排序

# 升序（默认）
select(User).order_by(User.age)

# 降序
select(User).order_by(User.age.desc())

# 多级排序：先按城市升序，同城市内按年龄降序
select(User).order_by(User.city, User.age.desc())

# 最常见：最新的排前面
select(User).order_by(User.created_at.desc())

五、limit / offset：分页

# 只取前 10 条
select(User).limit(10)

# 跳过前 20 条，取 10 条（第 3 页，每页 10 条）
select(User).offset(20).limit(10)

标准分页函数

def get_user_page(session, page: int = 1, page_size: int = 20):
    """page 从 1 开始"""
    stmt = (
        select(User)
        .order_by(User.created_at.desc())     # 分页必须配排序，否则每页内容不稳定！
        .offset((page - 1) * page_size)
        .limit(page_size)
    )
    return session.scalars(stmt).all()

📌 分页三定律：

必须带 order_by——没有排序的分页，数据库不保证每次顺序一致，会出现翻页看到重复/丢失数据

页码从 1 开始时，offset = (page - 1) * page_size

接口要同时返回总数（见下）让前端算总页数

分页配套：查询总数

from sqlalchemy import func, select

def get_user_page_with_total(session, page: int = 1, page_size: int = 20):
    base = select(User).where(User.age >= 18)          # 过滤条件写一份

    total = session.scalar(
        select(func.count()).select_from(base.subquery())
    )
    items = session.scalars(
        base.order_by(User.id).offset((page - 1) * page_size).limit(page_size)
    ).all()
    return {"total": total, "page": page, "page_size": page_size, "items": items}

六、综合演练

"搜索北京或上海、成年、名字带'三'的用户，按年龄从大到小，取前 5 个"：

from sqlalchemy import or_, select
from models import User, SessionLocal

with SessionLocal() as session:
    stmt = (
        select(User)
        .where(
            or_(User.city == "北京", User.city == "上海"),
            User.age >= 18,
            User.name.contains("三"),
        )
        .order_by(User.age.desc())
        .limit(5)
    )
    for user in session.scalars(stmt):
        print(user)

生成的 SQL：

SELECT users.id, users.name, users.email, users.age, users.city, users.created_at
FROM users
WHERE (users.city = ? OR users.city = ?) AND users.age >= ? AND (users.name LIKE '%' || ? || '%')
ORDER BY users.age DESC
LIMIT ? OFFSET ?

链式调用的顺序不影响结果（where 写在 order_by 后面也行），SQLAlchemy 会生成正确结构的 SQL。惯例顺序：select → where → order_by → offset → limit。

📝 本节小结

过滤：==/>/between/in_/like/contains/.is_(None)
组合：AND 用多参数并列，OR 必须 or_()；严禁 Python 的 and/or 关键字
动态条件：stmt = stmt.where(...) 逐步拼接（记得接返回值）
排序：.order_by(col.desc())；分页：.offset().limit() + 必须配排序
分页接口标配返回 total

下一节补完 CRUD 的最后两块 → 3.4 更新与删除

一、where：过滤条件​

比较运算符（全部被重载为 SQL 表达式）​

NULL 判断​

IN / NOT IN​

模糊匹配 LIKE​

二、组合条件：与、或、非​

AND：最简单——多个条件直接并列​

OR：必须用 or_​

混合嵌套​

NOT​

三、动态拼接条件（真实业务必备）​

四、order_by：排序​

五、limit / offset：分页​

标准分页函数​

分页配套：查询总数​

六、综合演练​

📝 本节小结​