fastapi:第二十七章：查询数据分页

一，代码：

# app/api/products.py
import math

from fastapi import APIRouter, Request, Depends, HTTPException, Form
from pydantic import BaseModel, Field
from sqlalchemy import func

from app.core.database import get_db
from app.core.logger import logger
from app.models.products import Product
from app.utils.log_util import get_query_param
from app.utils.response import success,fail
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select # 2.0 推荐使用 select 语法
import app.models
from typing import Annotated

router = APIRouter(prefix="/products", tags=["商品管理"])

@router.get("/all")
async def get_all_products(request: Request,page: int = 1, per_page: int = 2, db: AsyncSession = Depends(get_db)):
    # 异步写法不能直接用 db.query()，必须用 select
    skip = (page-1) * per_page
    limit = per_page
    offset = (page - 1) * per_page
    # 1. 抽取公共的基础查询条件（关键点：统一过滤条件）
    base_stmt = select(User).where(User.status >= 1, User.deleted == 0)

    # 2. 异步查询总条数
    # 通过将基础查询变为子查询，或者直接用 select_from，来计算符合条件的总数
    count_stmt = select(func.count()).select_from(base_stmt.subquery())
    total_items = await db.scalar(count_stmt)

    # 3. 异步查询当前页数据
    # 在基础查询的基础上，叠加排序（可选，但分页通常建议排序）、偏移和限制
    data_stmt = base_stmt.order_by(User.id.desc()).offset(offset).limit(per_page)
    result = await db.scalars(data_stmt)
    items = result.all()

    # 异步查询总条数 (注意 await)
    count_stmt = select(func.count()).select_from(Product)
    total_items = await db.scalar(count_stmt)

    # 计算分页元数据
    total_pages = math.ceil(total_items / per_page) if total_items > 0 else 1
    has_prev = page > 1
    has_next = page < total_pages

    res = {
        "list": items,
        "meta": {
            "total_items": total_items,
            "total_pages": total_pages,
            "current_page": page,
            "per_page": per_page,
            "has_prev": has_prev,
            "has_next": has_next,
            "prev_page": page - 1 if has_prev else None,
            "next_page": page + 1 if has_next else None
        }
    }

    return success(data=res)

二，测试效果：