PyramidでSQLAlchemy:IN演算子でOR検索をする

前回はSQLAlchemyにてWHERE句でLIKEを使った部分一致検索の指定方法について確認しました。

今回は、IN演算子の使い方について確認します。

以下のSQLを実行する事を目指します。

SELECT * FROM models WHERE id IN ( 1, 2, 3 );
SELECT * FROM models WHERE id NOT IN ( 1, 2, 3 );
SELECT * FROM models WHERE name IN ( 'one', 'two' );
SELECT * FROM models WHERE name NOT IN ( 'one', 'two' );

確認するデータベース環境は前回と同じです。

(1) in_メソッドを使って、IN条件を指定する

IN演算子を使って複数の値のOR条件指定をするには、filterメソッドの引数にて、条件指定したいカラムに対してin_メソッドを以下の様に適用させます。
IN演算子に指定する値はリスト、またはタプルです。

result = DBSession.query(MyModel).filter(MyModel.id.in_([1,2,3])).all()

ログに出力されたSQLは以下でした。

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.id IN (%(id_1)s, %(id_2)s, %(id_3)s)

MyModel.idに対してIN演算子が適用されています。
上記の例ではIN演算子に、リストで[1,2,3]という3つの値を指定しましたが、それらが1つずつパラメータ化されたSQLが構築されています。
簡単ですね。

文字列のカラムに対しても同様で、以下のようにします。
result = DBSession.query(MyModel).filter(MyModel.name.in_(['one','two'])).all()

(2) notin_メソッドを使って、NOT IN条件を指定する。

INの反対であるNOT IN条件を指定するには、notin_ メソッドを使って以下のように実装します。
result = DBSession.query(MyModel).filter(MyModel.id.notin_([1,2,3])).all()

ログに出力されたSQLは以下でした。

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.id NOT IN (%(id_1)s, %(id_2)s, %(id_3)s)

MyModel.idに対してNOT IN演算子が適用されています。
また余談ですが、NOTを実行するには、~とin_を組み合わせて以下の様にしても同じ結果が得られます。~が否定を意味するようです。

result = DBSession.query(MyModel).filter(~MyModel.id.in_([1,2,3])).all()

(3) in_op、notin_opという関数もあります。

これも余談になりますが、上記で見てきたもの以外にもin_op、notin_opという関数もあります。
sqlalchemy.sql.operatorsにて定義されているので、以下の様にimportすると使えます。
from sqlalchemy.sql.operators import in_op, notin_op

使い方はこれです。
result =DBSession.query(MyModel).filter(in_op(MyModel.id, [1,2,3])).all()

この場合、ログに出力されたSQLは以下でした。

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.id IN (%(id_1)s, %(id_2)s, %(id_3)s)

in_メソッドと同じですね。
in_opの定義を見れば分かりませすが、内部的にはin_メソッドを読んでいるだけでした。
なので、同じ結果がでるのは当たり前ですね。
notin_opも同様です。

(4)SQL文を指定して実行する方法
SQL文を直接指定して実行する場合は、前回同様
Sessionオブジェクトのexecuteメソッドを使います。
executeのパラメータに、条件指定する値を以下のように指定して実行します。

result = DBSession.execute(
"SELECT * FROM models where id in (:id1, :id2)",{'id1':1, 'id2':2}
)

実行すると、ログにはORMを使った場合と同様に以下が出力されました。

SELECT * FROM models where id in (%(id1)s, %(id2)s)

この場合、条件値をリストやタプルで指定できないので、固定値で決まっている場合は良いですが、可変な場合はSQL文への変数の組み込みを動的にしなければいけないので面倒です。
INを使う場合はORMを使った方が簡単ですね。。

今回はここまで。
SQLAlchemyでのIN演算子を使った検索方法を確認しました。

次回は、IS演算子について確認します。