Django model queries classified by SQL keywords.

Problem

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.

Solution

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 itertools package.

To create SQL OR queries use two Django Q() objects with the | character. To create SQL IS or IS NOT queries use Django's __isnull field lookup. To create SQL IN queries use Django's __in field lookup. To create SQL LIMIT and OFFSET queries use Python's slice syntax on query (e.g.[:5], [5:10]). To create LIKE queries use Django's __contains, __startswith and __endswith field lookups. To create ILIKE queries use Django's __icontains, __istartswith, __iendswith and __iexact field lookups.

To create SQL REGEXP queries use Django's __regex field lookup. To create SQL >/GREATER THAN queries use Django's __gt and __gte field lookups. To create SQL </LESSER THAN queries use Django's __lt and __lte field lookups. The create SQL COUNT queries use Django's count() method or aggregate Count() class. The create SQL MAX, MIN and SUM queries use Django's aggregate Max(), Min and Sum classes, respetively. The create SQL AVG, VARIANCE and STDDEV queries use Django's aggregate Avg(), Variance and StdDev classes, respetively.

To create SQL date and time queries use Django's __range, __year, __month, __day, __week_day, __hour, __minute and __second field lookups. To create SQL ORDER queries use Django's order_by method.

How it works

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.

Note Append .query to view the actual SQL

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 .query to a query, as illustrated in the following listing:.


from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink

import logging
stdlogger = logging.getLogger(__name__)

# Get the Store object with id=1
tgt_store = Store.objects.filter(id__exact=1)
stdlogger.debug("Query %s" % str(drink_target.query))

# Get the Drink objects with name="Espresso"
drink_target = Drink.objects.filter(name="Espresso")
print str(drink_target.query)

As you can see in the previous snippet, you can output the SQL query to a Python logger or use the 'quick & dirty' print statement. Note that .query only works with queries that output QuerySet's (i.e. it doesn't work with queries with the get() method). Take a look at the recipe Set up logging for a Django project on how to set up a Python logger.

=/EQUAL and !=/NOT EQUAL queries

Equality or = 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.

Listing 1 - Django equality = or EQUAL query

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink

# Get the Store object with id=1
tgt_store = Store.objects.get(id__exact=1)

# Get the Store object with id=1 (Short-handed version)
tgt_store_short = Store.objects.get(id=1)

# Get the Drink objects with name="Cappuccino"
tgt_drink = Drink.objects.filter(name__exact="Cappuccino")

# Get the Drink objects with name="Cappuccino" (Short-handed version)
tgt_drink_short = Drink.objects.filter(name="Cappuccino")

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.

Note Case insenstive equality queries

You can do case insensitive equality queries with the iexact field lookup (e.g. match 'IF','if','If' or 'iF'). See LIKE and ILIKE queries section for details.

Inequality or != searches also have two syntax variations presented in listing 2.

Listing 2 - Django inequality != or NOT EQUAL query

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink
from django.db.models import Q

# Get the Store objects that don't have state 'CA'
tgt_store_non_CA = Store.objects.exclude(state='CA')

# Get the Store objects that don't have state 'CA', using Q
tgt_store_non_CA_with_Q = Store.objects.filter(~Q(state="CA"))

# Get the Drink objects that don't have name="Cappuccino"
tgt_drink_non_cappuccino = Drink.objects.exclude(name="Cappuccino")

# Get the Drink objects that don't have name="Cappuccino", using Q
tgt_drink_short = Drink.objects.filter(~Q(name="Cappuccino"))

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

Both the exclude and Q object syntax produce the same results. Q objects are mostly used in more complex queries, but in this case it works just like exclude.

AND queries

To create Django queries with AND statements you can add multiple statements to a query, listing 3 illustrates a couple of examples.

Listing 3 - Django AND query

from coffeehouse.stores.models import Store
from django.db.models import Q

# Get the Store objects that have state 'CA' AND city 'San Diego'
tgt_store_CA_and_SD = Store.objects.filter(state='CA',city='San Diego')

# Get the Store objects that have state 'CA' AND city not 'San Diego'
tgt_store_CA_and_no_SD = Store.objects.filter(Q(state='CA') & ~Q(city='San Diego'))

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.

Listing 4 - Combine two Django queries


# Based on the queries from listing 3

two_queries_combo = tgt_store_CA_and_SD | tgt_store_CA_and_no_SD

from itertools import chain
two_queries_combo_w_chain = list(chain(tgt_store_CA_and_SD, tgt_store_CA_and_no_SD))

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.Drink and Food).

OR queries

To create Django queries with OR statements you can use Q object syntax, listing 5 illustrates a couple of examples.

Listing 5 - Django OR query

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink
from django.db.models import Q

# Get the Store objects that have state 'CA' OR state='AZ'
tgt_store_CA_and_AZ = Store.objects.filter(Q(state='CA') | Q(state='AZ'))

# Get the Drink objects with name "Cappuccino" or "Latte"
tgt_drinks = Drink.objects.filter(Q(name="Cappuccino") | Q(name='Latte'))

Listing 5 uses the | (pipe) operator between Q objects to indicate OR, similar to how the & operator is used to indicate AND.

IS and IS NOT queries

IS and IS NOT are typically used with NULL value queries, although depending on the database IS and IS NOT can also be used for boolean queries. Listing 6 illustrates common scenarios with IS and IS NOT with the Django model API.

Listing 6 - Django IS and IS NOT queries

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink
from django.db.models import Q

# Get the Store objects that have email NULL
tgt_stores = Store.objects.filter(email=None)

# Get the Store objects that have email NULL
tgt_stores_with_field_lookup = Store.objects.filter(email__isnull=True)

# Get the Store objects that have email NOT NULL
tgt_stores_with_field_lookup_not_null = Store.objects.filter(email__isnull=False)

Note in listing 6, the first example attempts a query on Python's None value, in this case None gets translated to SQL's NULL (i.e. 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.

IN queries

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.

Listing 7 - Django IN queries

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink
from django.db.models import Q

# Get the Store objects that have state 'CA' OR state='AZ'
tgt_store_CA_and_AZ = Store.objects.filter(state__in=['CA','AZ'])

# Get the Drink objects with id 1,2 or 3
tgt_drinks = Drink.objects.filter(id__in=[1,2,3])

LIMIT and OFFSET queries

Queries with LIMIT and 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 LIMIT and OFFSET queries.

Listing 8 - Django LIMIT and OFFSET queries

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink

# Get the first five (LIMIT=5) Store objects that have state 'CA'
tgt_store_first_five = Store.objects.filter(state='CA')[:5]

# Get the second five (OFFSET=5,LIMIT=5) Store objects (after the first 5) that have state 'CA'
tgt_store_first_five = Store.objects.filter(state='CA')[5:10]

# Get the first (LIMIT=1) Drink object
tgt_drinks_first = Drink.objects.all()[0]

As you can see in listing 8, the technique to emulate LIMIT and 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.

LIKE and ILIKE queries

Queries with LIKE and 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.

Listing 8 - Django LIKE queries

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink

# Get the Store objects that contain a 'D' anyhere in state (LIKE '%D%')
tgt_stores_with_D = Store.objects.filter(state__contains='D')

# Get the Store objects that start with 'San' in city (LIKE 'San%')
tgt_stores_san = Store.objects.filter(city__startswith='San')

# Get the Drink objects that end with 'cino' in name (LIKE '%cino')
tgt_drinks_cino = Drink.objects.filter(name__endswith='cino')

As you can see in listing 8, Django offers three different keywords to support the various LIKE wildcard combinations: '%PATTERN%', 'PATTERN%' and '%PATTERN', where % 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.

Listing 9 - Django ILIKE queries

from coffeehouse.stores.models import Store
from coffeehouse.drinks.models import Drink

# Get the Store objects that contain a 'D' in state anyhere case insensitive (ILIKE '%D%')
tgt_stores_with_D = Store.objects.filter(state__icontains='D')

# Get the Store objects that start with 'San' in city case insensitive (ILIKE 'San%')
tgt_stores_san = Store.objects.filter(city__istartswith='San')

# Get the Drink objects that end with 'cino' in name case insensitive (ILIKE '%cino')
tgt_drinks_cino = Drink.objects.filter(name__iendswith='cino')

# Get the Store objects that have state 'CA' case insensitive (ILIKE 'CA')
tgt_stores_ca = Store.objects.filter(state__iexact='CA')

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 ILIKE query.

It's worth mentioning the last example in listing 9 is a case-insensitive version of =/EQUAL and !=/NOT EQUAL queries. However, because iexact uses ILIKE under the hood it's grouped in this section.

REGEXP queries

Sometimes the patterns supported by the SQL LIKE & 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 Los or 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 regex or 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.

>/GREATER THAN and </LESS THAN queries

To fetch records on which a field is greater or lesser than a given number, Django offers the gt, gte, lt and lte field lookups. Listing 10 illustrates the use of these field lookups in Django.

Listing 10 - Django GREATER THAN and LESSER THAN queries

from coffeehouse.drinks.models import Drink
from coffeehouse.stores.models import Store

# Get the Drink objects with stock > 5
tgt_drinks_gt_5 = Drink.objects.filter(stock__gt=5)

# Get the Drink objects with stock > or equal 5
tgt_drinks_gte_5 = Drink.objects.filter(stock__gte=5)

# Get the Drink objects with stock < 2
tgt_drinks_lt_2 = Drink.objects.filter(stock__lt=2)

# Get the Drink objects with stock < or equal 2
tgt_drinks_lte_2 = Drink.objects.filter(stock__lte=2)

COUNT

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.

Listing 11 - Django COUNT queries

from coffeehouse.drinks.models import Drink
from coffeehouse.drinks.models import Store
from django.db.models import Count

# Get the number of stores (COUNT(*))
tgt_stores_count = Store.objects.all().count()

# Get the number of stores that have city 'San Diego' (COUNT(*))
tgt_stores_san_diego_count = Store.objects.filter(city='San Diego').count()

# Get the number of emails, NULL values are not counted (COUNT(email))
tgt_emails_count = Store.objects.aggregate(Count('email'))

# Get the number of emails, NULL values are not counted (COUNT(email) AS "coffeehouse_store_emails_count")
tgt_emails_count = Store.objects.aggregate(coffeehouse_store_emails_count=Count('email'))

# Get the number of distinct emails, NULL values are not counted (COUNT(DISTINCT email))
tgt_different_emails_count = Store.objects.aggregate(Count('email',distinct=True))

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 <field>__count (e.g.email__count). Finally, the fourth example adds the distinct=True property to the Count class to get the total count of distinct emails.

MAX, MIN,SUM, AVG, VARIANCE and STDDEV queries.

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.

Listing 12 - Django MAX, MIN,SUM, AVG, VARIANCE and STDDEV queries

from coffeehouse.drinks.models import Drink
from django.db.models import Avg, Max, Min
from django.db.models import Sum
from django.db.models import Variance, StdDev

# Get the average, maximum and minimum number of stock for all drinks
tgt_drink_avg_max_min_stock = Drink.objects.aggregate(average_drink_stock=Avg('stock'), Max('stock'), Min('stock'))

# Get the total stock for all drinks 
tgt_drink_all_stock = Drink.objects.aggregate(all_drink_stock=Sum('stock'))

# Get the variance and standard deviation for all drinks
# NOTE: Variance & StdDev return the population variance & standard deviation, respectively.
#       But it's also possible to return sample variance & standard deviation, using the sample=True argument
tgt_drink_statistics = Drink.objects.aggregate(Variance('stock'), standard_deviation_stock=StdDev('stock'))

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 Drink objects.

Date and time queries

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.

Listing 13 - Django date and time queries

from coffeehouse.drinks.models import Order
import datetime

# Define custom dates
start_date = datetime.date(2015, 5, 10)
end_date = datetime.date(2005, 5, 21)

# Get the Order objects from custom dates, starting May 10 2015 to May 21 2015
tgt_orders_custom_dates = Entry.objects.filter(pub_date__range=(start_date, end_date))

# Get the Order objects with year 2015
tgt_orders_2015 = Drink.objects.filter(date__year=2015)

# Get the Order objects with month January, where values is 1 through 12 (1=January, 12=December).
tgt_orders_Jan = Drink.objects.filter(date__month=1)

# Get the Order objects with day 15, where values are 1 through 31.
tgt_orders_dayone = Drink.objects.filter(date__day=1)

# Get the Order objects from January 1 2015
tgt_orders_Jan_1_2015 = Drink.objects.filter(date__year=2015,date__month=1,date__day=1)

# Get the Order objects with that fall on Monday, where values are 1 through 7 (1=Sunday, 7=Saturday).
tgt_orders_saturday = Drink.objects.filter(date__week_day=2)

# Get the Order objects placed at 10am, where values are 0 to 23 (0=12am, 23=11pm).
tgt_orders_10am = Drink.objects.filter(date__hour=10)

# Get the Order objects placed at the top of the hour, where values are 0 to 59.
tgt_orders_tophour = Drink.objects.filter(date__minute=0)

# Get the Order objects placed at the 30 seconds of every minute, where values are 0 to 59.
tgt_orders_exactminute = Drink.objects.filter(date__second=0)

Note Django date and time field lookups require pytz package

Because input data for date and time field lookups is converted to the project's time zone before filtering, you must have the pytz package installed (e.g. pip install pytz).

If you don't have the pytz package when attempting to use one of these field lookups, Django throws the error ImproperlyConfigured: This query requires pytz, but it isn't installed..

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 year, month and 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 hour, minute and second field lookups can be used to create queries for records that match a given hour, minute or second, respectively.

ORDER queries

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.

Listing 14 - Django ORDER queries

from coffeehouse.drinks.models import Drink

# Get the number of stores and order by city (ORDER BY city)
tgt_stores_count = Store.objects.all().order_by('city')

# Get the number of stores and order by name descending, email ascending (ORDER BY name DESC, email ASC
tgt_stores_san_diego_count = Store.objects.filter(city='San Diego').order_by('-name','email')

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