PyramidでSQLAlchemy:LIKE演算子で部分一致検索をする

前回はSQLAlchemyにてWHERE句でAND条件やOR条件の指定方法について実装方法を確認しました。

今回からは、WHERE句でよく使うであろう演算子について確認します。
具体的には、等号(=)、不等号(<、>)、LIKE、IN、BETWEEN、IS、EXISTSなどありますが、
今回はLIKE演算子について見ていきます。

以下のようなSQLを実行する方法を確認します。

SELECT * FROM models WHERE name LIKE  'o%';
SELECT * FROM models WHERE name LIKE  '%n%';
SELECT * FROM models WHERE name LIKE  '%e';

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

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

LIKE演算子を使って部分一致の条件指定をするには、以下のようにfilterメソッドの引数にて、条件指定したいカラムに対してlikeメソッドを以下の様に適用させます。

result = DBSession.query(MyModel).filter(MyModel.name.like('o%')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value 
FROM models 
WHERE models.name LIKE %(name_1)s

MyModel.nameに対してLIKE演算子が適用されています。
上記の例ではlikeメソッドに、’o%’という値を渡したので前方一致検索になりました。

後方一致検索にするには、以下のようにします。
result = DBSession.query(MyModel).filter(MyModel.name.like('%o')).all()

中間一致検索にするには、以下の様にします。
result = DBSession.query(MyModel).filter(MyModel.name.like('%o%')).all()

このように、likeメソッドの引数の与え方で、前方一致か後方一致か部分一致か使い分けられます。
直感的で分かり易いですね。

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

LIKEの反対であるNOT LIKE条件を指定するには、notlike メソッドを使って以下のように実装します。
result = DBSession.query(MyModel).filter(MyModel.name.notlike('o%')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.name NOT LIKE %(name_1)s

MyModel.nameに対してNOT LIKE演算子が適用されています。

(3) ワイルドカード文字を隠蔽する指定の仕方

余談ではありますが、上記1,2の例では、likeメソッド、notlikeメソッドの引数に、ワイルドカード文字である%を記述していました。
そもそもワイルドカード文字は、DBMSだったり接続するドライバだったりに依存する部分があります。
ORMはDMBSをラッピングして隠蔽するものですので、ここでプログラム中にDMBS依存のワイルドカード文字を明示的に指定するのは、ORMを利用する場合の思想的にいかがなものなのでしょうか?

もちろん通常のシステム開発ではDBMSは決まっていて、途中でコロコロ変更になるものではありませんので、
様々なDBMSに対応するパッケージ製品を作る場合以外では対して気にする必要も無い事ですが、ちょっと気になり調べました。

ワイルドカード文字を直接記述せずに、部分一致検索が出来るメソッドとして、startswith、endswith、containsメソッドが用意されています。それぞれ前方一致、後方一致、中間一致に相当します。

startswithメソッドは以下のように使います。
result = DBSession.query(MyModel).filter(MyModel.name.startswith('o')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.name LIKE concat(%(name_1)s, '%%')

startswithメソッドの引数ではワイルドカード文字は入れていませんが、SQLAlchemyにて構築されたSQLの中で、cancat関数を使ってパラメータとワイルドカード文字が結合されています。
LIKE演算子で前方一致検索をするという点では、likeメソッドを使った場合と同じですが、ワイルドカード文字をプログラムコードから排除する事ができています。

同様にendswithは次のようになります。
result = DBSession.query(MyModel).filter(MyModel.name.endswith('o')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.name LIKE concat('%%', %(name_1)s)

containsは次のようになります。
result = DBSession.query(MyModel).filter(MyModel.name.conains('o')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.name LIKE concat(concat('%%', %(name_1)s), '%%')

以上の様に、startswith、endswith、containsの各メソッドで部分一致検索を実装できる事が確認できました。
でも個人的にはlikeメソッドで良いかなと思っています。

(4) like_opという関数もあります。

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

使い方はこれです。
result = DBSession.query(MyModel).filter(like_op(MyModel.name,'%n%')).all()

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

SELECT models.id AS models_id, models.name AS models_name, models.value AS models_value
FROM models
WHERE models.name LIKE %(name_1)s

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

like_opと同様に、notlike_opとか、startswith_opとかもありますので興味のある人はご自身で調べてみてください。

今回はここまで。
SQLAlchemyでのLIKE演算子を使った部分一致条件の指定方法を確認しました。

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