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:
- 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”:
- Get tweets by staff or superusers (assumes FK relationship):
Tweet.select().join(User).where( (User.is_staff==True) | (User.is_superuser==True) )
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)
User.select() is equivalent to SelectQuery(User).
- 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)
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 = ?
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:
|==||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 >= ?) )
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 )
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.
|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:
- update query
- insert query
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))
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)
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]
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.
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.
- 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)¶
- 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)
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') ... )
where() calls are chainable
- join(model, join_type=None, on=None)¶
- 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.
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)
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)
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)
Parameters: clauses – a list of fields or calls to field.[asc|desc]() Return type: SelectQuery
>>> 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)¶
- page_num – a 1-based page number to use for paginating results
- paginate_by – number of results to return per-page
applies a LIMIT and OFFSET to the query.
>>> User.select().order_by(User.username).paginate(3, 20) # <-- get users 41-60
Parameters: num (int) – limit results to num rows
Parameters: num (int) – offset results by num rows
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
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)
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: Return type:
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”:
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'))
If the ForeignKeyField is nullable, then a LEFT OUTER join may need to be used:
>>> User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)
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.
Return type: SelectQuery
indicates that this query should lock rows for update
Return type: SelectQuery
indicates that this query should only return distinct rows. results in a SELECT DISTINCT query.
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.
this can provide a significant speed improvement when doing simple iteration over a large result set.
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)¶
- args – a list of DQ or Node objects
- kwargs – a mapping of column + lookup to value, e.g. “age__gt=55”
SelectQuery with appropriate WHERE clauses
>>> sq = Entry.filter(blog__title='Some Blog')
This method is chainable:
>>> base_q = User.filter(active=True) >>> some_user = base_q.filter(username='charlie')
this method is provided for compatibility with peewee 1.
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.
Executes the query:
>>> for user in User.select().where(User.active == True): ... print user.username
- class UpdateQuery¶
Used for updating rows in the database.
- __init__(model, **kwargs)¶
- model – Model 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
Return type: Number of rows updated
Performs the query
- class DeleteQuery¶
Deletes rows of the given model.
It will not traverse foreign keys or ensure that constraints are obeyed, so use it with care.
creates a DeleteQuery instance for the given model:
>>> dq = DeleteQuery(User).where(User.active==False)
Return type: Number of rows deleted
Performs the query
- 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
Return type: primary key of the new row
Performs the query
- 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>
Return type: a QueryResultWrapper for iterating over the result set. The results are instances of the given model.
Performs the query