基于flask_sqlachemy的分頁(yè)

flask 項(xiàng)目中使用分頁(yè)paginate
報(bào)錯(cuò) AttributeError:Query object has no attribute 'paignate'
創(chuàng)建model的時(shí)候使用的是sqlachemy,而不是flask_sqlachemy,而分頁(yè)paginate方法只在flask_sqlchemy中提供码荔，同樣get_or_404()和first_or_404()也只在flask_sqlachemy中才有纤控，所以要想使用，必須修改model的創(chuàng)建方式纽哥，基于flask_sqlalchemy才可以逮光。

https://stackoverflow.com/questions/18409645/sqlalchemy-does-not-work-with-pagination
屬性：
items : 表示獲得的查詢結(jié)果
pages : 表示一共有多少頁(yè)
page ：獲得當(dāng)前頁(yè)碼數(shù)
total ：數(shù)據(jù)總條數(shù)
has_prev: 是否有上一頁(yè)
has_next: 是否有下一頁(yè)
prev_num：上一頁(yè)頁(yè)碼
next_num：下一頁(yè)頁(yè)碼
iter_page()：當(dāng)前頁(yè)的頁(yè)碼列表

關(guān)聯(lián)查詢

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 的時(shí)候誰(shuí)先誰(shuí)后呢？看看這個(gè)錯(cuò)誤就知道了：

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 中參數(shù)的順序很重要嗡综，query 的第一個(gè)參數(shù)就代表著第一個(gè)table是誰(shuí)。故杜漠，此處 JOIN 的目標(biāo)應(yīng)該是 Alipay_B_Bus极景， 而不應(yīng)該是 Alipay_B_Order。

聯(lián)表分頁(yè)

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'

報(bào)錯(cuò)的原因是db.session.query 默認(rèn)返回的是 orm.Query 對(duì)象驾茴，這個(gè)對(duì)象并不包含paginate方法盼樟。
要解決這個(gè)問(wèn)題:

方法一

修改 Flask-SQLAlchemy 的源碼

找到 SQLAlchemy 對(duì)象的 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 等方法創(chuàng)建的 Query 對(duì)象支持 BaseQuery 的方法
    session_options['query_cls'] = BaseQuery

方法二

另一種關(guān)聯(lián)查詢語(yǔ)法

在 Flask-SQLAlchemy 提供的 Model 對(duì)象中晨缴，可以使用 Model.query 這樣的語(yǔ)法來(lái)直接得到一個(gè)查詢對(duì)象，這是由于 Flask-SQLAlchemy 中存在一個(gè) _QueryProperty 類峡捡，每次調(diào)用 Model.get時(shí)击碗，會(huì)自動(dòng)生成一個(gè)基于當(dāng)前 session 的 query 對(duì)象：

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 中聯(lián)表查詢

class BaseQuery(orm.Query):
    def paginate(self, page=None, per_page=None, error_out=True, max_per_page=None, count=True):
        """
        當(dāng)“error_out”為"True"時(shí)，符合下面判斷時(shí)將引起404響應(yīng)们拙，我一般都關(guān)了這個(gè)
        error_out稍途，把接口返回什么的控制權(quán)拿回來(lái)。
        """
        //如果"page"或"per-page"是"None",則將從請(qǐng)求查詢
        //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: // 如果沒(méi)有請(qǐng)求分別默認(rèn)為1和20
            if page is None:
                page = 1
            if per_page is None:
                per_page = 20
        //如果指定了“max_per_page”械拍，則“per_page”將受這個(gè)值鉗制。
        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
        //原來(lái)paginate就是用的limit和offset装盯，我猜如果有重復(fù)數(shù)據(jù)坷虑，先limit后all，all的時(shí)候再去個(gè)重验夯，數(shù)據(jù)就少了猖吴，分頁(yè)也沒(méi)了
        items = self.limit(per_page).offset((page - 1) * per_page).all()
        ......
        //如果"count"是"False",total就不能用了,不用的時(shí)候可以關(guān)掉,省個(gè)查詢
        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代碼沒(méi)有去重摔刁，再看看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()
    ......