As you learned in the previous chapter, Django models encapsulate data through classes, enforce data validation, are used to interact with a Django project's relational database and have a myriad of options to guarantee and customize how data operates in Django projects.

In this chapter we'll build on the previous Django model concepts and learn about a Django model queries and managers. We'll start with an in-depth look at Django model CRUD (Create-Read-Update-Delete) operations, including: single, multiple and relationship queries, covering their speed and efficiency implications. Next, you'll learn about the many SQL query variations supported by Django models, including: field lookups to produce SQL WHERE statements; models methods to produce SQL statements like DISTINCT and ORDER; as well as query expressions to execute SQL aggregation operations, database functions and sub-queries.

Next, you'll learn how to create raw (open-ended) SQL queries when Django's built-in SQL facilities prove to be insufficient. Finally, you'll learn how to create and configure custom model managers in Django models.

CRUD single records in Django models

Working with single records is one of the most common tasks you'll do with Django models. Next, I'll structure the following sections into the classical web application CRUD operations and describe the various techniques for each case so you can get a better grasp of what to use under different circumstances.

Note that although the following sections concentrate on the actual CRUD operation and its behaviors, sometimes I'll inevitably introduce more advanced query concepts in the examples (e.g. field lookups) which are described in detail in later sections of the chapter.

Create a single record with save() or create()

To create a single record on a Django model, you just need to make an instance of a model and invoke the save() method on it. Listing 8-1 illustrates the process to create a single record for a model called Store.

Tip Consult the book's accompanying source code to run the exercises, in order to reduce typing and automatically access test data.

Listing 8-1. Create a single record with model save() method

# Import Django model class
from coffeehouse.stores.models import Store

# Create a model Store instance
store_corporate = Store(name='Corporate',address='624 Broadway',state='CA',email='corporate@coffeehouse.com')

# Assign attribute value to instance with Python dotted notation
store_corporate.city = 'San Diego'

# Invoke the save() method to create the record
store_corporate.save()

# If successful, record reference has id 
store_corporate.id

As you can see in listing 8-1, you can declare all the instance attributes in a single step or you can use Python's dotted notation to assign attribute values one by one on the reference itself. Once the instance is ready, call the save() method on it to create the record in the database. There are two important behaviors to be aware of when you invoke save() method:

These are the two most important points when you use the save() method to create a record. For the full set of options and subtleties associated with a Django model save() method, see the previous chapter table 7-3 and the section on 'Model methods'.

After a successful call to the save() method in listing 8-1, you can see the object reference is assigned the id attribute -- created by the database -- which serves to directly link it to a database record that can later be updated and/or deleted.

The create() method offers a shorter route alternative to create a record. Listing 8-2 illustrates the equivalent record creation in listing 8-1 using the create() method.

Listing 8-2. Create a single record with create() method

# Import Django model class
from coffeehouse.stores.models import Store

# Create a model Store instance which is saved automatically
store_corporate = Store.objects.create(name='Corporate',address='624 Broadway',
city='San Diego',state='CA',email='corporate@coffeehouse')

# If successful, record reference has id 
store_corporate.id

You can see in listing 8-2, the create() method is invoked on a Django model class through the model's default objects model manager. The create() method accepts arguments that represent the model instance field values. The execution of create() returns an object reference to the created record including an id value just like the save() method.

Behind the scenes, the create() method actually uses the same save() method, but it uses the model manager to allow the creation of a record in a single line.

Read a single record with get() or get_or_create()

To read a single database record you can use the get() method -- which is part of a model's default objects model manager -- and which accepts any model field to qualify a record. Listing 8-3 illustrates a basic example of the get() Django model method.

Listing 8-3 Read model record with get() method

# Import Django model class
from coffeehouse.stores.models import Store

# Get the store with the name "Downtown" or equivalent SQL: 'SELECT....WHERE name = "Downtown"
downtown_store = Store.objects.get(name="Downtown")

# Define uptown_email for the query
uptown_email = "uptown@coffeehouse.com"

# Get the store with the email value uptown_email
# or equivalent SQL: 'SELECT....WHERE email = "uptown@coffeehouse.com"'
uptown_email_store = Store.objects.get(email=uptown_email)

# Once the get() method runs, you can access an object's attributes
# either in logging statements, functions or templates
downtown_store.address
downtown_store.email

# Note you can access the object without attributes.
# If the Django model has a __str__/ method definition, the output is based on this method
# If the Django model has no __str__ method definition, the output is just <object>
print(uptown_email_store)

As you can see in listing 8-3, the get() method uses a Django model attribute as its argument to retrieve a specific record. The first example gets the Store record with name=Downtown and the second example gets the Store record with email=uptown@coffeehouse.com. Once the record is assigned to a variable, you can access its contents or attributes using Python's dotted notation.

Tip In addition to single fields -- name="Downtown" or email="uptown@..." -- the get() method also accepts multiple fields to produce an and query (e.g. get(email="uptown@...",name="Downtown") to get a record were both email and name match). In addition, Django also offerrs field lookup to create finer single record queries (e.g. get(name__contains="Downtown") to produce a sub-string query). See the later section in the chapter on queries classified by SQL keyword.

It's that simple to use a Django model's get() method. However, the get() method has some behaviors you should be aware of:

Knowing these get() limitations, let's explore how to tackle the first scenario that involves a record that doesn't exist. A common occurrence when attempting to read a single record that doesn't exist, is to get it and if it doesn't exist just create it. Listing 8-4 illustrates how to use the get_or_create() method for this purpose.

Listing 8-4 Read or create model record with get_or_create() method

# Import Django model class
from coffeehouse.items.models import Menu

# Get or create a menu instance with name="Breakfast"
menu_target, created = Menu.objects.get_or_create(name="Breakfast")

As you can see in listing 8-4, the get_or_create() method -- also part of a model's default objects model manager -- is invoked on a Django model class using a model's attributes as its arguments to get or create a record in one step. The get_or_create() method returns a pair of results, the model instance -- whether created or read -- as well as a boolean indicating whether a model instance was created or read (i.e. True if created, False if read).

The get_or_create() method is a shortcut that uses both the get() and the create() methods -- the last of which uses the save() method behind the scenes, as you learned in the previous section. The difference being, the get_or_create() method automatically handles the error condition when get() finds no matches. Listing 8-5 illustrates how the get_or_create() method functions behind the scenes, which you can also use if you prefer to handle get() errors method explicitly.

Listing 8-5 Replicate get_or_create() method with explicit try/except block and save method

from django.core.exceptions import ObjectDoesNotExist
from coffeehouse.items.models import Menu

try: 
     menu_target = Menu.objects.get(name="Dinner")
     # If get() throws an error you need to handle it. 
     # You can use either the generic ObjectDoesNotExist or
     # <model>.DoesNotExist which inherits from
     # django.core.exceptions.ObjectDoesNotExist, so you can target multiple
     # DoesNotExist exceptions
except Menu.DoesNotExist: # or the generic "except ObjectDoesNotExist:"      
     menu_target = Menu(name="Dinner")
     menu_target.save()

As you can see in listing 8-5, it's necessary to write more code (e.g. error handling, get and save calls) when you know there's a possibility a record doesn't exist and you want to create it anyways. So the get_or_create() method becomes a helpful shortcut in this scenario.

Now let's take a look at the second get() limitation which involves getting multiple records on a query. By design, the get() method throws a MultipleObjectsReturned error if more than one record matches a query. This behavior is an actual feature, because there are circumstances when you want to ensure a query only returns one record and be informed otherwise (e.g. a query for a user or product where duplicates are considered erroneous).

If there's a possibility for a query to return one or multiple records, then you'll need to forgo the use of get() method and use either a model manager's filter() or exclude() methods. Both the filter() or exclude() methods produce a multi-record data structure called a QuerySet, which can be reduced to a single record with an additional QuerySet method (e.g. Item.objects.filter(name__contains='Salad').first() to get the first Item record whose name contains the Salad sub-string).

Since a Django model's filter() and exclude() methods are designed for multiple record queries, these methods along with QuerySet behaviors are described in detail in the later section on CRUD operations for multiple records. Additional QuerySet methods like first() are also described in the later section on model queries classified by SQL keyword.

Update a single record with save(), update(), update_or_create() or refresh_from_db()

If you already have a reference to a model record, an update is as simple as updating its attributes using Python's dotted notation and calling the save() method on it. Listing 8-6 illustrates this process.

Listing 8-6. Update model record with the save() method

# Import Django model class
from coffeehouse.stores.models import Store

# Get the store with the name "Downtown" or equivalent SQL:
# 'SELECT....WHERE name = "Downtown"
downtown_store = Store.objects.get(name="Downtown")

# Update the name value
downtown_store.name = "Downtown (Madison)"

# Call save() with the update_fields arg and a list of record fields to update selectively
downtown_store.save(update_fields=['name'])

# Or you can call save() without any argument and all record fields are updated
downtown_store.save()

In listing 8-6, you can see the save() method is called in two ways. You can use the update_fields argument with a list of fields to update certain fields and get a performance boost in large models. Or the other alternative is to use save() without any argument, in which case Django updates all fields.

If you don't yet have a reference to the record to update, it's slightly inefficient to first get it (i.e. issue a SELECT query) and then update it with the save() method. In addition, doing the update process in separate steps can lead to race conditions. For example, if another user fetches the same data at the same time and also does an update, you'll both race to save it, but whose update is definitive and whose is overwritten ? Because no party is aware the other is working on the same data, you need a way to indicate -- technically known as lock or isolate -- the data to avoid race conditions.

For such cases you can use the update() method -- part of a model's default objects model manager -- which performs an update in a single operation and guarantees there are no race conditions. Listing 8-7 illustrates this process.

Listing 8-7. Update model record with the update() method

from coffeehouse.stores.models import Store

Store.objects.filter(id=1).update(name="Downtown (Madison)")


from coffeehouse.items.models import Item
from django.db.models import F

Item.objects.filter(id=3).update(stock=F('stock') +100)

The first example in listing 8-7 uses the update() method to update the Store record with id=1 and set its name to Downtown (Madison). The second example in listing 8-7 uses a Django F expression and the update() method to update the Item record with id=3 and set its stock value to the current stock value plus 100. For the moment, don't worry about Django F expressions -- they're described later on for more elaborate queries -- just realize Django F expressions allow you to reference model fields within a query -- as an SQL expression -- which is necessary in this case to perform the update in a single operation.

Caution The update() method can update a field across multiple records if you're not careful. The update() method is preceded by the objects.filter() method which can return query results for multiple records. Notice in listing 8-7 the query uses the id field to define the query, ensuring that only a single record matches the query, because id is the table's primary key. If the query definition in objects.filter() uses a less strict look-up (e.g. a string) you can inadvertently update more records than you expect.

Similar to the convenience get_or_create() method described in the previous section, Django also offers the convenience update_or_create() method. This method is helpful in cases where you want to perform an update and aren't sure if the record exists yet. Listing 8-8 illustrates this process.

Listing 8-8 -- Update or create model record with the update_or_create() method

# Import Django model class
from coffeehouse.stores.models import Store

values_to_update = {'email':'downtown@coffeehouse.com'}


# Update for record with name='Downtown' and city='San Diego' is found, otherwise create record
obj_store, created = Store.objects.update_or_create(
    name='Downtown',city='San Diego', defaults=values_to_update)

The first thing that's done in listing 8-8 is create a dictionary with field-values to update. Next, you pass a query argument to update_or_create for a desired object (i.e. the one you wish to update or create), along with dictionary containing the field-values to update.

For the case in listing 8-8, if there's already a Store record with name='Downtown' and city='San Diego' the record's values in values_to_update are updated, if there is no matching Store record a new Store record with name='Downtown', city='San Diego' along with the values in values_to_update. The update_or_create method returns an updated or created object, as well as a boolean value to indicate if the record was newly created or not.

Note update_or_create only works on queries with single records. If there are multiple records that match the query in update_or_create() you'll get the error MultipleObjectsReturned just like the get() method.

If you change a model record inadvertently, you can re-instate its data from the database with the refresh_from_db() method, as illustrated in listing 8-9.

Listing 8-9.- Update model record from database with the refresh_from_db() method

from coffeehouse.stores.models import Store

store_corporate = Store.objects.get(id=1)
store_corporate.name = 'Not sure about this name'

# Update from db again
store_corporate.refresh_from_db() 
# Model record name now reflects value in database again store_corporate.name # Multiple edits store_corporate.name = 'New store name' store_corporate.email = 'newemail@coffeehouse.com'
store_corporate.address = 'To be confirmed' # Update from db again, but only address field # so store name and email remain with local values store_corporate.refresh_from_db(fields=['address'])

As you can see in listing 8-9, after changing the name field value on a model record, you can call the refresh_from_db() method on the reference to update the model record as it's in the database. The second example in listing 8-9 uses the refresh_from_db() method with the fields argument, which tells Django to only update the model fields declared in the fields list, allowing any (local) edits made to other fields to remain unchanged.

Delete a single record with delete()

If you already have a reference to a record, deleting it is as simple as invoking the delete() method on it. Listing 8-10 illustrates this process.

Listing 8-10. Delete model record with the delete() method

# Import Django model class
from coffeehouse.stores.models import Store

# Get the store with the name "Downtown" or equivalent SQL:
# 'SELECT....WHERE name = "Downtown"
downtown_store = Store.objects.get(name="Downtown")

# Call delete() to delete the record in the database
downtown_store.delete()

For cases where you don't yet have a reference to a record you want to delete, it can be slightly inefficient to first get it (i.e. issue a SELECT query) and then delete it with the delete() method. For such cases you can use the delete() method and append it to a query so everything is done in a single operation. Listing 8-11 illustrates this process.

Listing 8-11. Delete model record with the delete() method on query

from coffeehouse.items.models import Menu

Menu.objects.filter(id=1).delete()

Irrespective of the delete() method you use -- directly on reference or through the objects model manager -- a delete() method always returns a dictionary with the results of the delete operation. For example, if the delete operation in 8-11 is successful it returns (1, {'items.Menu': 1}) indicating one record of the items.Menu type was deleted. If the delete operation in 8-10 is successful, it returns (5, {'stores.Store_amenities': 4, 'stores.Store': 1}) indicating five overall records were deleted, four of the stores.Store_amenities type and one of the stores.Store -- in this case multiple records are deleted because stores.Store_amenities is a model relationship in the Store model.

Caution The delete() method can delete multiple records if you're not careful. The delete() method is preceded by the objects.filter() method which can return query results with multiple records. Notice in listing 8-11 the query uses an id field to define the query, ensuring that only a single record matches the query, because id is a table's primary key. If the query definition in objects.filter() uses a less strict look-up (e.g. a string) you can inadvertently delete more records than you expect.