Working With Data

Django’s QuerySet API provides a comprehensive array of methods and functions for working with data. In this section of the chapter, we will look at the common QuerySet methods, field lookups and aggregate functions, and how to build more complex queries with query expressions and Q() objects.

Methods That Return QuerySets

Table 9.1 lists all the built-in model methods that return QuerySets.

Table 9-1: Model methods that return QuerySets.

Let’s use the Django interactive shell to explore a few examples of the more common QuerySet methods not already covered in the book.

Modify the Filters To Suit Your Data

The examples in this chapter use data from my database. You will need to modify filters and search parameters to suit the data in your database. Date formats are also specific to your locale, so you may have to swap day and month parameters.

The data returned in the examples are illustrative. If you’re paying attention, you will see that the answers come from different datasets. This is because I have worked on many versions of this chapter over time. As your data will be different anyway, I have not changed all the examples to reflect a single dataset.

exclude()

exclude() will return a QuerySet of objects that don’t match the given lookup parameters, for example:

>>> from events.models import Venue
>>> Venue.objects.exclude(name="South Stadium")
<QuerySet [<Venue: West Park>, <Venue: North Stadium>, <Venue: East Park>]>

Using more than one lookup parameter will use an SQL AND operator under the hood:

>>> from events.models import Event
>>> from datetime import datetime, timezone
>>> venue1 = Venue.objects.get(name="East Park")
>>> Event.objects.exclude(venue=venue1,event_date=datetime(2020,23,5,tzinfo=timezone.utc))
<QuerySet [<Event: Test Event>, <Event: Club Presentation - Juniors>, <Event: Club Presentation - Seniors>, <Event: Gala Day>]>

The extra step in this example is because Venue is a foreign key to the Event model, so we first have to retrieve a Venue object.

annotate()

Annotations can be simple values, a field reference or an aggregate expression. For example, let’s use Django’s Count aggregate function to annotate our Event model with a total of all users attending each event:

>>> from events.models import Event
>>> from django.db.models import Count
>>> qry = Event.objects.annotate(total_attendees=Count('attendees'))
>>> for event in qry:
...     print(event.name, event.total_attendees) 
... 
Test Event 0
Gala Day 2
Club Presentation - Juniors 5
Club Presentation - Seniors 3
>>>

order_by() and reverse()

order_by() changes the default ordering of the QuerySet. Function parameters are the model fields to use to order the QuerySet. Ordering can be single level:

>>> from events.models import Event
>>> Event.objects.all().order_by('name')
<QuerySet [<Event: Club Presentation - Juniors>, <Event: Club Presentation - Seniors>, <Event: Gala Day>, <Event: Test Event>]>

Or ordering can be multi-level. In the following example, the events are first ordered by event date and then by event name:

>>> Event.objects.all().order_by('event_date','name')  
<QuerySet [<Event: Club Presentation - Juniors>, <Event: Club Presentation - Seniors>, <Event: Gala Day>, <Event: Test Event>]>

By default, QuerySet fields are ordered in ascending order. To sort in descending order, use the - (minus) sign:

>>> Event.objects.all().order_by('-name') 
<QuerySet [<Event: Test Event>, <Event: Gala Day>, <Event: Club Presentation - Seniors>, <Event: Club Presentation - Juniors>]>

reverse() reverses the default ordering of the QuerySet:

>>> Event.objects.all().reverse()                     
<QuerySet [<Event: Test Event>, <Event: Club Presentation - Juniors>, <Event: Club Presentation - Seniors>, <Event: Gala Day>]>

A model must have default ordering (by setting the ordering option of the models Meta class) for reverse() to be useful. If the model is unordered, the sort order of the returned QuerySet will be meaningless.

Also, note both order_by() and reverse() are not free operations—they come at a time cost to your database and should be used sparingly on large datasets.

values() and values_list()

values() returns Python dictionaries, instead of a QuerySet object:

>>> Event.objects.values()       
<QuerySet [{'id': 1, 'name': "Senior's Presentation Night", 'event_date': datetime.datetime(2020, 5, 30, 18, 0, tzinfo=<UTC>), 'venue_id': 2, 'manager_id': 1, 'description': 'Preso night'}, {'id': 2, 'name': "U15's Gala Day", 'event_date': datetime.datetime(2020, 5, 31, 12, 0, tzinfo=<UTC>), 'venue_id': 3, 'manager_id': 1, 'description': "let's go!"}, {'id': 3, 'name': 'Test Event', 'event_date': datetime.datetime(2020, 5, 23, 0, 28, 59, tzinfo=<UTC>), 'venue_id': 3, 'manager_id': None, 'description': ''}]>

You can also specify which fields you want returned:

>>> Event.objects.values('name','description') 
<QuerySet [{'name': "Senior's Presentation Night", 'description': 'Preso night'}, {'name': "U15's Gala Day", 'description': "let's go!"}, {'name': 'Test Event', 'description': ''}]>

values_list() is the same as values(), except it returns tuples:

>>> Event.objects.values_list() 
<QuerySet [(1, "Senior's Presentation Night", datetime.datetime(2020, 5, 30, 18, 0, tzinfo=<UTC>), 2, 1, 'Preso night'), (2, "U15's Gala Day", datetime.datetime(2020, 5, 31, 12, 0, tzinfo=<UTC>), 3, 1, "let's go!"), (3, 'Test Event', datetime.datetime(2020, 5, 23, 0, 28, 59, tzinfo=<UTC>), 3, None, '')]>

You can also specify which fields to return:

>>> Event.objects.values_list('name')
<QuerySet [("Senior's Presentation Night",), ("U15's Gala Day",), ('Test Event',)]>
>>>

dates() and datetimes()

You use the dates() and datetimes() methods to return time-bounded records from the database (for example, all the events occurring in a particular month). For dates(), these time bounds are year, month, week and day. datetimes() adds hour, minute and second bounds. Some examples:

>>> from events.models import Event
>>> Event.objects.dates('event_date', 'year')
<QuerySet [datetime.date(2020, 1, 1)]>
>>> Event.objects.dates('event_date', 'month') 
<QuerySet [datetime.date(2020, 5, 1)]>
>>> Event.objects.dates('event_date', 'week')  
<QuerySet [datetime.date(2020, 5, 18), datetime.date(2020, 5, 25)]>
>>> Event.objects.dates('event_date', 'day')  
<QuerySet [datetime.date(2020, 5, 23), datetime.date(2020, 5, 30), datetime.date(2020, 5, 31)]>
>>>

select_related() and prefetch_related()

Selecting related information can be a database-intensive operation, as each foreign key relationship requires an additional database lookup. For example, each Event object in our database has a foreign key relationship with the Venue table:

>>> event1 = Event.objects.get(id=1)
>>> event1.venue # Foreign key retrieval causes additional database hit
<Venue: South Stadium>

For our simple example, this is not a problem, but in large databases with many foreign key relationships, the load on the database can be prohibitive.

You use select_related() to improve database performance by retrieving all related data the first time the database is hit:

>>> event2 = Event.objects.select_related('venue').get(id=2) 
>>> event2.venue # venue has already been retrieved. Database is not hit again.
<Venue: East Park>
>>>

prefetch_related() works the same way as select_related(), except it will work across many-to-many relationships.

Executing Raw SQL

While Django’s developers provide the raw() query method for executing raw SQL, you are explicitly discouraged from doing so.

The Django ORM is very powerful. In the vast majority of cases where I have seen programmers resort to SQL it has been due to incomplete knowledge of Django’s ORM on the programmers part, not a deficiency in the ORM.

If you find yourself in the situation where a query is so complex you can’t find a way of completing the task with Django’s ORM, it’s likely you need to create a stored procedure or a new view within the database itself.

Methods That Don’t Return QuerySets

Table 9.2 lists all the built-in model methods that don’t return QuerySets.

Table 9-2: Model methods that don’t return QuerySets.

Let’s return to the Django interactive shell to dig deeper into some common examples not already covered in the book.

get_or_create()

get_or_create() will attempt to retrieve a record matching the search fields. If a record doesn’t exist, it will create one. The return value will be a tuple containing the created or retrieved object and a boolean value that will be True if a new record was created:

>>> from events.models import MyClubUser 
>>> usr, boolCreated = MyClubUser.objects.get_or_create(first_name='John', last_name='Jones', email='johnj@example.com')
>>> usr
<MyClubUser: John Jones>
>>> boolCreated
True

If we try to create the object a second time, it will retrieve the new record from the database instead.

>>> usr, boolCreated = MyClubUser.objects.get_or_create(
...     first_name='John',
...     last_name='Jones',
...     email='johnj@example.com'
... )
>>> usr
<MyClubUser: John Jones>
>>> boolCreated
False

update_or_create()

update_or_create() works similar to get_or_create(), except you pass the search fields and a dictionary named defaults containing the fields to update. If the object doesn’t exist, the method will create a new record in the database:

>>> usr, boolCreated = MyClubUser.objects.update_or_create(first_name='Mary',  defaults={'email':'maryj@example.com'}) 
>>> usr
<MyClubUser: Mary Jones>
>>> boolCreated
True

If the record exists, Django will update all fields listed in the defaults dictionary:

>>> usr, boolCreated = MyClubUser.objects.update_or_create(first_name='Mary', last_name='Jones', defaults={'email':'mary_j@example.com'})    
>>> usr
<MyClubUser: Mary Jones>
>>> usr.email
'mary_j@example.com'
>>> boolCreated
False
>>>

bulk_create() and bulk_update()

The bulk_create() method saves time by inserting multiple objects into the database at once, most often in a single query. The function has one required parameter—a list of objects:

>>> usrs = MyClubUser.objects.bulk_create(
...     [
...         MyClubUser(
...             first_name='Jane',
...             last_name='Smith',
...             email='janes@example.com'
...         ),
...         MyClubUser(
...             first_name='Steve',
...             last_name='Smith',
...             email='steves@example.com'
...         ),
...     ]
... )
>>> usrs
[<MyClubUser: Jane Smith>, <MyClubUser: Steve Smith>]

bulk_update(), on the other hand, takes a list of model objects and updates individual fields on selected model instances. For example, let’s say the first two “Smiths” in the database were entered incorrectly. First, we retrieve all the “Smiths”:

>>> usrs = MyClubUser.objects.filter(last_name='Smith')
>>> usrs
<QuerySet [<MyClubUser: Joe Smith>, <MyClubUser: Jane Smith>, <MyClubUser: Steve Smith>]>

bulk_update will only work on a list of objects, so first, we must create a list of objects we want to update:

>>> update_list = [usrs[0], usrs[1]]

Then, we make the modifications to the objects in the list:

>>> update_list[0].last_name = 'Smythe'
>>> update_list[1].last_name = 'Smythe'

We can then use the bulk_update function to save the changes to the database in a single query:

>>> MyClubUser.objects.bulk_update(update_list, ['last_name'])
>>> MyClubUser.objects.all()
<QuerySet [<MyClubUser: Joe Smythe>, <MyClubUser: Jane Doe>, <MyClubUser: John Jones>, <MyClubUser: Mary Jones>, <MyClubUser: Jane Smythe>, <MyClubUser: Steve Smith>]>
>>>

count()

Counts the number of objects in the QuerySet. Can be used to count all objects in a database table:

>>> MyClubUser.objects.count()
9

Or used to count the number of objects returned by a query:

>>> MyClubUser.objects.filter(last_name='Smythe').count() 
2

count() is functionally equivalent to using the aggregate() function, but count() has a cleaner syntax, and is likely to be faster on large datasets. For example:

>>> from django.db.models import Count
>>> MyClubUser.objects.all().aggregate(Count('id'))
{'id__count': 9}

in_bulk()

in_bulk() takes a list of id values and returns a dictionary mapping each id to an instance of the object with that id. If you don’t pass a list to in_bulk(), all objects will be returned:

>>> usrs = MyClubUser.objects.in_bulk()
>>> usrs
{1: <MyClubUser: Joe Smythe>, 2: <MyClubUser: Jane Doe>, 3: <MyClubUser: John Jones>}

Once retrieved, you can access each object by their key value:

>>> usrs[3]
<MyClubUser: John Jones>
>>> usrs[3].first_name   
'John'

Any non-empty list will retrieve all records with the listed ids:

>>> MyClubUser.objects.in_bulk([1]) 
{1: <MyClubUser: Joe Smythe>}

List ids don’t have to be sequential either:

>>> MyClubUser.objects.in_bulk([1, 3, 7]) 
{1: <MyClubUser: Joe Smythe>, 3: <MyClubUser: John Jones>, 7: <MyClubUser: Mary Jones>}

latest() and earliest()

Return the latest or the earliest date in the database for the provided field(s):

>>> from events.models import Event
>>> Event.objects.latest('event_date')
<Event: Test Event>
>>> Event.objects.earliest('event_date') 
<Event: Club Presentation - Juniors>

first() and last()

Returns the first or last object in the QuerySet:

>>> Event.objects.first()
<Event: Test Event>
>>> Event.objects.last()  
<Event: Gala Day>

aggregate()

Returns a dictionary of aggregate values calculated over the QuerySet. For example:

>>> from django.db.models import Count
>>> Event.objects.aggregate(Count('attendees'))
{'attendees__count': 7}
>>>

For a list of all aggregate functions available in Django, see Aggregate Functions later in this chapter.

exists()

Returns True if the returned QuerySet contains one or more objects, False if the QuerySet is empty. There are two common use-cases—to check if an object is contained in another QuerySet:

>>> from events.models import MyClubUser
# Let's retrieve John Jones from the database
>>> usr = MyClubUser.objects.get(first_name='John', last_name='Jones')
# And check to make sure he is one of the Joneses
>>> joneses = MyClubUser.objects.filter(last_name='Jones') 
>>> joneses.filter(pk=usr.pk).exists() 
True

And to check if a query returns an object:

>>> joneses.filter(first_name='Mary').exists()
True
>>> joneses.filter(first_name='Peter').exists() 
False
>>>

Field Lookups

Field lookups have a simple double-underscore syntax:

<searchfield>__<lookup>

For example:

>>> MyClubUser.objects.filter(first_name__exact="Sally")
<QuerySet [<MyClubUser: Sally Jones>]>
>>> MyClubUser.objects.filter(first_name__contains="Sally") 
<QuerySet [<MyClubUser: Sally Jones>, <MyClubUser: Sally-Anne Jones>]>

A complete list of Django’s field lookups is in Table 9-3.

Under the hood, Django creates SQL WHERE clauses to construct database queries from the applied lookups. Multiple lookups are allowed, and field lookups can also be chained (where logical):

>>> from events.models import Event
# Get all events in 2020 that occur before September
>>> Event.objects.filter(event_date__year=2020, event_date__month__lt=9)
<QuerySet [<Event: Senior's Presentation Night>, <Event: U15's Gala Day>, <Event: Test Event>]>
>>>
# Get all events occuring on or after the 10th of the month
>>> Event.objects.filter(event_date__day__gte=10) 
<QuerySet [<Event: Senior's Presentation Night>, <Event: U15's Gala Day>, <Event: Test Event>]>
>>>

Table 9-3: Django’s model field lookups.

Aggregate Functions

Django includes seven aggregate functions:

• Avg. Returns the mean value of the expression.
• Count. Counts the number of returned objects.
• Max. Returns the maximum value of the expression.
• Min. Returns the minimum value of the expression.
• StdDev. Returns the population standard deviation of the data in the expression.
• Sum. Returns the sum of all values in the expression.
• Variance. Returns the population variance of the data in the expression.

They are translated to the equivalent SQL by Django’s ORM.

Aggregate functions can either be used directly:

>>> from events.models import Event
>>> Event.objects.count()
4

Or with the aggregate() function:

>>> from django.db.models import Count 
>>> Event.objects.aggregate(Count('id'))
{'id__count': 4}
>>>

More Complex Queries

Query Expressions

Query expressions describe a computation or value used as a part of another query. There are six built-in query expressions:

• F(). Represents the value of a model field or annotated column.
• Func(). Base type for database functions like LOWER and SUM.
• Aggregate(). All aggregate functions inherit from Aggregate().
• Value(). Expression value. Not used directly.
• ExpressionWrapper(). Used to wrap expressions of different types.
• SubQuery(). Add a subquery to a QuerySet.

Django supports multiple arithmetic operators with query expressions, including:

• Addition and subtraction
• Multiplication and division
• Negation
• Modulo arithmetic; and
• The power operator

We have already covered aggregation in this chapter, so let’s have a quick look at the other two commonly used query expressions: F() and Func().

F() Expressions

The two primary uses for F() expressions is to move computational arithmetic from Python to the database and to reference other fields in the model.

Let’s start with a simple example: say we want to delay the first event in the event calendar by two weeks. A conventional approach would look like this:

>>> from events.models import Event
>>> import datetime
>>> e = Event.objects.get(id=1)
>>> e.event_date += datetime.timedelta(days=14)
>>> e.save()

In this example, Django retrieves information from the database into memory, uses Python to perform the computation—in this case, add 14 days to the event date—and then saves the record back to the database.

For this example, the overhead for using Python to perform the date arithmetic is not excessive; however, for more complex queries, there is a definite advantage to moving the computational load to the database.

Now let’s see how we accomplish the same task with an F() expression:

>>> from django.db.models import F
>>> e = Event.objects.get(id=1)    
>>> e.event_date = F('event_date') + datetime.timedelta(days=14)
>>> e.save()

We’re not reducing the amount of code we need to write here, but by using the F() expression, Django creates an SQL query to perform the computational logic inside the database rather than in memory with Python.

While this takes a huge load off the Django application when executing complex computations, there is one drawback—because the calculations take place inside the database, Django is now out of sync with the updated state of the database. We can test this by looking at the Event object instance:

>>> e.event_date 
<CombinedExpression: F(event_date) + DurationValue(14 days, 0:00:00)>

To retrieve the updated object from the database, we need to use the refresh_from_db() function:

>>> e.refresh_from_db()
>>> e.event_date
datetime.datetime(2020, 6, 27, 18, 0, tzinfo=datetime.timezone(datetime.timedelta(0), '+0000'))

The second use for F() expressions—referencing other model fields—is straightforward. For example, you can check for users with the same first and last name:

>>> MyClubUser.objects.filter(first_name=F('last_name'))
<QuerySet [<MyClubUser: Don Don>]>

This simple syntax works with all of Django’s field lookups and aggregate functions.

Func() Expressions

Func() expressions can be used to represent any function supported by the underlying database (e.g. LOWER, UPPER, LEN, TRIM, CONCAT, etc.). For example:

>>> from events.models import MyClubUser
>>> from django.db.models import F, Func
>>> usrs = MyClubUser.objects.all()
>>> qry = usrs.annotate(f_upper=Func(F('last_name'), function='UPPER')) 
>>> for usr in qry:
...     print(usr.first_name, usr.f_upper)
... 
Joe SMYTHE
Jane DOE
John JONES
Sally JONES
Sally-Anne JONES
Sarah JONES
Mary JONES
Jane SMYTHE
Steve SMITH
Don DON
>>>

Notice how we are using F() expressions again to reference another field in the MyClubUser model.

Q() Objects

Like F() expressions, a Q() object encapsulates an SQL expression inside a Python object. Q() objects are most often used to construct complex database queries by chaining together multiple expressions using AND (&) and OR (|) operators:

>>> from events.models import MyClubUser 
>>> from django.db.models import Q
>>> Q1 = Q(first_name__startswith='J')
>>> Q2 = Q(first_name__endswith='e') 
>>> MyClubUser.objects.filter(Q1 & Q2)
<QuerySet [<MyClubUser: Joe Smythe>, <MyClubUser: Jane Doe>, <MyClubUser: Jane Smythe>]>
>>> MyClubUser.objects.filter(Q1 | Q2) 
<QuerySet [<MyClubUser: Joe Smythe>, <MyClubUser: Jane Doe>, <MyClubUser: John Jones>, <MyClubUser: Sally-Anne Jones>, <MyClubUser: Jane Smythe>, <MyClubUser: Steve Smith>]>
>>>

You can also perform NOT queries using the negate (~) character:

>>> MyClubUser.objects.filter(~Q2)      
<QuerySet [<MyClubUser: John Jones>, <MyClubUser: Sally Jones>, <MyClubUser: Sarah Jones>, <MyClubUser: Mary Jones>, <MyClubUser: Don Don>]>
>>> MyClubUser.objects.filter(Q1 & ~Q2) 
<QuerySet [<MyClubUser: John Jones>]>