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でなくてオブジェクトが返ってくる。
[code lang=python]
(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'>
[/code]
cメンバはsqlalchemy.sql.schema.Columnクラス。C++みたく、なんらかのオペレータがオーバーライドされているはず。
と思ってdir()したら、そのまんまなメソッド発見。
[code lang=python]
(Pdb) for i in dir(self._db.User.c.id_): print(i)
__and__
...
__or__
[/code]
なるほど。
[code lang=python]
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)
[/code]
ということで、公式チュートリアルの通り、例えばWHERE+ORしてデータを引っ張るには以下のようにすれば良い。
[code lang=python]
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
[/code]
例えばuser_ids = [1,2,3]として、上記コードのset_trace()辺りでfilter_をダンプしてみると下記の通りで、想定通りのSQL queryが実行されそうな雰囲気。
[code lang=python]
(Pdb) str(filter_)
'"User".id_ = :id__1 OR "User".id_ = :id__2 OR "User".id_ = :id__3'
[/code]
雰囲気だけでなく、もちろんfetchall()結果も想定通りのデータが返る。

注意点

filter_周辺で回りくどいことをしているのは、両方ともsqlalchemy.sql.schema.Columnオブジェクトとした上でないと__or__メソッドが動作しないため。
例えば初期値として数値オブジェクトが入ってたりすると、想定通りに動作しない。ってまぁ当たり前なんだけど。

シェアする

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

フォローする

Close Bitnami banner
Bitnami