基于flask_sqlachemy的分页

flask 项目中使用分页paginate
报错 AttributeError:Query object has no attribute 'paignate'
创建model的时候使用的是sqlachemy,而不是flask_sqlachemy,而分页paginate方法只在flask_sqlchemy中提供，同样get_or_404()和first_or_404()也只在flask_sqlachemy中才有，所以要想使用，必须修改model的创建方式，基于flask_sqlalchemy才可以。

https://stackoverflow.com/questions/18409645/sqlalchemy-does-not-work-with-pagination
属性：
items : 表示获得的查询结果
pages : 表示一共有多少页
page ：获得当前页码数
total ：数据总条数
has_prev: 是否有上一页
has_next: 是否有下一页
prev_num：上一页页码
next_num：下一页页码
iter_page()：当前页的页码列表

关联查询

data = db.session.query(Alipay_B_Order.spec_name, Alipay_B_Bus.app_id).\
    join(Alipay_B_Bus,Alipay_B_Order.bus_id == Alipay_B_Bus.bus_id).\
    filter(Alipay_B_Order.order_id == order_id).first()

query 中使用了 Alipay_B_Order和 Alipay_B_Bus，后面再 join 一次 Alipay_B_Bus.那么 SQLAlchemy 如何选择 JOIN 的时候谁先谁后呢？看看这个错误就知道了：

data = db.session.query(Alipay_B_Order.spec_name, Alipay_B_Bus.app_id).\
    join(Alipay_B_Order,Alipay_B_Order.bus_id == Alipay_B_Bus.bus_id).\
    filter(Alipay_B_Order.order_id == order_id).first()

08-17 14:09 flask.app ERROR Exception on /internal_alipay/data_background_order_information [GET]
Traceback (most recent call last):
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask_cors/extension.py", line 161, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/mnt/home/tangsq/3/AppletServers/app/internal_alipay_applet/data_background_api.py", line 72, in data_background_order_information
    join(Alipay_B_Order,Alipay_B_Order.bus_id == Alipay_B_Bus.bus_id).\
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 1964, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/sqlalchemy/orm/base.py", line 201, in generate
    fn(self, *args[1:], **kw)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2108, in _join
    outerjoin, full, create_aliases, prop)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2164, in _join_left_to_right
    l_info.selectable)
sqlalchemy.exc.InvalidRequestError: Can't join table/selectable 'alipay_b_order' to itself

select * from alipay_b_order a INNER JOIN alipay_b_bus b on a.bus_id = b.bus_id where order_id ="2021043009571900008c"

因此，query 中参数的顺序很重要，query 的第一个参数就代表着第一个table是谁。故，此处 JOIN 的目标应该是 Alipay_B_Bus， 而不应该是 Alipay_B_Order。

联表分页

data = db.session.query(Alipay_B_Order).\
    join(Alipay_B_Bus,Alipay_B_Order.bus_id == Alipay_B_Bus.bus_id).\
    filter(Alipay_B_Order.order_id==order_id, Alipay_B_Bus.app_id == app_id).paginate(1, per_page=20, error_out=False)

08-17 15:17 flask.app ERROR Exception on /internal_alipay/data_background_order_information [GET]
Traceback (most recent call last):
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask_cors/extension.py", line 161, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/root/.virtualenvs/apicall_env/lib/python3.5/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/mnt/home/tangsq/3/AppletServers/app/internal_alipay_applet/data_background_api.py", line 77, in data_background_order_information
    filter(Alipay_B_Order.order_id==order_id, Alipay_B_Bus.app_id == app_id).paginate(1, per_page=20, error_out=False)
AttributeError: 'Query' object has no attribute 'paginate'

报错的原因是db.session.query 默认返回的是 orm.Query 对象，这个对象并不包含paginate方法。
要解决这个问题:

方法一

修改 Flask-SQLAlchemy 的源码

找到 SQLAlchemy 对象的 init 定义，在其中加入 session_options['query_cls'] = BaseQuery 即可：

def __init__(self, app=None, use_native_unicode=True, session_options=None, metadata=None):

    if session_options is None:
        session_options = {}

    session_options.setdefault('scopefunc', connection_stack.__ident_func__)
    self.use_native_unicode = use_native_unicode
    self.app = app

    # 使用 BaseQuery，这样可以让使用 db.session.query 等方法创建的 Query 对象支持 BaseQuery 的方法
    session_options['query_cls'] = BaseQuery

方法二

另一种关联查询语法

在 Flask-SQLAlchemy 提供的 Model 对象中，可以使用 Model.query 这样的语法来直接得到一个查询对象，这是由于 Flask-SQLAlchemy 中存在一个 _QueryProperty 类，每次调用 Model.get时，会自动生成一个基于当前 session 的 query 对象：

data = Alipay_B_Order.query.join(Alipay_B_Bus,Alipay_B_Order.bus_id == Alipay_B_Bus.bus_id).\
    filter(Alipay_B_Order.order_id==order_id, Alipay_B_Bus.app_id == app_id).\
    paginate(1, per_page=20, error_out=False)
print({"total": data.total,
                     "pages": data.pages,
                     "next_num": data.next_num,
                     "prev_num": data.prev_num,
                     "has_next": data.has_next,
                     "has_prev": data.has_prev})
print(data.items)
for item in data.items:
    oneData = item.__dict__
    del oneData["_sa_instance_state"]
    print(item.spec_name)

参考--在 Flask-SQLAlchemy 中联表查询

class BaseQuery(orm.Query):
    def paginate(self, page=None, per_page=None, error_out=True, max_per_page=None, count=True):
        """
        当“error_out”为"True"时，符合下面判断时将引起404响应，我一般都关了这个
        error_out，把接口返回什么的控制权拿回来。
        """
        //如果"page"或"per-page"是"None",则将从请求查询
        //flask-sqlalchemy和flask app绑定的还真是挺深，连request都用上了
        if request:
            if page is None:
                try:
                    //这么写更好page = request.args.get('page', 1, type=int)
                    page = int(request.args.get('page', 1))
                except (TypeError, ValueError):
                    if error_out:
                        abort(404)

                    page = 1

            if per_page is None:
                try:
                    per_page = int(request.args.get('per_page', 20))
                except (TypeError, ValueError):
                    if error_out:
                        abort(404)

                    per_page = 20
        else: // 如果没有请求分别默认为1和20
            if page is None:
                page = 1
            if per_page is None:
                per_page = 20
        //如果指定了“max_per_page”，则“per_page”将受这个值钳制。
        if max_per_page is not None:
            per_page = min(per_page, max_per_page)

        if page < 1:
            ......
                page = 1

        if per_page < 0:
            ......
                per_page = 20
        //原来paginate就是用的limit和offset，我猜如果有重复数据，先limit后all，all的时候再去个重，数据就少了，分页也没了
        items = self.limit(per_page).offset((page - 1) * per_page).all()
        ......
        //如果"count"是"False",total就不能用了,不用的时候可以关掉,省个查询
        if not count:
            total = None
        else:
            //为什么order_by(None)?
            total = self.order_by(None).count()

        return Pagination(self, page, per_page, total, items)

class Pagination(object):
    def __init__(self, query, page, per_page, total, items):
        #: the unlimited query object that was used to create this
        #: pagination object.
        self.query = query
        #: the current page number (1 indexed)
        self.page = page
        #: the number of items to be displayed on a page.
        self.per_page = per_page
        #: the total number of items matching the query
        self.total = total
        #: the items for the current page
        self.items = items

flask-sqlalchemy代码没有去重，再看看sqlalchemy的代码

class Query(Generative):
    ......
    def all(self):
        """
        不翻译了，留着原话，这里给去重了
        Return the results represented by this :class:`_query.Query`
        as a list.
        This results in an execution of the underlying SQL statement.
        .. warning::  The :class:`_query.Query` object,
           when asked to return either
           a sequence or iterator that consists of full ORM-mapped entities,
           will **deduplicate entries based on primary key**.  See the FAQ for
           more details.
            .. seealso::
                :ref:`faq_query_deduplicating`
        """
        return self._iter().all()
    ......