Query operators

    Because I ran out of operators to override, there are some additional query operations available as methods:

    To combine clauses using logical operators, use:

    Here is how you might use some of these query operators:

    Here is how you might combine expressions. Comparisons can be arbitrarily complex.

    Note

    Note that the actual comparisons are wrapped in parentheses. Python’s operator precedence necessitates that comparisons be wrapped in parentheses.

    1. # Find any users who are active administrations.
    2. User.select().where(
    3.   (User.is_admin == True) &
    4.   (User.is_active == True))
    6. # Find any users who are either administrators or super-users.
    7. User.select().where(
    8.   (User.is_admin == True) |
    9.   (User.is_superuser == True))
    11. # Find any Tweets by users who are not admins (NOT IN).
    12. admins = User.select().where(User.is_admin == True)
    13. non_admin_tweets = Tweet.select().where(Tweet.user.not_in(admins))
    15. # Find any users who are not my friends (strangers).
    16. friends = User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
    17. strangers = User.select().where(User.id.not_in(friends))

    Warning

    Although you may be tempted to use python’s in, and, or and not operators in your query expressions, these will not work. The return value of an in expression is always coerced to a boolean value. Similarly, and, or and not all treat their arguments as boolean values and cannot be overloaded.

    So just remember:

    • Use .in_() and .not_in() instead of in and not in
    • Use & instead of and
    • Use | instead of or
    • Use instead of not
    • Use .is_null() instead of is None or == None.
    • Don’t forget to wrap your comparisons in parentheses when using logical operators.

    For more examples, see the Expressions section.

    Note

    LIKE and ILIKE with SQLite

    Because SQLite’s LIKE operation is case-insensitive by default, peewee will use the SQLite GLOB operation for case-sensitive searches. The glob operation uses asterisks for wildcards as opposed to the usual percent-sign. If you are using SQLite and want case-sensitive partial string matching, remember to use asterisks for the wildcard.

    Because of the way SQL handles NULL, there are some special operations available for expressing:

    • IS NULL
    • IS NOT NULL
    • IN
    • NOT IN

    The simplest way to use IS NULL and IN is to use the operator overloads:

    1. # Get all User objects whose last login is NULL.
    2. User.select().where(User.last_login >> None)
    4. # Get users whose username is in the given list.
    5. usernames = ['charlie', 'huey', 'mickey']
    6. User.select().where(User.username << usernames)

    If you don’t like operator overloads, you can call the Field methods instead:

    1. # Get all User objects whose last login is NULL.
    2. User.select().where(User.last_login.is_null(True))
    4. # Get users whose username is in the given list.
    5. usernames = ['charlie', 'huey', 'mickey']
    6. User.select().where(User.username.in_(usernames))

    To negate the above queries, you can use unary negation, but for the correct semantics you may need to use the special IS NOT and NOT IN operators:

    1. # Get all User objects whose last login is *NOT* NULL.
    2. User.select().where(User.last_login.is_null(False))
    3. # Using unary negation instead.
    4. User.select().where(~(User.last_login >> None))
    6. # Get users whose username is *NOT* in the given list.
    7. usernames = ['charlie', 'huey', 'mickey']
    8. User.select().where(User.username.not_in(usernames))
    10. # Using unary negation instead.
    11. usernames = ['charlie', 'huey', 'mickey']
    12. User.select().where(~(User.username << usernames))

    Adding user-defined operators

    Because I ran out of python operators to overload, there are some missing operators in peewee, for instance modulo. If you find that you need to support an operator that is not in the table above, it is very easy to add your own.

    Here is how you might add support for modulo in SQLite:

    1. from peewee import *
    2. from peewee import Expression  # The building block for expressions.
    4. def mod(lhs, rhs):
    5.     # Note: this works with Sqlite, but some drivers may use string-
    6.     # formatting before sending the query to the database, so you may
    7.     # need to use '%%' instead here.
    8.     return Expression(lhs, '%', rhs)

    Now you can use these custom operators to build richer queries:

    For more examples check out the source to the playhouse.postgresql_ext module, as it contains numerous operators specific to postgresql’s hstore.

    Peewee is designed to provide a simple, expressive, and pythonic way of constructing SQL queries. This section will provide a quick overview of some common types of expressions.

    There are two primary types of objects that can be composed to create expressions:

    • Field instances
    • SQL aggregations and functions using

    We will assume a simple “User” model with fields for username and other things. It looks like this:

    1. class User(Model):
    2.     username = CharField()
    3.     is_admin = BooleanField()
    4.     is_active = BooleanField()
    5.     last_login = DateTimeField()
    6.     login_count = IntegerField()
    7.     failed_logins = IntegerField()

    Comparisons use the Query operators:

    1. # username is equal to 'charlie'
    2. User.username == 'charlie'
    4. # user has logged in less than 5 times
    5. User.login_count < 5

    Comparisons can be combined using bitwise and and or. Operator precedence is controlled by python and comparisons can be nested to an arbitrary depth:

    1. # User is both and admin and has logged in today
    2. (User.is_admin == True) & (User.last_login >= today)
    5. (User.username == 'charlie') | (User.username == 'charles')

    Comparisons can be used with functions as well:

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

    We can do some fairly interesting things, as expressions can be compared against other expressions. Expressions also support arithmetic operations:

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

    Expressions can be used in all parts of a query, so experiment!

    Many databases support , which are similar to Python tuple objects. In Peewee, it is possible to use row-values in expressions via Tuple. For example,

    1. # If for some reason your schema stores dates in separate columns ("year",
    2. # "month" and "day"), you can use row-values to find all rows that happened
    3. # in a given month:
    4. Tuple(Event.year, Event.month) == (2019, 1)

    The more common use for row-values is to compare against multiple columns from a subquery in a single expression. There are other ways to express these types of queries, but row-values may offer a concise and readable approach.

    For example, assume we have a table “EventLog” which contains an event type, an event source, and some metadata. We also have an “IncidentLog”, which has incident type, incident source, and metadata columns. We can use row-values to correlate incidents with certain events:

    1. class EventLog(Model):
    2.     event_type = TextField()
    3.     source = TextField()
    4.     timestamp = TimestampField()
    6. class IncidentLog(Model):
    7.     incident_type = TextField()
    8.     source = TextField()
    9.     traceback = TextField()
    10.     timestamp = TimestampField()
    12. # Get a list of all the incident types and sources that have occured today.
    13. incidents = (IncidentLog
    14.              .select(IncidentLog.incident_type, IncidentLog.source)
    15.              .where(IncidentLog.timestamp >= datetime.date.today()))
    17. # Find all events that correlate with the type and source of the
    18. # incidents that occured today.
    19. events = (EventLog
    20.           .select()
    21.           .where(Tuple(EventLog.event_type, EventLog.source).in_(incidents))
    22.           .order_by(EventLog.timestamp))

    Other ways to express this type of query would be to use a or to join on a subquery. The above example is there just to give you and idea how might be used.

    You can also use row-values to update multiple columns in a table, when the new data is derived from a subquery. For an example, see here.

    SQL Functions

    SQL functions, like COUNT() or SUM(), can be expressed using the fn() helper:

    1. # Get all users and the number of tweets they've authored. Sort the
    2. # results from most tweets -> fewest tweets.
    3. query = (User
    4.          .select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
    5.          .join(Tweet, JOIN.LEFT_OUTER)
    6.          .group_by(User)
    7.          .order_by(fn.COUNT(Tweet.id).desc()))
    8. for user in query:
    9.     print('%s -- %s tweets' % (user.username, user.tweet_count))

    The fn helper exposes any SQL function as if it were a method. The parameters can be fields, values, subqueries, or even nested functions.

    Nesting function calls

    Suppose you need to want to get a list of all users whose username begins with a. There are a couple ways to do this, but one method might be to use some SQL functions like LOWER and SUBSTR. To use arbitrary SQL functions, use the special fn() object to construct queries:

    1. # Select the user's id, username and the first letter of their username, lower-cased
    2. first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
    3. query = User.select(User, first_letter.alias('first_letter'))
    5. # Alternatively we could select only users whose username begins with 'a'
    6. a_users = User.select().where(first_letter == 'a')
    8. >>> for user in a_users:
    9. ...    print(user.username)

    There are times when you may want to simply pass in some arbitrary sql. You can do this using the special class. One use-case is when referencing an alias:

    1. # We'll query the user table and annotate it with a count of tweets for
    2. # the given user
    3. query = (User
    4.          .select(User, fn.Count(Tweet.id).alias('ct'))
    5.          .join(Tweet)
    6.          .group_by(User))
    8. # Now we will order by the count, which was aliased to "ct"
    9. query = query.order_by(SQL('ct'))
    11. # You could, of course, also write this as:
    12. query = query.order_by(fn.COUNT(Tweet.id))

    There are two ways to execute hand-crafted SQL statements with peewee:

    1. RawQuery for executing SELECT queries and returning model instances.

    Security and SQL Injection

    By default peewee will parameterize queries, so any parameters passed in by the user will be escaped. The only exception to this rule is if you are writing a raw SQL query or are passing in a SQL object which may contain untrusted data. To mitigate this, ensure that any user-defined data is passed in as a query parameter and not part of the actual SQL query:

    Note

    MySQL and Postgresql use '%s' to denote parameters. SQLite, on the other hand, uses '?'. Be sure to use the character appropriate to your database. You can also find this parameter by checking Database.param.