クエリ演算子

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 <= value <= high` の検索。
`.regexp(exp)`	正規表現マッチ (大文字と小文字を区別)。
`.iregexp(exp)`	正規表現マッチ (大文字と小文字を区別しない)。
`.bin_and(value)`	バイナリ AND。
`.bin_or(value)`	バイナリ OR。
`.concat(other)`	`\|\|` を使用して、2 つの文字列またはオブジェクトを連結します。
`.distinct()`	DISTINCT選択のための列をマークします。
`.collate(collation)`	指定された照合順序で列を指定します。
`.cast(type)`	列の値を指定された型にキャストします。

論理演算子を使用して句を組み合わせるには、以下を使用します。

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

これらのクエリ演算子のいくつかを使用する方法を次に示します。

# Find the user whose username is "charlie".
User.select().where(User.username == 'charlie')

# Find the users whose username is in [charlie, huey, mickey]
User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))

# Find users whose salary is between 50k and 60k (inclusive).
Employee.select().where(Employee.salary.between(50000, 60000))

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

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

式を組み合わせる方法を次に示します。比較は任意に複雑にできます。

注意

実際の比較は括弧で囲まれていることに注意してください。Python の演算子の優先順位により、比較を括弧で囲む必要があります。

# Find any users who are active administrations.
User.select().where(
  (User.is_admin == True) &
  (User.is_active == True))

# Find any users who are either administrators or super-users.
User.select().where(
  (User.is_admin == True) |
  (User.is_superuser == True))

# Alternatively, use the boolean values directly. Here we query users who
# are admins and NOT superusers.
User.select().where(User.is_admin & ~User.is_superuser)

# Find any Tweets by users who are not admins (NOT IN).
admins = User.select().where(User.is_admin == True)
non_admin_tweets = Tweet.select().where(Tweet.user.not_in(admins))

# Find any users who are not my friends (strangers).
friends = User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
strangers = User.select().where(User.id.not_in(friends))

警告

クエリ式で Python の in、and、or、is、および not 演算子を使用しようとするかもしれませんが、これらは**機能しません。** in 式の戻り値は常にブール値に強制されます。同様に、and、or、および not はすべて引数をブール値として扱い、オーバーロードできません。

したがって、覚えておいてください

in および not in の代わりに、.in_() および .not_in() を使用します。
and の代わりに & を使用します。
or の代わりに | を使用します。
not の代わりに ~ を使用します。
is None または == None の代わりに、.is_null() を使用します。
True および False と比較するには、== および != を使用するか、式の暗黙的な値を使用できます。
論理演算子を使用する場合は、比較を括弧で囲むことを忘れないでください。

詳細については、「式」セクションを参照してください。

注意

SQLiteを使用したLIKEとILIKE

SQLite の LIKE 操作はデフォルトで大文字と小文字を区別しないため、peewee は大文字と小文字を区別する検索に SQLite の GLOB 操作を使用します。glob 操作では、通常のパーセント記号ではなく、ワイルドカードとしてアスタリスクを使用します。SQLite を使用していて、大文字と小文字を区別する部分文字列一致が必要な場合は、ワイルドカードにアスタリスクを使用することを忘れないでください。

三値論理

SQL が NULL を処理する方法のため、以下を表現するために利用できる特別な操作がいくつかあります。

IS NULL
IS NOT NULL
IN
NOT IN

IS NULL および IN 演算子を否定演算子 (~) で使用することは可能ですが、正しいセマンティクスを得るために、IS NOT NULL および NOT IN を明示的に使用する必要がある場合があります。

IS NULL および IN を使用する最も簡単な方法は、演算子のオーバーロードを使用することです。

# Get all User objects whose last login is NULL.
User.select().where(User.last_login >> None)

# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username << usernames)

演算子のオーバーロードが気に入らない場合は、代わりにフィールドメソッドを呼び出すことができます。

# Get all User objects whose last login is NULL.
User.select().where(User.last_login.is_null(True))

# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.in_(usernames))

上記のクエリを否定するには、単項否定を使用できますが、正しいセマンティクスを得るために、特別な IS NOT および NOT IN 演算子を使用する必要がある場合があります。

# Get all User objects whose last login is *NOT* NULL.
User.select().where(User.last_login.is_null(False))

# Using unary negation instead.
User.select().where(~(User.last_login >> None))

# Get users whose username is *NOT* in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.not_in(usernames))

# Using unary negation instead.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(~(User.username << usernames))

ユーザー定義演算子の追加

オーバーロードする python 演算子がなくなったため、peewee にはいくつかの演算子 (たとえば modulo) が欠落しています。上記の表にない演算子をサポートする必要がある場合は、独自の演算子を簡単に追加できます。

SQLite で modulo のサポートを追加する方法を次に示します。

from peewee import *
from peewee import Expression  # The building block for expressions.

def mod(lhs, rhs):
    # Note: this works with Sqlite, but some drivers may use string-
    # formatting before sending the query to the database, so you may
    # need to use '%%' instead here.
    return Expression(lhs, '%', rhs)

これらのカスタム演算子を使用して、より豊富なクエリを構築できるようになりました。

# Users with even ids.
User.select().where(mod(User.id, 2) == 0)

詳細については、playhouse.postgresql_ext モジュールのソースを確認してください。これには、postgresql の hstore に固有の多数の演算子が含まれています。

式

Peewee は、SQL クエリを構築するためのシンプルで表現力豊かな、pythonic な方法を提供するように設計されています。このセクションでは、一般的な式の種類について簡単に説明します。

式を作成するために構成できるオブジェクトには、主に 2 つのタイプがあります。

Field インスタンス
fn を使用した SQL 集計と関数

ここでは、ユーザー名やその他のフィールドを持つ単純な「User」モデルを想定します。それはこのようなものです。

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

比較では、クエリ演算子を使用します。

# username is equal to 'charlie'
User.username == 'charlie'

# user has logged in less than 5 times
User.login_count < 5

比較は、**ビット単位の** *and* および *or* を使用して組み合わせることができます。演算子の優先順位は Python によって制御され、比較は任意の深さにネストできます。

# User is both and admin and has logged in today
(User.is_admin == True) & (User.last_login >= today)

# User's username is either charlie or charles
(User.username == 'charlie') | (User.username == 'charles')

# User is active and not a superuser.
(User.is_active & ~User.is_superuser)

比較は関数でも使用できます。

# user's username starts with a 'g' or a 'G':
fn.Lower(fn.Substr(User.username, 1, 1)) == 'g'

式は他の式と比較できるため、かなり興味深いことができます。式は算術演算もサポートしています。

# users who entered the incorrect more than half the time and have logged
# in at least 10 times
(User.failed_logins > (User.login_count * .5)) & (User.login_count > 10)

式を使用すると、*アトミック更新* を実行できます。

# when a user logs in we want to increment their login count:
User.update(login_count=User.login_count + 1).where(User.id == user_id)

式はクエリのすべての部分で使用できるため、試してみてください。

行の値

多くのデータベースは、Python のタプルオブジェクトに似た行の値をサポートしています。Peewee では、Tuple を介して式で行の値を使用できます。例:

# If for some reason your schema stores dates in separate columns ("year",
# "month" and "day"), you can use row-values to find all rows that happened
# in a given month:
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()

# Get a list of all the incident types and sources that have occured today.
incidents = (IncidentLog
             .select(IncidentLog.incident_type, IncidentLog.source)
             .where(IncidentLog.timestamp >= datetime.date.today()))

# Find all events that correlate with the type and source of the
# incidents that occured today.
events = (EventLog
          .select()
          .where(Tuple(EventLog.event_type, EventLog.source).in_(incidents))
          .order_by(EventLog.timestamp))

このタイプのクエリを表現する他の方法は、結合を使用するか、サブクエリで結合することです。上記の例は、Tuple がどのように使用されるかのアイデアを示すためだけのものです。

また、行の値を使用して、新しいデータがサブクエリから派生した場合に、テーブル内の複数の列を更新することもできます。例については、こちらを参照してください。

SQL関数

COUNT() や SUM() などの SQL 関数は、fn() ヘルパーを使用して表現できます。

# Get all users and the number of tweets they've authored. Sort the
# results from most tweets -> fewest tweets.
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で始まるすべてのユーザーのリストを取得したいとします。これを行う方法はいくつかありますが、1つの方法として、LOWERやSUBSTRのようなSQL関数を使用することが考えられます。任意のSQL関数を使用するには、特別なfn()オブジェクトを使用してクエリを構築します。

# Select the user's id, username and the first letter of their username, lower-cased
first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
query = User.select(User, first_letter.alias('first_letter'))

# Alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(first_letter == 'a')

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

SQLヘルパー

任意のSQLを単純に渡したい場合があります。これを行うには、特別なSQLクラスを使用します。使用例の1つは、エイリアスを参照する場合です。

# We'll query the user table and annotate it with a count of tweets for
# the given user
query = (User
         .select(User, fn.Count(Tweet.id).alias('ct'))
         .join(Tweet)
         .group_by(User))

# Now we will order by the count, which was aliased to "ct"
query = query.order_by(SQL('ct'))

# You could, of course, also write this as:
query = query.order_by(fn.COUNT(Tweet.id))

peeweeで手作りのSQLステートメントを実行するには、2つの方法があります。

あらゆるタイプのクエリを実行するためのDatabase.execute_sql()
SELECTクエリを実行し、モデルインスタンスを返すためのRawQuery

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

デフォルトでは、peeweeはクエリをパラメータ化するため、ユーザーが渡したパラメータはすべてエスケープされます。このルールの唯一の例外は、生のSQLクエリを作成している場合、または信頼できないデータが含まれている可能性のあるSQLオブジェクトを渡している場合です。これを軽減するには、ユーザー定義のデータがSQLクエリの一部ではなく、クエリパラメータとして渡されるようにしてください。

# Bad! DO NOT DO THIS!
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s' % (user_data,))

# Good. `user_data` will be treated as a parameter to the query.
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)

# Bad! DO NOT DO THIS!
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))

# Good. `user_data` will be treated as a parameter.
query = MyModel.select().where(SQL('Some SQL expression %s', user_data))

注意

MySQLとPostgresqlは、パラメータを示すために'%s'を使用します。一方、SQLiteは'?'を使用します。データベースに適した文字を使用するようにしてください。このパラメータは、Database.paramを確認することでも見つけることができます。