SQLAlchemy 如何实现 分页查询

你需要知道的事：分页查询，第一反应你想到的可能会是 paginate，但是如果你使用的是sqlalchemy，而不是flask_sqlalchemy（将sqlalchemy集成到flask框架中的一个模块）

此时一定会报错： AttributeError: 'Query' object has no attribute 'paginate        没有paginate ？？？

原因解释：因为paginate是flask_sqlalchemy中才有的方法，而sqlalchemy本身没有这个方法的哦 ！！！

那么问题来了，如果就是要使用sqlalchemy模块，如何实现分页查询呢？比如你使用的fastapi框架，这时你不可能再去使用flask_sqlalchemy模块了吧

 

其实，sqlalchemy本身也有专门实现分页查询的方法：即  offset（偏移量） 与 limit（数据条数限制）我将会用以下案例进行详细说明

 

案例说明：

1.使用的是python fastapi框架

2.BudgetStatic是创建好的数据模型

3.to_dict()方法是在创建的数据模型中定义好的

为了方便起见，直接附上定义数据模型部分的代码，引入 sqlalchemy 以及 如何连接数据库 就省略啦

class BudgetStatic(Base):
    # 定义表名
    __tablename__ = 'ops_budget_static'
    # 定义字段
    # primary_key=True 设置为主键
    # nullable=False 不可为空
    # 默认Integereger为Integereger(11)
    # budget_id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    static_id = Column(Integer, primary_key=True, nullable=False)
    budget_number = Column(String(32), nullable=False)
    budget_year = Column(String(32), nullable=False)
    project_name = Column(String(32), nullable=False)
    project_code = Column(String(32), nullable=False)
    product_name = Column(String(32), nullable=False)
    product_code = Column(String(32), nullable=False)
    asset_type = Column(String(32), nullable=False)
    asset_medium_type = Column(String(32), nullable=False)
    asset_small_type = Column(String(32), nullable=False)
    responsible = Column(String(32), nullable=False)
    budget_name = Column(String(32), nullable=False)
    budget_usage_description = Column(String(500), nullable=False)
    report_time = Column(DateTime, nullable=False)
    is_server = Column(String(32), nullable=False)
    package_type = Column(String(32), nullable=False)
    service_fee = Column(Integer, nullable=False)
    requested_resources_environment = Column(String(32), nullable=False)
    invest_direction = Column(String(32), nullable=False)
    rigid = Column(String(32), nullable=False)
    invest_type = Column(String(32), nullable=False)

    # # 用于生成外键
    # dynamic = relationship('BudgetDynamic', backref='ops_budget_static')

    # 初始化对象
    # __init__函数：在创建类实例的时候，实例会自动调用这个方法，一般用来对实例的属性进行初始化
    # 注意：需要初始化的属性（参数）都是在调用实例时的必传参数
    def __init__(self, budget_number, budget_year, project_name, project_code, product_name, product_code, asset_type,
                 asset_medium_type,
                 asset_small_type, responsible, budget_name, budget_usage_description, report_time, is_server,
                 package_type, service_fee,
                 requested_resources_environment, invest_direction, rigid, invest_type):
        # self.budget_id = budget_id           #这个参数设置的是自增，无需在创建实例时给它传递值
        self.budget_number = budget_number
        self.budget_year = budget_year
        self.project_name = project_name
        self.project_code = project_code
        self.product_name = product_name
        self.product_code = product_code
        self.asset_type = asset_type
        self.asset_medium_type = asset_medium_type
        self.asset_small_type = asset_small_type
        self.responsible = responsible
        self.budget_name = budget_name
        self.budget_usage_description = budget_usage_description
        self.report_time = report_time
        self.is_server = is_server
        self.package_type = package_type
        self.service_fee = service_fee
        self.requested_resources_environment = requested_resources_environment
        self.invest_direction = invest_direction
        self.rigid = rigid
        self.invest_type = invest_type

    # 打印形式
    # 当使用print输出实例对象的时候，就会打印从在这个方法中return的数据；
    # 注意：对于__str__函数的输出参数没有要求，写哪些就会输出哪些
    # def __str__(self):
    #     return "static_id: %s, budget_number:%s, budget_year:%s, project_name:%s, project_code:%s, product_name:%s product_code:%s asset_type:%s asset_medium_type:%s,  \
    #             asset_small_type:%s, responsible:%s, budget_name:%s, budget_usage_description:%s, report_time:%s, is_server:%s, package_type:%s, service_fee:%s,  \
    #             requested_resources_environment:%s, invest_direction:%s, rigid:%s, invest_type:%s" \
    #            % (str(self.static_id), str(self.budget_number), str(self.budget_year), str(self.project_name), str(self.project_code),str(self.product_name), str(self.product_code), str(self.asset_type),
    #               str(self.asset_medium_type),str(self.asset_small_type), str(self.responsible), str(self.budget_name), str(self.budget_usage_description), str(self.report_time),
    #               str(self.is_server), str(self.package_type), str(self.service_fee), str(self.requested_resources_environment), str(self.invest_direction), str(self.rigid),
    #               str(self.invest_type))

    # 实质上to_dict与__str__函数都是一样的，都是打印形式！
    # to_dict函数的作用：就是将BudgetStatic对象转换为字典形式
    def to_dict(self):
        return {
            'static_id': self.static_id,
            'budget_number': self.budget_number,
            'budget_year': self.budget_year,
            'project_name': self.project_name,
            'project_code': self.project_code,
            'product_name': self.product_name,
            'product_code': self.product_code,
            'asset_type': self.asset_type,
            'asset_medium_type': self.asset_medium_type,
            'asset_small_type': self.asset_small_type,
            'responsible': self.responsible,
            'budget_name': self.budget_name,
            'budget_usage_description': self.budget_usage_description,
            'report_time': self.report_time,
            'is_server': self.is_server,
            'package_type': self.package_type,
            'service_fee': self.service_fee,
            'requested_resources_environment': self.requested_resources_environment,
            'invest_direction': self.invest_direction,
            'rigid': self.rigid,
            'invest_type': self.invest_type
        }

案例分析：

前端传递的参数：page_one（当前页码） page_size（每页显示的记录数量）

后端使用的方法：sqlalchemy自带的 offset（偏移量）与  limit（数据条数限制）

偏移量的计算：

本案例中只有5条数据

page_one：当前页码， page_size：每页显示的记录数量    offset：偏移量         limit：限制条数
   1                    2                                 0                      2
   2                    2                                 2                      2
   3                    2                                 4                      2

根据 page_one 和 page_size 与 offset 的数字关系，可以发现： offset = page_size * (page_one - 1)

案例代码：

# 测试分页查询
@app.get("/budget/views_page_test")
async def select_budget_page(
        budget_year: Optional[str] = None,
        project_name: Optional[str] = None,
        project_code: Optional[str] = None,
        page_one: Optional[int] = 1,
        page_size: Optional[int] = 2
):

    # # offset：偏移量，limit：数据条数限制
    # # # 偏移量为2，每页展示2条数据；即从第3条数据开始展示，展示3,4（本案例中只有5条数据）
    # # budget_test_data = session.query(BudgetStatic).offset(2).limit(2)
    # offset_data= page_size *( page_one - 1 )
    # budget_test_data = session.query(BudgetStatic).offset(offset_data).limit(page_size)

    offset_data = page_size * (page_one - 1)
    budget_static_filter = session.query(BudgetStatic).filter(
        or_(BudgetStatic.budget_year == budget_year, budget_year == None),
        or_(BudgetStatic.project_name == project_name, project_name == None),
        or_(BudgetStatic.project_code == project_code, project_code == None)
    ).offset(offset_data).limit(page_size)

    # <class 'sqlalchemy.orm.query.Query'>
    # print(type(budget_static_filter))
    item = []
    # 1.遍历查询结果，拿到列表中的每一个元素
    for item_static in budget_static_filter:
        # <class 'mysql_engine.BudgetStatic'>
        # print(type(item_static))
        # print(item_static)

        # 2.将BudgetStatic对象转换为字典
        dict_static = {}
        dict_static.update(item_static.__dict__)

        # 3.将转换后的元素添加到列表中
        item.append(dict_static)
    return item

代码优化：

# 测试分页查询
@app.get("/budget/views_page_test")
async def select_budget_page(
        budget_year: Optional[str] = None,
        project_name: Optional[str] = None,
        project_code: Optional[str] = None,
        page_one: Optional[int] = 1,
        page_size: Optional[int] = 2
):
    offset_data = page_size * (page_one - 1)
    budget_static_filter = session.query(BudgetStatic).filter(
        or_(BudgetStatic.budget_year == budget_year, budget_year == None),
        or_(BudgetStatic.project_name == project_name, project_name == None),
        or_(BudgetStatic.project_code == project_code, project_code == None)
    ).offset(offset_data).limit(page_size)

    # to_dict方法是在 创建数据模型 时就定义好了的
    test_item = [item.to_dict() for item in budget_static_filter]

    return test_item