Filtering records

You can filter for particular records using normal python operators. Peewee supports a wide variety of query operators.

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
...     print(tweet.user.username, '->', tweet.message)
...
Charlie -> hello world
Charlie -> this is fun
>>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
...     print(tweet.message, tweet.created_date)
...
Really old tweet 2010-01-01 00:00:00

You can also filter across joins:

>>> for tweet in Tweet.select().join(User).where(User.username == 'Charlie'):
...     print(tweet.message)
hello world
this is fun
look at this picture of my food

If you want to express a complex query, use parentheses and python’s bitwise or and and operators:

>>> Tweet.select().join(User).where(
...     (User.username == 'Charlie') |
...     (User.username == 'Peewee Herman'))

Note

Note that Peewee uses bitwise operators (& and |) rather than logical operators (and and or). The reason for this is that Python coerces the return value of logical operations to a boolean value. This is also the reason why “IN” queries must be expressed using .in_() rather than the in operator.

Check out the table of query operations to see what types of queries are possible.

Note

A lot of fun things can go in the where clause of a query, such as:

• A field expression, e.g. User.username == 'Charlie'
• A function expression, e.g. fn.Lower(fn.Substr(User.username, 1, 1)) == 'a'
• A comparison of one column to another, e.g. Employee.salary < (Employee.tenure * 1000) + 40000

You can also nest queries, for example tweets by users whose username starts with “a”:

# get users whose username starts with "a"
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
# the ".in_()" method signifies an "IN" query
a_user_tweets = Tweet.select().where(Tweet.user.in_(a_users))

More query examples

Note

For a wide range of example queries, see the Query Examples document, which shows how to implements queries from the PostgreSQL Exercises website.

Get active users:

User.select().where(User.active == True)

Get users who are either staff or superusers:

User.select().where(
    (User.is_staff == True) | (User.is_superuser == True))

Get tweets by user named “charlie”:

Tweet.select().join(User).where(User.username == 'charlie')

Get tweets by staff or superusers (assumes FK relationship):

Tweet.select().join(User).where(
    (User.is_staff == True) | (User.is_superuser == True))

Get tweets by staff or superusers using a subquery:

staff_super = User.select(User.id).where(
    (User.is_staff == True) | (User.is_superuser == True))
Tweet.select().where(Tweet.user.in_(staff_super))