Python製WebアプリフレームワークFlaskとお供することが多いSQLAlchemyを使ったWHERE句のOR指定方法の注意点。

一時期Djangoを触っていて挫折して、その後Bottleを弄ってたらあまりにシンプルすぎるフレームワークでやること多すぎて挫折して、Flaskに流れ着いたところ。

けっこう良い気がします、Flask。

でも今回は、Flaskではなくて、MySQLとの繋ぎに使っているSQLAlchemyについて。

SQLAlchemyでSELECT … WHERE … OR/ANDするには

SQLAlchemy経由でquery実行する際のWHERE句のORやANDでデータを引っ張る方法は、以下の公式ドキュメントで詳細に解説されている。

英語だけど。

特にWHERE句で使用するWHERE clauseクラスのor/andオペレータについて、以下のように具体的に記載されている。

ふつーに|&を指定すればいいらしい。

So that looks a lot better, we added an expression to our select() which had the effect of adding WHERE users.id = addresses.user_id to our statement, and our results were managed down so that the join of users and addresses rows made sense. But let’s look at that expression? It’s using just a Python equality operator between two different Column objects. It should be clear that something is up. Saying 1 == 1 produces True, and 1 == 2 produces False, not a WHERE clause. So lets see exactly what that expression is doing:

実際にやってみると、結果は確かにTrueFalseでなくてオブジェクトが返ってくる。


(Pdb) self._db.User.c.id_ == self._db.Profile.c.userId
<sqlalchemy.sql.elements.BinaryExpression object at 0x10d460eb8>
(Pdb) type(self._db.User.c.id_ == self._db.Profile.c.userId)
<class 'sqlalchemy.sql.elements.BinaryExpression'>
(Pdb) type(self._db.User.c.id_)
<class 'sqlalchemy.sql.schema.Column'>

cメンバはsqlalchemy.sql.schema.Columnクラス。C++みたく、なんらかのオペレータがオーバーライドされているはず。

と思ってdir()したら、そのまんまなメソッド発見。


(Pdb) for i in dir(self._db.User.c.id_): print(i)
__and__
...
__or__

なるほど。


In [115]: sqlalchemy.sql.schema.Column.__or__??
Signature: sqlalchemy.sql.schema.Column.__or__(self, other)
Source:
def __or__(self, other):
"""Implement the ``|`` operator.

When used with SQL expressions, results in an
OR operation, equivalent to
:func:`~.expression.or_`, that is::

a | b

is equivalent to::

from sqlalchemy import or_
or_(a, b)

Care should be taken when using ``|`` regarding
operator precedence; the ``|`` operator has the highest precedence.
The operands should be enclosed in parenthesis if they contain
further sub expressions::

(a == 2) | (b == 4)

"""
return self.operate(or_, other)

ということで、公式チュートリアルの通り、例えばWHERE+ORしてデータを引っ張るには以下のようにすれば良い。


filter_ = None
if user_ids:
import pdb; pdb.set_trace() # for debug
filter_ = self._db.User.c.id_ == user_ids[0]
for user_id in user_ids[1:]:
filter_ |= self._db.User.c.id_ == user_id

joined_query = self._db.User.join(self._db.Profile, self._db.User.c.id_ == self._db.Profile.c.userId)
joined_query = joined_query.join(self._db.UsersAbility, self._db.User.c.id_ == self._db.UsersAbility.c.userId)
joined_query = joined_query.join(self._db.Ability, self._db.UsersAbility.c.abilityId == self._db.Ability.c.id_)
selected_query = joined_query.select()

# if filter_: # not operated... why?
if filter_ is not None:
selected_query = selected_query.where(filter_)

executed = selected_query.with_only_columns(db_columns).execute()
for record in executed.fetchall():
yield record

例えばuser_ids = [1,2,3]として、上記コードのset_trace()辺りでfilter_をダンプしてみると下記の通りで、想定通りのSQL queryが実行されそうな雰囲気。


(Pdb) str(filter_)
'"User".id_ = :id__1 OR "User".id_ = :id__2 OR "User".id_ = :id__3'

雰囲気だけでなく、もちろんfetchall()結果も想定通りのデータが返る。

注意点

filter_周辺で回りくどいことをしているのは、両方ともsqlalchemy.sql.schema.Columnオブジェクトとした上でないと__or__メソッドが動作しないため。

例えば初期値として数値オブジェクトが入ってたりすると、想定通りに動作しない。ってまぁ当たり前なんだけど。

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする