You want to make fine grained Django model queries but don't know the Django syntax, you just know SQL keywords and don't want use raw SQL for your queries.
To create SQL
EQUAL queries use Django's
__exact field lookup. To create SQL
NOT EQUAL queries use Django's
exclude() method or
~Q() object. To create SQL
AND queries use two fields or
Q() objects in a Django query. To combine two Django queries use the
| character or the
chain in the
To create SQL
OR queries use two Django
Q() objects with the
| character. To create SQL
IS NOT queries use Django's
__isnull field lookup. To create SQL
IN queries use Django's
__in field lookup. To create SQL
OFFSET queries use Python's slice syntax on query (e.g.
[5:10]). To create
LIKE queries use Django's
__endswith field lookups. To create
ILIKE queries use Django's
__iexact field lookups.
To create SQL
REGEXP queries use Django's
__regex field lookup. To create SQL
GREATER THAN queries use Django's
__gte field lookups. To create SQL
LESSER THAN queries use Django's
__lte field lookups. The create SQL
COUNT queries use Django's
count() method or aggregate
Count() class. The create SQL
SUM queries use Django's aggregate
Sum classes, respetively. The create SQL
STDDEV queries use Django's aggregate
StdDev classes, respetively.
To create SQL date and time queries use Django's
__second field lookups. To create SQL
ORDER queries use Django's
In the previous Django model recipes we illustrated how to query single and multiple records with Django's model methods. However, the matching process was done on exact values. For example, a query for the
Store record with
id=1 or a query for all
Store records with
state="CA". In reality, this exact matching process is far from most real world scenarios that require finer grained Django queries.
In this recipe I'll show you various Django model queries and classify the examples by SQL keywords which is a more commonly known syntax.
| Append |
Sometimes it can be helpful or even necessary to view the actual SQL executed by a Django model query. You can do so by appending
As you can see in the previous snippet, you can output the SQL query to a Python logger or use the 'quick & dirty'
= searches are among the most common Django queries. In fact, in the previous recipes that described single and multiple CRUD operations we used this type of query. There are two syntax variations for equality searches, one is a short-handed version and the other uses the
exact field lookup, listing 1 shows both approaches.
As you can see in listing 1, you can either use the
exact field lookup to explicitly qualify the query or just use the syntax
<field>=<value>. Because exact searches are the most common, Django implies
exact searches by default. So either syntax option works the same.
|Case insenstive equality queries|
You can do case insensitive equality queries with the
!= searches also have two syntax variations presented in listing 2.
As you can see in listing 2, one syntax variation is to use the
exclude method to exclude objects that match a given statement. Another alternative is to use a Django Q object to negate a query. In listing 2 you can see the Q object
Q(state="CA") that matches
state values with
CA, but because the Q object is preceded with
~(tilde symbol) it's a negation pattern (i.e. matches
state values that aren't
Q object syntax produce the same results.
Q objects are mostly used in more complex queries, but in this case it works just like
To create Django queries with
AND statements you can add multiple statements to a query, listing 3 illustrates a couple of examples.
If you're looking for a broader
AND query than the ones in listing 3 (e.g. get
Store objects with state 'CA' and those with state 'AZ'), you might want to look at either the
OR queries (e.g.
Store objects with state 'CA' or state 'AZ') or
IN queries (e.g.
Store objects with the state in list ['CA,'AZ']) examples.
Another case of
AND query that's worth mentioning is combining the results of two queries (i.e. queryset_1_results AND queryset_2_results), listing 4 illustrates how to do this.
The first option in listing 4 uses the
| (pipe) operator to combine two queries, this works but only if both queries use the same model (e.g.
Store). The second option in listing 4 uses the Python
itertools library to achieve the same result, it's the more flexible option because it can combine queries even if they use different models (e.g.
To create Django queries with
OR statements you can use
Q object syntax, listing 5 illustrates a couple of examples.
Listing 5 uses the
| (pipe) operator between
Q objects to indicate
OR, similar to how the
& operator is used to indicate
IS NOT are typically used with
NULL value queries, although depending on the database
IS NOT can also be used for boolean queries. Listing 6 illustrates common scenarios with
IS NOT with the Django model API.
Note in listing 6, the first example attempts a query on Python's
None value, in this case
None gets translated to SQL's
email=NULL. The second and third examples in listing 6 use the
isnull field lookup, to create
IS NULL and
IS NOT NULL queries, respectively.
To make a query that matches a list of values you can use Django's
in field lookup. Listing 7 illustrates the use of the
in field lookup.
OFFSET are often used to reduce the result size of a large query, this can be particularly helpful in scenarios that involve pagination (i.e. presenting results in multiple pages or by multiple clicks) to avoid transferring a large amount of data in a single step. Listing 8 illustrates how to emulate the use of the
As you can see in listing 8, the technique to emulate
OFFSET queries is through Python's slice syntax. In case you've never used Python's slice syntax, the technique is straightforward: The syntax
a[start:end] gets items from start to end-1 of
a, the syntax
a[start:] gets items from start through the rest of
a and the syntax
a[:end] gets items from the beginning of a through end-1.
ILIKE are used to match string patterns, with the former being case-senstive and the latter case-insensitive. Listing 8 illustrates how to accomplish the behavior of
LIKE queries with Django's model syntax.
As you can see in listing 8, Django offers three different keywords to support the various
LIKE wildcard combinations:
% is SQL's wildcard.
Django also supports the SQL
ILIKE keyword, which functions just as
LIKE but is case-insensitve. Listing 9 illustrates how to accomplish the behavior of
ILIKE queries with Django's model syntax.
The examples in listing 9 are very similar to those in listing 8, the only different is Django's syntax is preceded with the letter
i to indicate a case-insensitve
It's worth mentioning the last example in listing 9 is a case-insensitive version of
NOT EQUAL queries. However, because
ILIKE under the hood it's grouped in this section.
Sometimes the patterns supported by the SQL
ILIKE keywords are too basic, in which case you can use the SQL
REGEXP keyword to define a text pattern as a regular expression. Although it would go beyond the scope of this recipe to describe regular expression syntax, regular expressions can define fragmented patterns (e.g. pattern starting with
sa followed by any letters, followed by a number), as well as conditional patterns (e.g. pattern that starts with
Los or ends in
Angeles), among other things.
Django supports the SQL
REGEXP keyword with the
regex field lookup. For example, a pattern to match cities that start with
San could be
Store.objects.filter(city__regex=r'^(Los|San) +'). In addition, Django also offers the
iregex field lookup to create case insensitive regular expression queries.
Note that a recommended practice for defining patterns used with
iregex is to use Python raw string literals. A Python raw string literal is just a string preceded by
r that more conveniently expresses strings that would be modified by escape sequence processing (e.g.
r'\n' is identical to
'\\n'), something that's especially helpful with regular expressions that rely a lot on escape characters.
To fetch records on which a field is greater or lesser than a given number, Django offers the
lte field lookups. Listing 10 illustrates the use of these field lookups in Django.
In scenarios where you only need to get the number of records that match a criteria, it's more efficient to let the database engine make the calculation instead of getting all the data and then making the calculation in Python. SQL offers the
COUNT keyword for just this purpose, which is also supported by Django models.
Django supports the SQL
COUNT keyword through the
count() method and the aggregate
Count class, listing 11 illustrates various examples of both cases.
The first two examples in listing 11 append the
count() method as the last part of a regular Django query to get a total count. The third example uses the aggregate
Count class to get the total count of emails in
Store records. The fourth example prefixes the
Count class with a string to simulate SQL's
AS keyword, so the resulting query uses this string. Note that by default, if no string is specified on the
Count class the resulting query uses
email__count). Finally, the fourth example adds the
distinct=True property to the
Count class to get the total count of distinct emails.
In addition to SQL's
COUNT, Django also supports other SQL keywords associated with mathematical operations that are best done in the database itself. Listing 12 illustrates various examples of these operations available through aggregate classes.
As you can see in the first example of listing 12, it's possible to define multiple aggregate classes in a single query, in this case to the get the average, minimum and maximum
stock values across all
Drink objects. In addition, notice it's also possible to prefix the aggregate class with a string to act as SQL's AS keyword. The second example in listing 12 gets the sum of all
stock values across all
Drink objects. While the last example calculates the variance and standard deviation for all
stock values across all
Although date and time queries in SQL can be done with equality, greater than and lesser than symbols, writing SQL date and time queries can be time consuming due to their special representation. For example, to create an SQL query for all records with a 2015 year timestamp you need to do something like
'WHERE date BETWEEN '2015-01-01' AND '2015-12-31'. As you can see, syntax wise these type of queries can become more complex and error prone if they need to deal with timezones, months and things like leap years, among other things.
To simplify creating date and time queries, Django offers various field lookups. Because Django is timezone aware -- due to the default configuration file parameters
TIME_ZONE = 'UTC' and
USE_TZ = True in
settings.py -- these date and time field lookups automatically adjust input values to the project's timezone, which is a very helpful feature that limits the complexity of creating input dates and times. Listing 13 illustrates the various Django field lookups related to dates and times.
|Django date and time field lookups require |
Because input data for date and time field lookups is converted to the project's time zone before filtering, you must have the
If you don't have the
The first example in listing 13 uses the
range field lookup which takes two Python datetime objects to define a date range for the query. Although
range is the most flexible approach to create date and time queries, there are other field lookup alternatives that offer simpler syntax. The
day field lookups allow you to create queries that match records for a given year, month or day, respectively. In addition, if you look at the middle of listing 13, you'll notice it's also possible to create a query with multiple field lookups to match a combination of year, month and day.
Finally, toward the bottom half of listing 13 you can see the
week_day field lookup can create a query for records that match a given week day. And the
second field lookups can be used to create queries for records that match a given hour, minute or second, respectively.
Django offers the
order_by() method to support SQL's
ORDER keyword which permits the database engine perform the ordering of a query result. The
order_by() method is appended to a query with a list of model fields to define the ordering, a process that's illustrated in listing 14.
The first example in listing 14 defines a query for all
Store objects ordered by
city. By default,
order_by sets the order ascending (i.e. 'A' records first, 'Z' records last). The second example defines a
Store query but with a list of order fields, so the query is first ordered by the first field and then with the second. In addition, the second example illustrates the use of the
- symbol to override the default ascending order, in this case
-name indicates to order the records by
name but in descending order (i.e. 'Z' records first, 'A' records last).