Querying API

Constructing queries

Queries in peewee are constructed one piece at a time.

The “pieces” of a peewee query are generally representative of clauses you might find in a SQL query. Most methods are chainable, so you build your query up one clause at a time. This way, rather complex queries are possible.

Here is a barebones select query:

>>> user_q = User.select() # <-- query is not executed
>>> user_q
<peewee.SelectQuery object at 0x7f6b0810c610>

>>> [u.username for u in user_q] # <-- query is evaluated here
[u'admin', u'staff', u'editor']

We can build up the query by adding some clauses to it:

>>> user_q = user_q.where(User.username << ['admin', 'editor'])
>>> user_q = user_q.order_by(User.username.desc())
>>> [u.username for u in user_q] # <-- query is re-evaluated here
[u'editor', u'admin']

Looking at some simple queries

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)
)

Where clause

All queries except InsertQuery support the where() method. If you are familiar with Django’s ORM, it is analagous to the filter() method.

>>> User.select().where(User.is_staff == True)

Note

User.select() is equivalent to SelectQuery(User).

Joining

You can join on tables related to one another by ForeignKeyField. The join() method acts on the Model that is the current “query context”. This is either:

  • the model the query class was initialized with
  • the model most recently JOINed on

There are three types of joins by default:

  • JOIN_INNER (default)
  • JOIN_LEFT_OUTER
  • JOIN_FULL

Here is an example using JOINs:

>>> User.select().join(Blog).where(User.is_staff == True, Blog.status == LIVE)

The above query grabs all staff users who have a blog that is “LIVE”. This next does the inverse: grabs all the blogs that are live whose author is a staffer:

>>> Blog.select().join(User).where(User.is_staff == True, Blog.status == LIVE)

Another way to write the above query would be to use a subquery:

>>> staff = User.select().where(User.is_staff == true)
>>> Blog.select().where(Blog.status == LIVE, Blog.user << staff)

The above bears a little bit of explanation. First off the SQL generated will not perform any explicit JOIN - it will rather use a subquery in the WHERE clause:

-- translates roughly to --
SELECT t1.* FROM blog AS t1
WHERE (
    t1.status = ? AND
    t1.user_id IN (
        SELECT t2.id FROM user AS t2 WHERE t2.is_staff = ?
    )
)

And here it is using joins:

-- and here it would be if using joins --
SELECT t1.* FROM blog AS t1
INNER JOIN user AS t2
    ON t1.user_id = t2.id
WHERE
    t1.status = ? AND
    t2.is_staff = ?

Column lookups

The other bit that’s unique about the query is that it specifies "user__in". Users familiar with Django will recognize this syntax - lookups other than “=” are signified by a double-underscore followed by the lookup type. The following lookup types are available in peewee:

Lookup Meaning
== 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

Performing advanced queries

As you may have noticed, all the examples up to now have shown queries that combine multiple clauses with “AND”. To create arbitrarily complex queries, simply use python’s bitwise “and” and “or” operators:

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

The WHERE clause will look something like:

WHERE (is_staff = ? OR is_superuser = ?)

In order to negate an expression, use the bitwise “invert” operator:

>>> staff_users = User.select().where(is_staff=True)
>>> Tweet.select().where(
...     ~(Tweet.user << staff_users)
... )

This query generates roughly the following SQL:

SELECT t1.* FROM blog AS t1
WHERE
    NOT t1.user_id IN (
        SELECT t2.id FROM user AS t2 WHERE t2.is_staff = ?
    )

Rather complex lookups are possible:

>>> sq = User.select().where(
...     ((User.is_staff == True) | (User.is_superuser == True)) &
...     (User.join_date >= datetime(2009, 1, 1)
... )

This generates roughly the following SQL:

SELECT * FROM user
WHERE (
    (is_staff = ? OR is_superuser = ?) AND
    (join_date >= ?)
)

Note

If you need more power, check out RawQuery

Comparing against column data

Suppose you have a model that looks like the following:

class WorkerProfiles(Model):
    salary = IntegerField()
    desired = IntegerField()
    tenure = IntegerField()

What if we want to query WorkerProfiles to find all the rows where “salary” is greater than “desired” (maybe you want to find out who may be looking for a raise)?

WorkerProfile.select().where(
    WorkerProfile.salary < WorkerProfile.desired
)

We can also create expressions, like to find employees who might not be getting paid enough based on their tenure:

WorkerProfile.select().where(
    WorkerProfile.salary < (WorkerProfile.tenure * 1000) + 40000
)

Atomic updates

The techniques shown above also work for updating data. Suppose you are counting pageviews in a special table:

PageView.update(count=PageView.count + 1).where(
    PageView.url == request.url
)

The “fn” helper

class fn

A helper class that will convert arbitrary function calls to SQL function calls.

SQL provides a number of helper functions as a part of the language. These functions can be used to calculate counts and sums over rows, perform string manipulations, do complex math, and more. There are a lot of functions.

To express functions in peewee, use the fn object. The way it works is anything to the right of the “dot” operator will be treated as a function. You can pass that function arbitrary parameters which can be other valid expressions.

For example:

Peewee expression Equivalent SQL
fn.Count(Tweet.id).alias('count') Count(t1."id") AS count
fn.Lower(fn.Substr(User.username, 1, 1)) Lower(Substr(t1."username", 1, 1))
fn.Rand().alias('random') Rand() AS random
fn.Stddev(Employee.salary).alias('sdv') Stddev(t1."salary") AS sdv

Functions can be used as any part of a query:

  • select
  • where
  • group_by
  • order_by
  • having
  • update query
  • insert query

Aggregating records

Suppose you have some users and want to get a list of them along with the count of tweets each has made. First I will show you the shortcut:

query = User.select().annotate(Tweet)

This is equivalent to the following:

query = User.select(
    User, fn.Count(Tweet.id).alias('count')
).join(Tweet).group_by(User)

The resulting query will return User objects with all their normal attributes plus an additional attribute ‘count’ which will contain the number of tweets. By default it uses an inner join if the foreign key is not nullable, which means users without tweets won’t appear in the list. To remedy this, manually specify the type of join to include users with 0 tweets:

query = User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)

You can also specify a custom aggregator. In the following query we will annotate the users with the date of their most recent tweet:

query = User.select().annotate(Tweet, fn.Max(Tweet.created_date).alias('latest'))

Conversely, sometimes you want to perform an aggregate query that returns a scalar value, like the “max id”. Queries like this can be executed by using the aggregate() method:

most_recent_tweet = Tweet.select().aggregate(fn.Max(Tweet.created_date))

SQL Functions

Arbitrary SQL functions can be expressed using the fn function.

Selecting users and counts of tweets:

>>> users = User.select(User, fn.Count(Tweet.id).alias('count')).join(Tweet).group_by(User)
>>> for user in users:
...     print user.username, 'posted', user.count, 'tweets'

This functionality can also be used as part of the WHERE or HAVING clauses:

>>> a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
>>> for user in a_users:
...    print user.username

alpha
Alton

Speeding up simple select queries

Simple select queries can get a performance boost (especially when iterating over large result sets) by calling naive(). This method simply patches all attributes directly from the cursor onto the model. For simple queries this should have no noticeable impact. The main difference is when multiple tables are queried, as in the previous example:

# above example
tweets = Tweet.select(Tweet, User).join(User)
for tweet in tweets.order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.user.username)

And here is how you would do the same if using a naive query:

# very similar query to the above -- main difference is we're
# aliasing the blog title to "blog_title"
tweets = Tweet.select(Tweet, User.username).join(User).naive()
for tweet in tweets.order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.username)

Query evaluation

In order to execute a query, it is always necessary to call the execute() method.

To get a better idea of how querying works let’s look at some example queries and their return values:

>>> dq = User.delete().where(User.active == False) # <-- returns a DeleteQuery
>>> dq
<peewee.DeleteQuery object at 0x7fc866ada4d0>
>>> dq.execute() # <-- executes the query and returns number of rows deleted
3

>>> uq = User.update(active=True).where(User.id > 3) # <-- returns an UpdateQuery
>>> uq
<peewee.UpdateQuery object at 0x7fc865beff50>
>>> uq.execute() # <-- executes the query and returns number of rows updated
2

>>> iq = User.insert(username='new user') # <-- returns an InsertQuery
>>> iq
<peewee.InsertQuery object at 0x7fc865beff10>
>>> iq.execute() # <-- executes query and returns the new row's PK
8

>>> sq = User.select().where(User.active == True) # <-- returns a SelectQuery
>>> sq
<peewee.SelectQuery object at 0x7fc865b7a510>
>>> qr = sq.execute() # <-- executes query and returns a QueryResultWrapper
>>> qr
<peewee.QueryResultWrapper object at 0x7fc865b7a6d0>
>>> [u.id for u in qr]
[1, 2, 3, 4, 7, 8]
>>> [u.id for u in qr] # <-- re-iterating over qr does not re-execute query
[1, 2, 3, 4, 7, 8]

>>> [u.id for u in sq] # <-- as a shortcut, you can iterate directly over
>>>                    #     a SelectQuery (which uses a QueryResultWrapper
>>>                    #     behind-the-scenes)
[1, 2, 3, 4, 7, 8]

Note

Iterating over a SelectQuery will cause it to be evaluated, but iterating over it multiple times will not result in the query being executed again.

QueryResultWrapper

As I hope the previous bit showed, Delete, Insert and Update queries are all pretty straightforward. Select queries are a little bit tricky in that they return a special object called a QueryResultWrapper. The sole purpose of this class is to allow the results of a query to be iterated over efficiently. In general it should not need to be dealt with explicitly.

The preferred method of iterating over a result set is to iterate directly over the SelectQuery, allowing it to manage the QueryResultWrapper internally.

SelectQuery

class SelectQuery

By far the most complex of the 4 query classes available in peewee. It supports JOIN operations on other tables, aggregation via GROUP BY and HAVING clauses, ordering via ORDER BY, and can be iterated and sliced to return only a subset of results.

__init__(model, *selection)
Parameters:
  • model – a Model class to perform query on
  • selection – a list of models, fields, functions or expressions

If no query is provided, it will default to all the fields of the given model.

>>> sq = SelectQuery(User, User.id, User.username)
>>> sq = SelectQuery(User,
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User)
where(*q_or_node)
Parameters:q_or_node – a list of expressions (Q or Node objects
Return type:a SelectQuery instance
>>> sq = SelectQuery(User).where(User.username == 'somebody')
>>> sq = SelectQuery(Blog).where(
...     (User.username == 'somebody') |
...     (User.username == 'nobody')
... )

Note

where() calls are chainable

join(model, join_type=None, on=None)
Parameters:
  • model – the model to join on. there must be a ForeignKeyField between the current query context and the model passed in.
  • join_type – allows the type of JOIN used to be specified explicitly, one of JOIN_INNER, JOIN_LEFT_OUTER, JOIN_FULL
  • on – if multiple foreign keys exist between two models, this parameter is the ForeignKeyField to join on.
Return type:

a SelectQuery instance

Generate a JOIN clause from the current query context to the model passed in, and establishes model as the new query context.

>>> sq = SelectQuery(Tweet).join(User)
>>> sq = SelectQuery(User).join(Relationship, on=Relationship.to_user)
group_by(*clauses)
Parameters:clauses – either a list of model classes or field names
Return type:SelectQuery
>>> # get a list of blogs with the count of entries each has
>>> sq = User.select(
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User)
having(*q_or_node)
Parameters:q_or_node – a list of expressions (Q or Node objects
Return type:SelectQuery
>>> sq = User.select(
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User).having(fn.Count(Tweet.id) > 10)
order_by(*clauses)
Parameters:clauses – a list of fields or calls to field.[asc|desc]()
Return type:SelectQuery

example:

>>> User.select().order_by(User.username)
>>> Tweet.select().order_by(Tweet.created_date.desc())
>>> Tweet.select().join(User).order_by(
...     User.username, Tweet.created_date.desc()
... )
paginate(page_num, paginate_by=20)
Parameters:
  • page_num – a 1-based page number to use for paginating results
  • paginate_by – number of results to return per-page
Return type:

SelectQuery

applies a LIMIT and OFFSET to the query.

>>> User.select().order_by(User.username).paginate(3, 20) # <-- get users 41-60
limit(num)
Parameters:num (int) – limit results to num rows
offset(num)
Parameters:num (int) – offset results by num rows
count()
Return type:an integer representing the number of rows in the current query
>>> sq = SelectQuery(Tweet)
>>> sq.count()
45 # <-- number of tweets
>>> sq.where(Tweet.status == DELETED)
>>> sq.count()
3 # <-- number of tweets that are marked as deleted
get()
Return type:Model instance or raises DoesNotExist exception

Get a single row from the database that matches the given query. Raises a <model-class>.DoesNotExist if no rows are returned:

>>> active = User.select().where(User.active == True)
>>> try:
...     user = active.where(User.username == username).get()
... except User.DoesNotExist:
...     user = None

This method is also exposed via the Model api, in which case it accepts arguments that are translated to the where clause:

>>> user = User.get(User.active == True, User.username == username)
exists()
Return type:boolean whether the current query will return any rows. uses an optimized lookup, so use this rather than get().
>>> sq = User.select().where(User.active == True)
>>> if sq.where(User.username==username, User.password==password).exists():
...     authenticated = True
annotate(related_model, aggregation=None)
Parameters:
  • related_model – related Model on which to perform aggregation, must be linked by ForeignKeyField.
  • aggregation – the type of aggregation to use, e.g. fn.Count(Tweet.id).alias('count')
Return type:

SelectQuery

Annotate a query with an aggregation performed on a related model, for example, “get a list of users with the number of tweets for each”:

>>> User.select().annotate(Tweet)

if aggregation is None, it will default to fn.Count(related_model.id).alias('count') but can be anything:

>>> user_latest = User.select().annotate(Tweet, fn.Max(Tweet.created_date).alias('latest'))

Note

If the ForeignKeyField is nullable, then a LEFT OUTER join may need to be used:

>>> User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)
aggregate(aggregation)
Parameters:aggregation – a function specifying what aggregation to perform, for example fn.Max(Tweet.created_date).

Method to look at an aggregate of rows using a given function and return a scalar value, such as the count of all rows or the average value of a particular column.

for_update([for_update=True])
Return type:SelectQuery

indicates that this query should lock rows for update

distinct()
Return type:SelectQuery

indicates that this query should only return distinct rows. results in a SELECT DISTINCT query.

naive()
Return type:SelectQuery

indicates that this query should only attempt to reconstruct a single model instance for every row returned by the cursor. if multiple tables were queried, the columns returned are patched directly onto the single model instance.

Note

this can provide a significant speed improvement when doing simple iteration over a large result set.

switch(model)
Parameters:model – model to switch the query context to.
Return type:a SelectQuery instance

Switches the query context to the given model. Raises an exception if the model has not been selected or joined on previously. The following example selects from blog and joins on both entry and user:

>>> sq = SelectQuery(Blog).join(Entry).switch(Blog).join(User)
filter(*args, **kwargs)
Parameters:
  • args – a list of DQ or Node objects
  • kwargs – a mapping of column + lookup to value, e.g. “age__gt=55”
Return type:

SelectQuery with appropriate WHERE clauses

Provides a django-like syntax for building a query. The key difference between filter() and SelectQuery.where() is that filter() supports traversing joins using django’s “double-underscore” syntax:

>>> sq = Entry.filter(blog__title='Some Blog')

This method is chainable:

>>> base_q = User.filter(active=True)
>>> some_user = base_q.filter(username='charlie')

Note

this method is provided for compatibility with peewee 1.

execute()
Return type:QueryResultWrapper

Executes the query and returns a QueryResultWrapper for iterating over the result set. The results are managed internally by the query and whenever a clause is added that would possibly alter the result set, the query is marked for re-execution.

__iter__()

Executes the query:

>>> for user in User.select().where(User.active == True):
...     print user.username

UpdateQuery

class UpdateQuery

Used for updating rows in the database.

__init__(model, **kwargs)
Parameters:
  • modelModel class on which to perform update
  • kwargs – mapping of field/value pairs containing columns and values to update
>>> uq = UpdateQuery(User, active=False).where(User.registration_expired==True)
>>> uq.execute() # run the query
>>> atomic_update = UpdateQuery(User, message_count=User.message_count + 1).where(User.id == 3)
>>> atomic_update.execute() # run the query
where(*args, **kwargs)

Same as SelectQuery.where()

execute()
Return type:Number of rows updated

Performs the query

DeleteQuery

class DeleteQuery

Deletes rows of the given model.

Note

It will not traverse foreign keys or ensure that constraints are obeyed, so use it with care.

__init__(model)

creates a DeleteQuery instance for the given model:

>>> dq = DeleteQuery(User).where(User.active==False)
where(*args, **kwargs)

Same as SelectQuery.where()

execute()
Return type:Number of rows deleted

Performs the query

InsertQuery

class InsertQuery

Creates a new row for the given model.

__init__(model, **kwargs)

creates an InsertQuery instance for the given model where kwargs is a dictionary of field name to value:

>>> iq = InsertQuery(User, username='admin', password='test', active=True)
>>> iq.execute() # <--- insert new row
execute()
Return type:primary key of the new row

Performs the query

RawQuery

class RawQuery

Allows execution of an arbitrary query and returns instances of the model via a QueryResultsWrapper.

__init__(model, query, *params)

creates a RawQuery instance for the given model which, when executed, will run the given query with the given parameters and return model instances:

>>> rq = RawQuery(User, 'SELECT * FROM users WHERE username = ?', 'admin')
>>> for obj in rq.execute():
...     print obj
<User: admin>
execute()
Return type:a QueryResultWrapper for iterating over the result set. The results are instances of the given model.

Performs the query