Query operators

The following types of comparisons are supported by peewee:

ComparisonMeaning
==x equals y
<x is less than y
<=x is less than or equal to y
>x is greater than y
>=x is greater than or equal to y
!=x is not equal to y
<<x IN y, where y is a list or query
>>x IS y, where y is None/NULL
%x LIKE y where y may contain wildcards
**x ILIKE y where y may contain wildcards
^x XOR y
~Unary negation (e.g., NOT x)

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

MethodMeaning
.in_(value)IN lookup (identical to <<).
.not_in(value)NOT IN lookup.
.is_null(is_null)IS NULL or IS NOT NULL. Accepts boolean param.
.contains(substr)Wild-card search for substring.
.startswith(prefix)Search for values beginning with prefix.
.endswith(suffix)Search for values ending with suffix.
.between(low, high)Search for values between low and high.
.regexp(exp)Regular expression match (case-sensitive).
.iregexp(exp)Regular expression match (case-insensitive).
.bin_and(value)Binary AND.
.bin_or(value)Binary OR.
.concat(other)Concatenate two strings or objects using ||.
.distinct()Mark column for DISTINCT selection.
.collate(collation)Specify column with the given collation.
.cast(type)Cast the value of the column to the given type.

To combine clauses using logical operators, use:

OperatorMeaningExample
&AND(User.is_active == True) & (User.is_admin == True)
| (pipe)OR(User.is_admin) | (User.is_superuser)
~NOT (unary negation)~(User.username.contains(‘admin’))

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

  1. # Find the user whose username is "charlie".
  2. User.select().where(User.username == 'charlie')
  4. # Find the users whose username is in [charlie, huey, mickey]
  5. User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
  7. Employee.select().where(Employee.salary.between(50000, 60000))
  9. Employee.select().where(Employee.name.startswith('C'))
  11. Blog.select().where(Blog.title.contains(search_string))

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.