クエリー演算子¶

peeweeでは以下の形式の比較方法をサポートしています:

比較	意味
`==`	x と y が等しい
`<`	x は y 未満
`<=`	x は y 以下
`>`	x は y より大きい
`>=`	x は y 以上
`!=`	x は y と異なる
`<<`	x IN y, y はリストまたはクエリー
`>>`	x IS y, y は None または NULL
`%`	x LIKE y, y はワイルドカードを含んでもよい
`**`	x ILIKE y, y はワイルドカードを含んでもよい
`^`	x XOR y
`~`	否定の単項演算子 (すなわち NOT x)

すでにオーバーライド可能な演算子記号を使い尽くしてしまっているため, 以下のクエリー演算子をメソッドとして用意しています:

メソッド	意味
`.in_(value)`	IN 探索 (`<<` と同じ).
`.not_in(value)`	NOT IN 探索.
`.is_null(is_null)`	IS NULL または IS NOT NULL. ブール型パラメータも.
`.contains(substr)`	サブ文字列のワイルドカード検索.
`.startswith(prefix)`	`prefix` で始まる値の検索.
`.endswith(suffix)`	`suffix` で終わる値の検索.
`.between(low, high)`	`low` と `high` の間にある値の検索.
`.regexp(exp)`	正規表現マッチング（大文字小文字を区別する）.
`.iregexp(exp)`	正規表現マッチング（大文字小文字を区別しない）.
`.bin_and(value)`	バイナリの AND.
`.bin_or(value)`	バイナリの OR.
`.concat(other)`	`\|\|` で２つの文字列またはオブジェクトを連結.
`.distinct()`	そのカラムを DISTINCT select としてマーク.
`.collate(collation)`	そのカラムに対して指定した照合順序を適用.
`.cast(type)`	そのカラムの値を指定した型でキャスト.

論理演算を使って句を連結する際に使う演算子:

演算子	意味	使用例
`&`	AND	`(User.is_active == True) & (User.is_admin == True)`
`\|` (pipe)	OR	`(User.is_admin) \| (User.is_superuser)`
`~`	NOT (否定単項演算子)	`~(User.username.contains('admin'))`

これらのクエリー演算子の使用例:

# username が "charlie" であるユーザを見つける.
User.select().where(User.username == 'charlie')

# username が [charlie, huey, mickey] のいずれかであるユーザを見つける.
User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))

Employee.select().where(Employee.salary.between(50000, 60000))

Employee.select().where(Employee.name.startswith('C'))

Blog.select().where(Blog.title.contains(search_string))

次は式を結合する方法です. 比較はどんなに複雑でも構いません.

Note

実際の比較部分は括弧で括られます. Python の演算子の優先度の都合により, 比較部分は括弧で括られている必要があります.

# アクティブな管理者であるユーザを見つける.
User.select().where(
  (User.is_admin == True) &
  (User.is_active == True))

# 管理者もしくはスーパーユーザであるユーザを見つける.
User.select().where(
  (User.is_admin == True) |
  (User.is_superuser == True))

# 管理者でない(NOT IN)ユーザのツイートを見つける.
admins = User.select().where(User.is_admin == True)
non_admin_tweets = Tweet.select().where(Tweet.user.not_in(admins))

# 自分のfriendsでない（知らない）ユーザを見つける
friends = User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
strangers = User.select().where(User.id.not_in(friends))

Warning

自分のクエリーの中で Python の``in``, and, or, not といった演算子を使いたくなる誘惑に駆られるかもしれませんが,これらは 動きません. in 式の戻り値は,常に強制的にブール値に変更されます.同様に,``and``, or, not の引数もブール値になってしまい,これをオーバーオードすることはできません.

このため,以下のことだけを覚えておきましょう:

in と not in の代わりに .in_() と .not_in() を使う
and の代わりに & を使う
or の代わりに | を使う
not の代わりに ~ を使う
is None や == None の代わりに .is_null() を使う.
論理演算子を使う場合は,比較部分全体を括弧で囲むのを忘れないこと.

これ以外の例については, 表現式(Expressions) の章を参照してください.

Note

SQLite における LIKE とILIKE

SQLite の LIKE はデフォルトではケース（大文字小文字）を区別しないため, peewee はケースを区別する検索の場合, SQLite の GLOB 機能を使います. glob 機能ではワイルドカードを表す際,通常のパーセント記号ではなくアスタリスクを使います. もし SQLite を使ってケースを区別する部分一致検索を行う場合, ワイルドカードにはアスタリスクを使うことを覚えておいてください.

Three valued logic¶

SQL の NULL を扱うために, 特別な書き方をする表現式があります:

IS NULL
IS NOT NULL
IN
NOT IN

IS NULL や IN 演算子を否定演算子 (~) と組み合わせて使うことはできますが,文脈をはっきりさせるため,明示的に IS NOT NULL と NOT IN を使う必要がある場合があります.

IS NULL と IN を使うための最も簡単な方法は,演算子オーバーロードを使う方法です:

# last login が NULL である User オブジェクトをすべて取得する
User.select().where(User.last_login >> None)

# username が指定されたリストに含まれるユーザを取得する
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username << usernames)

演算子オーバーロードを使いたくない場合,代わりにフィールドメソッドを呼ぶこともできます:

# last login が NULL である User オブジェクトをすべて取得する
User.select().where(User.last_login.is_null(True))

# username が指定されたリストに含まれるユーザを取得する
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.in_(usernames))

前述とは反対の意味のクエリーを使いたい場合,単項否定演算子も使えます.ただし文脈上正確に意図を示したい場合,特別な IS NOT / NOT IN 演算子が使えます:

# last login が NULL *でない* User オブジェクトをすべて取得する
User.select().where(User.last_login.is_null(False))

# これを単項否定演算子を使って行う.
User.select().where(~(User.last_login >> None))

# username が指定されたリストに *含まれない* ユーザを取得する
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.not_in(usernames))

# これを単項否定演算子を使って行う.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(~(User.username << usernames))

ユーザ定義演算子を追加する¶

オーバーロードできそうな Python の演算子を使い尽くしてしまったため,peewee にはたとえば 剰余 のように、存在しない演算子があります.前述の表の中に自分が使いたい演算子がなかった場合,簡単に自分専用の演算子を追加できます.

SQlite で 剰余 サポートを追加するには以下のようにします:

from peewee import *
from peewee import Expression # 式のビルド部分

def mod(lhs, rhs):
    return Expression(lhs, '%', rhs)

これらのカスタム演算子を使うことで、より直感的なクエリーをビルド(構築) できるようになりました:

# id が奇数のユーザ
User.select().where(mod(User.id, 2) == 0)

これ以外の使用例については playhouse.postgresql_ext モジュールのソースを参照してみてください.この中には postgresql の hstore に固有の演算子が多数含まれています.

表現式(Expressions)¶

Peewee はシンプルで表現力豊かな,かつpython的な方法によるSQLクエリー構築ができるようにデザインされています.この章では一般的な表現式についての概観をみてみましょう.

Peewee では２つのタイプのオブジェクトを組み合わせて表現式を生成します:

Field インスタンス
SQL の集約関数と fn を使った関数

ユーザ名その他の項目を持つ,シンプルな “User” モデルを考えてみましょう. それは以下のようになります:

class User(Model):
    username = CharField()
    is_admin = BooleanField()
    is_active = BooleanField()
    last_login = DateTimeField()
    login_count = IntegerField()
    failed_logins = IntegerField()

比較の際はクエリー演算子を使います:

# username が 'charlie' と等しい
User.username == 'charlie'

# ログイン回数が５回未満のユーザ
User.login_count < 5

比較の際は ビットごとの and と or を組み合わせます. 演算子の優先順位は python によって制御され,比較は入れ子にして任意の深さにできます:

# admin でかつ今日ログインしたユーザ
(User.is_admin == True) & (User.last_login >= today)

# ユーザ名が charlie または charles
(User.username == 'charlie') | (User.username == 'charles')

比較は関数を使って行うこともできます:

# ユーザ名が 'g' または 'G' で始まるユーザ
fn.Lower(fn.Substr(User.username, 1, 1)) == 'g'

表現式を別の表現式と比較することができるので,ちょっとおもしろいことをやってみましょう. 表現式では算術演算子もサポートしています:

# ログイン失敗の回数が成功回数の半分を超えるものの,
# 少なくとも10回はログインしたことがあるユーザ
(User.failed_logins > (User.login_count * .5)) & (User.login_count > 10)

表現式では アトミック(途中の割り込みがないことが保証されている)な更新 が行えます:

# ユーザがログインした時,それらのログイン回数をインクリメントしたい:
User.update(login_count=User.login_count + 1).where(User.id == user_id)

表現式はクエリー内のすべての部分で使えます.これは新機軸です!

行の値(row values)¶

多くのデータベースでは行の値(row values) をサポートしていますが,これは python のタプル(tuple) オブジェクトとよく似ています. Peewee では表現式における行の値は Tuple 経由で利用可能です. 以下に例を示します.

# もしあなたのスキーマで何らかの理由で日付を ("year", "month", "day") という
# 別々のカラムに格納している場合でも,行の値を使って指定月に属するすべての行を
# 見つけることが可能です:
Tuple(Event.year, Event.month) == (2019, 1)

行の値に関するより一般的な使い方を,単一表現式の中のサブクエリーから生成される複数カラムの場合と比較してみましょう.これらのタイプのクエリーを表現するための他の方法もありますが,行の値を利用する方法は完結で読みやすいアプローチです.

ここでは例として “EventLog” というテーブルがあり,この中にはイベントタイプとイベントソース,その他のメタデータが入っているとします.さらに “IncidentLog” もあり,これにはインシデントタイプ,インシデントソース,メタデータのカラムが入っています.行の値を使えば,インシデントと特定のイベントを関連付けることが可能です:

class EventLog(Model):
    event_type = TextField()
    source = TextField()
    data = TextField()
    timestamp = TimestampField()

class IncidentLog(Model):
    incident_type = TextField()
    source = TextField()
    traceback = TextField()
    timestamp = TimestampField()

# 本日発生したインシデントのタイプとソースの一覧をすべて取得する
incidents = (IncidentLog
             .select(IncidentLog.incident_type, IncidentLog.source)
             .where(IncidentLog.timestamp >= datetime.date.today()))

# 本日発生したイベントの中で,タイプとソースがインシデントに関連する
# ものをすべて見つける.
events = (EventLog
          .select()
          .where(Tuple(EventLog.event_type, EventLog.source).in_(incidents))
          .order_by(EventLog.timestamp))

このタイプのクエリーを表現するための他の方法としては, join やサブクエリーに対する join が使えます. 上記の例は,単に Tuple を使うためのアイデアを提供したに過ぎません.

新しいデータがサブクエリーから発生した場合,行の値を使ってテーブル内の複数のカラムを更新することもできます.この利用例としては here を参照してください.

SQL 関数¶

COUNT() や SUM() といった SQL 関数は, fn() ヘルパー: を使って表現できます:

# すべてのユーザと,彼らがつぶやいた多数のツイートを取得する.
# 結果はツイート数の多い方から降順にソートする.
query = (User
         .select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(fn.COUNT(Tweet.id).desc()))
for user in query:
    print('%s -- %s tweets' % (user.username, user.tweet_count))

fn ヘルパーは,任意の SQL 関数をあたかもメソッドであるかのように見せてくれます.パラメータはフィールド,値,サブクエリーに加えて,さらにネストした関数も指定できます。

ネストした関数呼び出し¶

ここであなたは,ユーザ名が a で始まるユーザの一覧を取得する必要があるとします. このやり方はいくつかありますが,*LOWER* や SUBSTR といった SQL 関数を使えるメソッドが1つあります.任意の SQL 関数を使いたい場合は, fn() という特別なオブジェクトを使ってクエリーを構築します.

# ユーザの id とユーザ名, およびユーザ名の先頭1文字を小文字にした
# ものを select する
first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
query = User.select(User, first_letter.alias('first_letter'))

# 別の方法として,ユーザ名が 'a' で始まるユーザだけを select することも可能
a_users = User.select().where(first_letter == 'a')

>>> for user in a_users:
...    print(user.username)

SQL ヘルパー¶

単に任意の SQL 文を渡したいという場合もあると思います.この場合,特別な SQL クラスが使えます.ユースケースのひとつして,別名を参照したい場合を示します:

# ユーザテーブルに問い合わせを行い,特定のユーザについてツイート数の
# 注釈を付ける.
query = (User
         .select(User, fn.Count(Tweet.id).alias('ct'))
         .join(Tweet)
         .group_by(User))

# 次に "ct" という別名を付けられた回数フィールドでソートする.
query = query.order_by(SQL('ct'))

# これは当然以下のようにも書ける:
query = query.order_by(fn.COUNT(Tweet.id))

Peewee で手作りの SQL ステートメントを実行したい場合,以下の２つの方法がある:

任意のタイプのクエリーを実行するための Database.execute_sql()
SELECT クエリーを発行してモデルのインスタンスを返す RawQuery

セキュリティと SQL インジェクション¶

peewee はデフォルトでクエリーをパラメータ化するため,ユーザから渡されたパラメータはすべてエスケープされます.このルールにおける唯一の例外は,生の SQL クエリーを書いたり, 信頼できないデータを含む恐れがある SQL オブジェクトを渡す場合です.危険性を軽減するため,いかなるユーザ定義データもクエリーパラメータとして渡し,実際の SQL クエリーの一部として渡すことがないようにしてください:

# やっちゃダメ！
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s' % (user_data,))

# 大丈夫. `user_data` はクエリーへのパラメータとして扱われます.
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)

# やっちゃダメ！
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))

# 大丈夫. `user_data` はパラメータとして扱われます.
query = MyModel.select().where(SQL('Some SQL expression %s', user_data))

Note

MySQL と Postgresql は '%s' をパラメータの印として使用します.一方 SQLite は '?' を使います.お使いのデータベースに適した文字を使うように心がけてください.このパラメータは Database.param をチェックすることで見つけらることができます.