SQLAlchemy 是一個(gè)功能強(qiáng)大的 ORM 筝尾。 Flask-SQLAlchemy 是一個(gè) Flask 插件粟关，它讓我們?cè)?Flask 框架中使用 SQLAlchemy 變得更容易统诺。

本篇介紹我在使用 Flask-SQLAlchemy 2.1 時(shí)進(jìn)行聯(lián)表查詢的一些經(jīng)驗(yàn)

表定義

這里有兩個(gè)表袱蚓，account 表保存帳號(hào) ID 和昵稱肮塞，bind 表保存 account 之間的綁定關(guān)系。

# 省略了外鍵定義梯澜，請(qǐng)自行腦補(bǔ)
create table account
(
    gameuid int auto_increment primary key,
    nickname varchar(34) not null
);
create table bind
(
    bindid int auto_increment primary key,
    fromid int not null,
    toid int not null
);

對(duì)應(yīng)的 Model 如下：

class Account(db.Model):
    __tablename__ = 'account'
    gameuid = db.Column(db.INT, primary_key=True, nullable=False, autoincrement=True)
    nickname = db.Column(db.VARCHAR(64), nullable=False, unique=True)
    def __repr__(self):
        return '<Account %r>' % (self.gameuid)

class Bind(db.Model):
    __tablename__ = 'bind'
    bindid = db.Column(db.BIGINT, primary_key=True, autoincrement=True)
    # 綁定者和被綁定者的 gameuid
    fromid = db.Column(db.BIGINT, db.ForeignKey('account.gameuid'), nullable=False)
    toid = db.Column(db.BIGINT, db.ForeignKey('account.gameuid'), nullable=False)
    def __repr__(self):
        return '<Bind %r.%r>' % (self.fromid, self.toid)

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

先來(lái)看一個(gè)簡(jiǎn)單的例子：查詢 gameuid 1000 賬號(hào)下綁定的所有帳號(hào)寞冯。

>>> db.session.query(Bind.bindid, Bind.fromid, Bind.toid, Account.gameuid, Account.nickname). \
    filter(Bind.toid == 1000). \
    filter(Bind.fromid == Account.gameuid). \
    all()
[(2, 10001, 1000, 10001, '玩家10001'), (3, 10002, 1000, 10002, '玩家10002'), (4, 10003, 1000, 10003, '玩家10003'), (5, 10004, 1000, 10004, '玩家10004'), (6, 10005, 1000, 10005, '玩家10005'), (7, 10006, 1000, 10006, '玩家10006'), (8, 10007, 1000, 10007, '玩家10007'), (9, 10008, 1000, 10008, '玩家10008'), (10, 10009, 1000, 10009, '玩家10009'), (53, 10000, 1000, 10000, '玩家10000'), (54, 11000, 1000, 11000, '玩家11000')]

看一看生成的 SQL 語(yǔ)句：

>>> print(db.session.query(Bind.bindid, Bind.fromid, Bind.toid, Account.gameuid, Account.nickname). \
    filter(Bind.toid == 1000). \
    filter(Bind.fromid == Account.gameuid))
SELECT bind.bindid AS bind_bindid, bind.fromid AS bind_fromid, bind.toid AS bind_toid, account.gameuid AS account_gameuid, account.nickname AS account_nickname
FROM bind, account
WHERE bind.toid = %(toid_1)s AND bind.fromid = account.gameuid

這里的聯(lián)表查詢使用的是 WHERE 語(yǔ)句。如果希望使用 JOIN 語(yǔ)句晚伙，可以這樣寫：

>>> print(db.session.query(Bind.bindid, Account.gameuid, Account.nickname). \
    join(Account, Account.gameuid==Bind.fromid). \
    filter(Bind.toid == 1000))
SELECT bind.bindid AS bind_bindid, bind.fromid AS bind_fromid, account.gameuid AS account_gameuid, account.nickname AS account_nickname
FROM bind INNER JOIN account ON account.gameuid = bind.fromid
WHERE bind.toid = %(toid_1)s

可以看出吮龄，現(xiàn)在生成的 SQL 語(yǔ)句已經(jīng)使用 JOIN 語(yǔ)句了。但上面的語(yǔ)意有點(diǎn)奇怪撬腾，既然已經(jīng)在 query 中使用了 Bind 和 Account螟蝙，后面再 join 一次 Account 總覺(jué)得有點(diǎn)多余恢恼。那么 SQLAlchemy 如何選擇 JOIN 的時(shí)候誰(shuí)先誰(shuí)后呢民傻？看看這個(gè)錯(cuò)誤就知道了：

>>> db.session.query(Bind.bindid, Bind.fromid, Account.gameuid, Account.nickname). \
    join(Bind, Account.gameuid==Bind.fromid). \
    filter(Bind.toid == 1000)
>>> Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/zrong/.pyvenv/api/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 1971, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/Users/zrong/.pyvenv/api/lib/python3.6/site-packages/sqlalchemy/orm/base.py", line 201, in generate
    fn(self, *args[1:], **kw)
  File "/Users/zrong/.pyvenv/api/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2115, in _join
    outerjoin, full, create_aliases, prop)
  File "/Users/zrong/.pyvenv/api/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2171, in _join_left_to_right
    l_info.selectable)
sqlalchemy.exc.InvalidRequestError: Can't join table/selectable 'bind' to itself

這個(gè)錯(cuò)誤顯然說(shuō)明，query 中參數(shù)的順序很重要场斑，第一個(gè)參數(shù)所代表的 table 就是 JOIN 時(shí)放在前面的那個(gè) table漓踢。因此，此處 JOIN 的目標(biāo)應(yīng)該是 Account漏隐， 而不應(yīng)該是 Bind 自身喧半。

分頁(yè)支持

上面的例子已經(jīng)解決了大多數(shù)需求了。我們?cè)賮?lái)看看分頁(yè)青责。在 Flask-SQLAlchemy 中封裝了一個(gè) paginate 方法挺据，可以方便地將查詢記錄進(jìn)行分頁(yè)：

>>> db.session.query(Bind.bindid, Bind.fromid, Account.gameuid, Account.nickname). \
    join(Bind, Account.gameuid==Bind.fromid). \
    filter(Bind.toid == 1000). \
    paginate(1, 10)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
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ì)象：

class _QueryProperty(object):

    def __init__(self, sa):
        self.sa = sa

    def __get__(self, obj, type):
        try:
            mapper = orm.class_mapper(type)
            if mapper:
                return type.query_class(mapper, session=self.sa.session())
        except UnmappedClassError:
            return None

使用 Model.query得到的這個(gè) query 對(duì)象可以直接進(jìn)行 JOIN 操作，得到的結(jié)果是 Model 對(duì)象俗壹。這樣就方便多了：

>>> Account.query.join(Bind, Bind.fromid == Account.gameuid).filter(Bind.toid == 1000).all()
[<Account 10001>, <Account 10002>, <Account 10003>, <Account 10004>, <Account 10005>, <Account 10006>, <Account 10007>, <Account 10008>, <Account 10009>, <Account 10000>, <Account 11000>]

轉(zhuǎn)換成 SQL 是這樣的：

SELECT account.gameuid AS account_gameuid, account.nickname AS account_nickname
FROM account INNER JOIN bind ON bind.fromid = account.gameuid
WHERE bind.toid = %(toid_1)s

可以看出科汗，這樣的查詢結(jié)果和使用 db.session.query 并沒(méi)有什么不同。由于返回的是 Model 對(duì)象绷雏，使用上可能還更加方便了肛捍。

篩選字段

如何使用 Model.query.join 語(yǔ)法得到部分字段呢？這里可以使用 SQLAlchemy 提供的 with_entities 方法：

>>> Account.query.join(Bind, Bind.fromid == Account.gameuid). \
    filter(Bind.toid == 1000). \
    with_entities(Bind.bindid, Account.nickname).all()
[(2, '玩家10001'), (3, '玩家10002'), (4, '玩家10003'), (5, '玩家10004'), (6, '玩家10005'), (7, '玩家10006'), (8, '玩家10007'), (9, '玩家10008'), (10, '玩家10009'), (53, '玩家10000'), (54, '玩家11000')]
>>>

注意之众，列表中的項(xiàng) (2, '玩家10001') 并不是標(biāo)準(zhǔn)的 Python tuple拙毫。你如果查看它的類型，會(huì)發(fā)現(xiàn)一個(gè)奇怪的名稱： <class 'sqlalchemy.util._collections.result'> 棺禾。它是一個(gè) AbstractKeyedTuple 對(duì)象缀蹄，擁有一個(gè) keys() 方法，這樣可以很容易將其轉(zhuǎn)換成 dict ：

>>> results = Account.query.join(Bind, Bind.fromid == Account.gameuid). \
    filter(Bind.toid == 1000). \
    with_entities(Bind.bindid, Account.nickname).all()
>>> [dict(zip(result.keys(), result)) for result in results]
[{'bindid': 2, 'nickname': '玩家10001'}, {'bindid': 3, 'nickname': '玩家10002'}, {'bindid': 4, 'nickname': '玩家10003'}, {'bindid': 5, 'nickname': '玩家10004'}, {'bindid': 6, 'nickname': '玩家10005'}, {'bindid': 7, 'nickname': '玩家10006'}, {'bindid': 8, 'nickname': '玩家10007'}, {'bindid': 9, 'nickname': '玩家10008'}, {'bindid': 10, 'nickname': '玩家10009'}, {'bindid': 53, 'nickname': '玩家10000'}, {'bindid': 54, 'nickname': '玩家11000'}]

想了解 AbstractKeyedTuple 膘婶，可以看看這篇文檔 New KeyedTuple implementation dramatically faster 缺前。

獲得多個(gè) Model 的記錄

除了篩選字段外，還可以用另一個(gè)方法獲取多個(gè) Model 的記錄悬襟。那就是衅码，返回兩個(gè) Model 的所有字段：

>>> db.session.query(Account, Bind).join(Bind, Account.gameuid==Bind.fromid).filter(Bind.toid==1000).all()
[(<Account 10001>, <Bind 10001, 1000>), (<Account 10002>, <Bind 10002, 1000>), (<Account 10004>, <Bind 10004, 1000>), (<Account 10005>, <Bind 10005, 1000>), (<Account 10006>, <Bind 10006, 1000>), (<Account 10007>, <Bind 10007, 1000>), (<Account 10008>, <Bind 10008, 1000>), (<Account 10009>, <Bind 10009, 1000>), (<Account 10000>, <Bind 10000, 1000>), (<Account 11000>, <Bind 11000, 1000>)]

使用上面的語(yǔ)法直接返回 Account 和 Bind 對(duì)象，可以進(jìn)行更加靈活的操作脊岳。

多表查詢

要聯(lián)結(jié)超過(guò) 2 張以上的表逝段，可以直接在 join 得到的結(jié)果之后鏈?zhǔn)秸{(diào)用 join 。也可以在 filter 的結(jié)果后面鏈?zhǔn)秸{(diào)用 join 割捅。join 和 filter 返回的都是 query 對(duì)象奶躯，因此可以無(wú)限鏈?zhǔn)秸{(diào)用下去。

寫完查詢后亿驾，應(yīng)該打印生成的 SQL 語(yǔ)句查看一下有沒(méi)有性能問(wèn)題嘹黔。

原文作者：zrong
原文鏈接：https://blog.zengrong.net/post/join-in-flash-sqlalchemy/