Filtering records

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

  1. >>> user = User.get(User.username == 'Charlie')
  2. >>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
  3. ... print(tweet.user.username, '->', tweet.message)
  4. ...
  5. Charlie -> hello world
  6. Charlie -> this is fun
  7.  
  8. >>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
  9. ... print(tweet.message, tweet.created_date)
  10. ...
  11. Really old tweet 2010-01-01 00:00:00

You can also filter across joins:

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

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

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

Note

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

Check out the table of query operations to see whattypes 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 usernamestarts with “a”:

  1. # get users whose username starts with "a"
  2. a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
  3.  
  4. # the ".in_()" method signifies an "IN" query
  5. 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 Examplesdocument, which shows how to implements queries from the PostgreSQL Exerciseswebsite.

Get active users:

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

Get users who are either staff or superusers:

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

Get tweets by user named “charlie”:

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

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

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

Get tweets by staff or superusers using a subquery:

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