fastapi: sqlalchemy:分页时得到结果集总数的两种方式

一，代码：

在 FastAPI + 异步 SQLAlchemy 的架构中，获取分页总数据量（Total Count）通常有两种主流高效的方法。

方法一：针对mysql5.7及更旧版本：使用 func.count() 进行二次查询（最传统、清晰）
这是最稳妥的办法。先构造基础的 select 语句，然后通过 func.count() 查出总数，再对原语句加上 limit 和 offset 查出单页数据。

# 1. 定义 列表 WTForms 表单
# 1. 定义请求体数据模型
class UserListForm(BaseModel):
    page: int = Field(default=1, gt=0, description="页数需要大于0")
    page_size: int = Field(default=3, gt=0, description="每页数量需要大于0")

@router.get("/all")
async def get_all_users(request: Request,
                        prod_data: Annotated[UserListForm, Query()],
                           db: AsyncSession = Depends(get_db)):
    # 参数
    page = prod_data.page
    page_size = prod_data.page_size

    # 1. 定义基础查询语句（可以在这里加 where 条件）
    base_query = select(User).where(User.status == 1)

    # 2. 异步查询总数
    # scalar() 会直接返回 count 的整数值
    count_query = select(func.count()).select_from(base_query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 3. 异步查询当前页的数据
    data_query = base_query.offset((page - 1) * page_size).limit(page_size)
    data_result = await db.execute(data_query)
    # scalars().all() 将结果集转化为 Python 模型列表
    items = data_result.scalars().all()

    return {
        "items": items,
        "total": total,
    }

方法二：针对mysql8.0及更新版本：利用 Window Function（窗口函数）一条 SQL 搞定（高级/高效）
如果你的数据库是 PostgreSQL 或者 MySQL 8.0+，可以使用窗口函数 func.count().over()。它可以在查出分页数据的同时，在每一行数据旁边附带上整张表的总数据量。

这样只需要与数据库交互一次（执行一条 SQL），在并发量高的生产环境中性能更好。

@router.get("/all2")
async def get_all_users2(request: Request,
                        prod_data: Annotated[UserListForm, Query()],
                           db: AsyncSession = Depends(get_db)):
    # 参数
    page = prod_data.page
    page_size = prod_data.page_size

    # 使用 func.count().over() 作为一个隐藏列
    stmt = (
        select(User, func.count().over().label("total_count"))
        .where(User.status == 1)
        .offset((page - 1) * page_size)
        .limit(page_size)
    )

    result = await db.execute(stmt)

    # 因为多查了一个 count 列，result 出来的每一行是一个元组 (User对象, total_count值)
    rows = result.all()

    # 解析结果
    if not rows:
        return {"total": 0, "page": page, "size": page_size, "items": []}

    # 无论哪一行，对应的 total_count 都是一样的，取第一行的即可
    total = rows[0].total_count
    items = [row.User for row in rows]

    return {
        "items": items,
        "total": total,
    }

二，测试效果：