Django’s database API is the other half of the model API discussed in Appendix A. Once you’ve defined a model, you’ll use this API any time you need to access the database. You’ve seen examples of this API in use throughout the book; this appendix explains all the various options in detail.
Throughout this appendix I’ll refer to the following models, which comprise a Weblog application:
To represent database-table data in Python objects, Django uses an intuitive system: a model class represents a database table, and an instance of that class represents a particular record in the database table.
To create an object, instantiate it using keyword arguments to the model class, then call
save() to save it to the database.
Assuming models live in a file
mysite/blog/models.py, here’s an example:
This performs an
INSERT SQL statement behind the scenes. Django doesn’t hit the database until you explicitly call
save() method has no return value.
To create and save an object in a single step, use the
Saving Changes to Objects
To save changes to an object that’s already in the database, use
b5 that has already been saved to the database, this example changes its name and updates its record in the database:
This performs an
UPDATE SQL statement behind the scenes. Django doesn’t hit the database until you explicitly call
Saving ForeignKey and ManyToManyField fields
ForeignKey field works exactly the same way as saving a normal field – simply assign an object of the right type to the field in question. This example updates the
blog attribute of an
entry, assuming appropriate instances of
Blog are already saved to the database (so we can retrieve them below):
ManyToManyField works a little differently – use the
add() method on the field to add a record to the relation. This example adds the
joe to the
To add multiple records to a
ManyToManyField in one go, include multiple arguments in the call to
add(), like this:
Django will complain if you try to assign or add an object of the wrong type.
To retrieve objects from your database, construct a
QuerySet via a
Manager on your model class.
QuerySet represents a collection of objects from your database. It can have zero, one or many filters. Filters narrow down the query results based on the given parameters. In SQL terms, a
QuerySet equates to a
SELECT statement, and a filter is a limiting clause such as
You get a
QuerySet by using your model’s
Manager. Each model has at least one
Manager, and it’s called
objects by default. Access it directly via the model class, like so:
Retrieving All Objects
The simplest way to retrieve objects from a table is to get all of them. To do this, use the
method on a
all() method returns a
QuerySet of all the objects in the database.
Retrieving Specific Objects with Filters
QuerySet returned by
all() describes all objects in the database table. Usually, though, you’ll need to select only a subset of the complete set of objects.
To create such a subset, you refine the initial
QuerySet, adding filter conditions. The two most common ways to refine a
filter(**kwargs).Returns a new
QuerySetcontaining objects that match the given lookup parameters.
exclude(**kwargs).Returns a new
QuerySetcontaining objects that do not match the given lookup parameters.
The lookup parameters (
**kwargs in the above function definitions) should be in the format described in “field lookups” later in this chapter.
The result of refining a
QuerySet is itself a
QuerySet, so it’s possible to chain refinements together. For example:
This takes the initial
QuerySet of all entries in the database, adds a filter, then an exclusion, then another filter. The final result is a
QuerySet containing all entries with a headline that starts with “What”, that were published between January 30, 2005, and the current day.
Filtered Querysets Are Unique
Each time you refine a
QuerySet, you get a brand-new
QuerySet that is in no way bound to the previous
QuerySet. Each refinement creates a separate and distinct
QuerySet that can be stored, used and reused.
QuerySets are separate. The first is a base
QuerySet containing all entries that contain a headline starting with What. The second is a subset of the first, with an additional criterion that excludes records whose
pub_date is today or in the future. The third is a subset of the first, with an additional criterion that selects only the records whose
pub_date is today or in the future. The initial
q1) is unaffected by the refinement process.
QuerySets are Lazy
QuerySets are lazy – the act of creating a
QuerySet doesn’t involve any database activity. You can stack filters together all day long, and Django won’t actually run the query until the
QuerySet is evaluated. Take a look at this example:
Though this looks like three database hits, in fact it hits the database only once, at the last line (
print(q)). In general, the results of a
QuerySet aren’t fetched from the database until you ask for them. When you do, the
QuerySet is evaluated by accessing the database.
Retrieving A Single Object with Get
filter() will always give you a
QuerySet, even if only a single object matches the query – in this case, it will be a
QuerySet containing a single element.
If you know there is only one object that matches your query, you can use the
get() method on a
Manager which returns the object directly:
You can use any query expression with
get(), just like with
filter() – again, see “field lookups” in the next section of this chapter.
Note that there is a difference between using
get(), and using
filter() with a slice of
. If there are no results that match the query,
get() will raise a
DoesNotExist exception. This exception is an attribute of the model class that the query is being performed on – so in the code above, if there is no
object with a primary key of 1, Django will raise
Similarly, Django will complain if more than one item matches the
get() query. In this case, it will raise
MultipleObjectsReturned, which again is an attribute of the model class itself.
Other Queryset Methods
Most of the time you’ll use
exclude() when you need to look up objects from the database. However, that’s far from all there is; see the QuerySet API Reference
for a complete list of all the various
Use a subset of Python’s array-slicing syntax to limit your
QuerySet to a certain number of results. This is the equivalent of SQL’s
For example, this returns the first 5 objects (
This returns the sixth through tenth objects (
OFFSET 5 LIMIT 5):
Negative indexing (i.e.
Entry.objects.all()[-1]) is not supported.
Generally, slicing a
QuerySet returns a new
QuerySet – it doesn’t evaluate the query. An exception is if you use the step parameter of Python slice syntax. For example, this would actually execute the query in order to return a list of every second object of the first 10:
To retrieve a single object rather than a list (e.g.
SELECT foo FROM bar LIMIT 1),
use a simple index instead of a slice.
For example, this returns the first
Entry in the database, after ordering entries alphabetically by headline:
This is roughly equivalent to:
Note, however, that the first of these will raise
IndexError while the second will raise
DoesNotExist if no objects match the given criteria. See
get() for more details.
Field lookups are how you specify the meat of an SQL
WHERE clause. They’re specified as keyword arguments to the
get(). Basic lookups keyword arguments take the form
(That’s a double-underscore). For example:
translates (roughly) into the following SQL:
The field specified in a lookup has to be the name of a model field. There’s one exception though, in case of a
ForeignKey you can specify the field name suffixed with
_id. In this case, the value parameter is expected to contain the raw value of the foreign model’s primary key. For example:
If you pass an invalid keyword argument, a lookup function will raise
The complete list of field lookups are:
A complete reference, including examples for each field lookup can be found in the field lookup reference .
Lookups That Span Relationships
Django offers a powerful and intuitive way to follow relationships in lookups, taking care of the SQL
JOINs for you automatically, behind the scenes. To span a relationship, just use the field name of related fields across models, separated by double underscores, until you get to the field you want.
This example retrieves all
Entry objects with a
This spanning can be as deep as you’d like.
It works backwards, too. To refer to a reverse relationship, just use the lowercase name of the model.
This example retrieves all
Blog objects which have at least one
If you are filtering across multiple relationships and one of the intermediate models doesn’t have a value that meets the filter condition, Django will treat it as if there is an empty (all values are
NULL), but valid, object there. All this means is that no error will be raised. For example, in this filter:
(if there was a related
Author model), if there was no
author associated with an entry, it would be treated as if there was also no
name attached, rather than raising an error because of the missing
author. Usually this is exactly what you want to have happen. The only case where it might be confusing is if you are using
Blog objects that have an empty
name on the
author and also those which have an empty
author on the
entry. If you don’t want those latter objects, you could write:
Spanning multi-valued relationships
When you are filtering an object based on a
ManyToManyField or a reverse
ForeignKey, there are two different sorts of filter you may be interested in. Consider the
Entry relationship (
Entry is a one-to-many relation). We might be interested in finding blogs that have an entry which has both Lennon in the headline and was published in 2008.
Or we might want to find blogs that have an entry with Lennon in the headline as well as an entry that was published in 2008. Since there are multiple entries associated with a single
Blog, both of these queries are possible and make sense in some situations.
The same type of situation arises with a
ManyToManyField. For example, if an
Entry has a
tags, we might want to find entries linked to tags called “music”
and “bands” or we might want an entry that contains a tag with a name of “music” and a status of “public”.
To handle both of these situations, Django has a consistent way of processing
exclude() calls. Everything inside a single
filter() call is applied simultaneously to filter out items matching all those requirements.
filter() calls further restrict the set of objects, but for multi-valued relations, they apply to any object linked to the primary model, not necessarily those objects that were selected by an earlier
That may sound a bit confusing, so hopefully an example will clarify. To select all blogs that contain entries with both Lennon in the headline and that were published in 2008 (the same entry satisfying both conditions), we would write:
To select all blogs that contain an entry with Lennon in the headline as well as an entry that was published in 2008, we would write:
Suppose there is only one blog that had both entries containing Lennon and entries from 2008, but that none of the entries from 2008 contained Lennon. The first query would not return any blogs, but the second query would return that one blog.
In the second example, the first filter restricts the queryset to all those blogs linked to entries with Lennon in the headline. The second filter restricts the set of blogs further to those that are also linked to entries that were published in 2008.
The entries selected by the second filter may or may not be the same as the entries in the first filter. We are filtering the
Blog items with each filter statement, not the
All of this behavior also applies to
exclude(): all the conditions in a single
exclude() statement apply to a single instance (if those conditions are talking about the same multi-valued relation). Conditions in subsequent
exclude() calls that refer to the same relation may end up filtering on different linked objects.
Filters Can Reference Fields On the Model
In the examples given so far, we have constructed filters that compare the value of a model field with a constant. But what if you want to compare the value of a model field with another field on the same model?
F expressions to allow such comparisons. Instances of
F() act as a reference to a model field within a query. These references can then be used in query filters to compare the values of two different fields on the same model instance.
For example, to find a list of all blog entries that have had more comments than pingbacks, we construct an
F() object to reference the pingback count, and use that
F() object in the query:
Django supports the use of addition, subtraction, multiplication, division, modulo, and power arithmetic with
F() objects, both with constants and with other
F() objects. To find all the blog entries with more than twice as many comments as pingbacks, we modify the query:
To find all the entries where the rating of the entry is less than the sum of the pingback count and comment count, we would issue the query:
You can also use the double underscore notation to span relationships in an
F() object. An
F() object with a double underscore will introduce any joins needed to access the related object.
For example, to retrieve all the entries where the author’s name is the same as the blog name, we could issue the query:
For date and date/time fields, you can add or subtract a
timedelta object. The following would return all entries that were modified more than 3 days after they were published:
F() objects support bitwise operations by
.bitor(), for example:
The pk Lookup Shortcut
For convenience, Django provides a
pk lookup shortcut, which stands for primary key.
In the example
Blog model, the primary key is the
id field, so these three statements are equivalent:
The use of
pk isn’t limited to
__exact queries – any query term can be combined with
pk to perform a query on the primary key of a model:
pk lookups also work across joins. For example, these three statements are equivalent:
Escaping Percent Signs and Underscores in LIKE Statements
The field lookups that equate to
LIKE SQL statements (
iendswith) will automatically escape the two special characters used in
statements – the percent sign and the underscore. (In a
LIKE statement, the percent sign signifies a multiple-character wildcard and the underscore signifies a single-character wildcard.)
This means things should work intuitively, so the abstraction doesn’t leak. For example, to retrieve all the entries that contain a percent sign, just use the percent sign as any other character:
Django takes care of the quoting for you; the resulting SQL will look something like this:
Same goes for underscores. Both percentage signs and underscores are handled for you transparently.
Caching and Querysets
QuerySet contains a cache to minimize database access. Understanding how it works will allow you to write the most efficient code.
In a newly created
QuerySet, the cache is empty. The first time a
QuerySet is evaluated – and, hence, a database query happens – Django saves the query results in the
QuerySet’s cache and returns the results that have been explicitly requested (e.g., the next element, if the
QuerySet is being iterated over). Subsequent evaluations of the
QuerySet reuse the cached results.
Keep this caching behavior in mind, because it may bite you if you don’t use your
QuerySets correctly. For example, the following will create two
QuerySets, evaluate them, and throw them away:
That means the same database query will be executed twice, effectively doubling your database load. Also, there’s a possibility the two lists may not include the same database records, because an
Entry may have been added or deleted in the split second between the two requests.
To avoid this problem, simply save the
QuerySet and reuse it:
When querysets are not cached
Querysets do not always cache their results. When evaluating only part of the queryset, the cache is checked, but if it is not populated then the items returned by the subsequent query are not cached. Specifically, this means that limiting the queryset using an array slice or an index will not populate the cache.
For example, repeatedly getting a certain index in a queryset object will query the database each time:
However, if the entire queryset has already been evaluated, the cache will be checked instead:
Here are some examples of other actions that will result in the entire queryset being evaluated and therefore populate the cache:
Complex Lookups with Q Objects
Keyword argument queries – in
filter(), etc. – are ANDed together. If you need to execute more complex queries (for example, queries with
OR statements), you can use
Q object (
django.db.models.Q) is an object used to encapsulate a collection of keyword arguments. These keyword arguments are specified as in Field lookups above.
For example, this
Q object encapsulates a single
Q objects can be combined using the
| operators. When an operator is used on two
Q objects, it yields a new
For example, this statement yields a single
Q object that represents the OR of two
This is equivalent to the following SQL
You can compose statements of arbitrary complexity by combining
Q objects with the
| operators and use parenthetical grouping. Also,
Q objects can be negated using the
~ operator, allowing for combined lookups that combine both a normal query and a negated (
Each lookup function that takes keyword-arguments (e.g.
get()) can also be passed one or more
Q objects as positional (not-named) arguments. If you provide multiple
object arguments to a lookup function, the arguments will be ANDed together.
… roughly translates into the SQL:
Lookup functions can mix the use of
Q objects and keyword arguments. All arguments provided to a lookup function (be the keyword arguments or
Q objects) are ANDed together. However, if a
Q object is provided, it must precede the definition of any keyword arguments. For example:
… would be a valid query, equivalent to the previous example; but:
… would not be valid.
To compare two model instances, just use the standard Python comparison operator, the double equals sign:
==. Behind the scenes, that compares the primary key values of two models.
Entry example above, the following two statements are equivalent:
If a model’s primary key isn’t called
id, no problem. Comparisons will always use the primary key, whatever it’s called. For example, if a model’s primary key field is called
name, these two statements are equivalent:
The delete method, conveniently, is named
delete(). This method immediately deletes the object and has no return value. Example:
You can also delete objects in bulk. Every
QuerySet has a
delete() method, which deletes all members of that
For example, this deletes all
Entry objects with a
pub_date year of 2005:
Keep in mind that this will, whenever possible, be executed purely in SQL, and so the
delete() methods of individual object instances will not necessarily be called during the process. If you’ve provided a custom
delete() method on a model class and want to ensure that it is called, you will need to manually delete instances of that model (e.g., by iterating over a
QuerySet and calling
delete() on each object individually) rather than using the bulk
delete() method of a
When Django deletes an object, by default it emulates the behavior of the SQL constraint
ON DELETE CASCADE – in other words, any objects which had foreign keys pointing at the object to be deleted will be deleted along with it. For example:
This cascade behavior is customizable via the
on_delete argument to the
delete() is the only
QuerySet method that is not exposed on a
Manager itself. This is a safety mechanism to prevent you from accidentally requesting
Entry.objects.delete(), and deleting all the entries. If you do want to delete all the objects, then you have to explicitly request a complete query set:
Copying Model Instances
Although there is no built-in method for copying model instances, it is possible to easily create new instance with all fields’ values copied. In the simplest case, you can just set
None. Using our blog example:
Things get more complicated if you use inheritance. Consider a subclass of
Due to how inheritance works, you have to set both
id to None:
This process does not copy related objects. If you want to copy relations, you have to write a little bit more code. In our example,
Entry has a many to many field to
Updating Multiple Objects at Once
Sometimes you want to set a field to a particular value for all the objects in a
QuerySet. You can do this with the
update() method. For example:
You can only set non-relation fields and
ForeignKey fields using this method. To update a non-relation field, provide the new value as a constant. To update
ForeignKey fields, set the new value to be the new model instance you want to point to. For example:
update() method is applied instantly and returns the number of rows matched by the query (which may not be equal to the number of rows updated if some rows already have the new value).
The only restriction on the
QuerySet that is updated is that it can only access one database table, the model’s main table. You can filter based on related fields, but you can only update columns in the model’s main table. Example:
Be aware that the
update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any
save() methods on your models, or emit the
post_save signals (which are a consequence of calling
save()), or honor the
auto_now field option. If you want to save every item in a
QuerySet and make sure that the
save() method is called on each instance, you don’t need any special function to handle that. Just loop over them and call
Calls to update can also use
F expressions to update one field based on the value of another field in the model. This is especially useful for incrementing counters based upon their current value. For example, to increment the pingback count for every entry in the blog:
F() objects in filter and exclude clauses, you can’t introduce joins when you use
F() objects in an update – you can only reference fields local to the model being updated. If you attempt to introduce a join with an
F() object, a
FieldError will be raised:
When you define a relationship in a model (i.e., a
ManyToManyField), instances of that model will have a convenient API to access the related object(s).
Using the models at the top of this page, for example, an
e can get its associated
Blog object by accessing the
(Behind the scenes, this functionality is implemented by Python descriptors. This shouldn’t really matter to you, but I point it out here for the curious.)
Django also creates API accessors for the other side of the relationship – the link from the related model to the model that defines the relationship. For example, a
b has access to a list of all related
Entry objects via the
All examples in this section use the sample
Entry models defined at the top of this page.
If a model has a
ForeignKey, instances of that model will have access to the related (foreign)
object via a simple attribute of the model. For example:
You can get and set via a foreign-key attribute. As you may expect, changes to the foreign key aren’t saved to the database until you call
ForeignKey field has
null=True set (i.e., it allows
NULL values), you can assign
None to remove the relation. Example:
Forward access to one-to-many relationships is cached the first time the related object is accessed. Subsequent accesses to the foreign key on the same object instance are cached. Example:
Note that the
QuerySet method recursively prepopulates the cache of all one-to-many relationships ahead of time. Example:
Following relationships backward
If a model has a
ForeignKey, instances of the foreign-key model will have access to a
Manager that returns all instances of the first model. By default, this
Manager is named
foo is the source model name, lowercased. This
QuerySets, which can be filtered and manipulated as described in the Retrieving objects section above.
You can override the
foo_set name by setting the
related_name parameter in the
ForeignKey definition. For example, if the
Entry model was altered to
blog = ForeignKey(Blog, related_name='entries'), the above example code would look like this:
Using a custom reverse manager
By default, the
RelatedManager used for reverse relations is a subclass of the default manager for that model. If you would like to specify a different manager for a given query you can use the following syntax:
EntryManager performed default filtering in its
get_queryset() method, that filtering would apply to the
Of course, specifying a custom reverse manager also enables you to call its custom methods:
Additional methods to handle related objects
In addition to the
QuerySet methods defined in “retrieving objects” earlier, the
Manager has additional methods used to handle the set of related objects. A synopsis of each is as follows (complete details can be found in the related objects reference):
add(obj1, obj2, ...)Adds the specified model objects to the related object set.
create(**kwargs)Creates a new object, saves it and puts it in the related object set. Returns the newly created object.
remove(obj1, obj2, ...)Removes the specified model objects from the related object set.
clear()Removes all objects from the related object set.
set(objs)Replace the set of related objects.
To assign the members of a related set in one fell swoop, just assign to it from any iterable object. The iterable can contain object instances, or just a list of primary key values. For example:
In this example,
e2 can be full Entry instances, or integer primary key values.
clear() method is available, any pre-existing objects will be removed from the
entry_set before all objects in the iterable (in this case, a list) are added to the set. If the
clear() method is not
available, all objects in the iterable will be added without removing any existing elements.
Each reverse operation described in this section has an immediate effect on the database. Every addition, creation and deletion is immediately and automatically saved to the database.
Both ends of a many-to-many relationship get automatic API access to the other end. The API works just as a backward one-to-many relationship, above.
The only difference is in the attribute naming: The model that defines the
ManyToManyField uses the attribute name of that field itself, whereas the reverse model uses the lowercased model name of the original model, plus
'_set' (just like reverse one-to-many relationships).
An example makes this easier to understand:
ManyToManyField can specify
related_name. In the above example, if the
Entry had specified
related_name='entries', then each
Author instance would have an
entries attribute instead of
One-to-one relationships are very similar to many-to-one relationships. If you define a
OneToOneField on your model, instances of that model will have access to the related object via a simple attribute of the model.
The difference comes in reverse queries. The related model in a one-to-one relationship also has access to a
Manager object, but that
Manager represents a single object, rather than a collection of objects:
If no object has been assigned to this relationship, Django will raise a
Instances can be assigned to the reverse relationship in the same way as you would assign the forward relationship:
Queries Over Related Objects
Queries involving related objects follow the same rules as queries involving normal value fields. When specifying the value for a query to match, you may use either an object instance itself, or the primary key value for the object.
For example, if you have a Blog object
id=5, the following three queries would be identical:
Falling Back to Raw SQL
If you find yourself needing to write an SQL query that is too complex for Django’s database-mapper to handle, you can fall back on writing SQL by hand.
Finally, it’s important to note that the Django database layer is merely an interface to your database. You can access your database via other tools, programming languages or database frameworks; there’s nothing Django-specific about your database.