To retrieve objects from your database, construct a QuerySet
via a Manager
on your model class.
A 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 WHERE
or LIMIT
.
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:
>>> Blog.objects <django.db.models.manager.Manager object at ...> >>> b = Blog(name='Foo', tagline='Bar') >>> b.objects Traceback: ... AttributeError: "Manager isn't accessible via Blog instances."
The simplest way to retrieve objects from a table is to get all of them. To do this, use the all()
method on a Manager
:
>>> all_entries = Entry.objects.all()
The all()
method returns a QuerySet
of all the objects in the database.
The 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 QuerySet
are:
filter(**kwargs)
. Returns a newQuerySet
containing objects that match the given lookup parameters.exclude(**kwargs)
. Returns a newQuerySet
containing 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:
>>> Entry.objects.filter( ... headline__startswith='What' ... ).exclude( ... pub_date__gte=datetime.date.today() ... ).filter(pub_date__gte=datetime(2005, 1, 30) ... )
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.
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.
Example:
>>> q1 = Entry.objects.filter(headline__startswith="What") >>> q2 = q1.exclude(pub_date__gte=datetime.date.today()) >>> q3 = q1.filter(pub_date__gte=datetime.date.today())
These three 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 QuerySet
(q1
) is unaffected by the refinement process.
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:
>>> q = Entry.objects.filter(headline__startswith="What") >>> q = q.filter(pub_date__lte=datetime.date.today()) >>> q = q.exclude(body_text__icontains="food") >>> print(q)
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.
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:
>>> one_entry = Entry.objects.get(pk=1)
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 [0]
. 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 Entry
object with a primary key of 1, Django will raise Entry.DoesNotExist
.
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.
Most of the time you'll use all()
, get()
, filter()
, and exclude()
when you need to look up objects from the database. However, that's far from all there is; see the QuerySet API Reference at https://docs.djangoproject.com/en/1.8/ref/models/querysets/, for a complete list of all the various QuerySet
methods.
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 LIMIT
and OFFSET
clauses.
For example, this returns the first 5 objects (LIMIT 5
):
>>> Entry.objects.all()[:5]
This returns the sixth through tenth objects (OFFSET 5 LIMIT 5
):
>>> Entry.objects.all()[5:10]
Negative indexing (that is, 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:
>>> Entry.objects.all()[:10:2]
To retrieve a single object rather than a list (for example, 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:
>>> Entry.objects.order_by('headline')[0]
This is roughly equivalent to:
>>> Entry.objects.order_by('headline')[0:1].get()
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 QuerySet
methods filter()
, exclude()
, and get()
. Basic lookups keyword arguments take the form field__lookuptype=value
. (That's a double-underscore). For example:
>>> Entry.objects.filter(pub_date__lte='2006-01-01')
translates (roughly) into the following SQL:
SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
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:
>>> Entry.objects.filter(blog_id=4)
If you pass an invalid keyword argument, a lookup function will raise TypeError
.
The complete list of field lookups are:
exact
iexact
contains
icontains
in
gt
gte
lt
lte
startswith
istartswith
endswith
iendswith
range
year
month
day
week_day
hour
minute
second
isnull
search
regex
iregex
A complete reference, including examples for each field lookup can be found in the field lookup reference at https://docs.djangoproject.com/en/1.8/ref/models/querysets/#field-lookups.
Django offers a powerful and intuitive way to follow relationships in lookups, taking care of the SQL JOIN
s 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 Blog
whose name
is 'Beatles Blog'
:
>>> Entry.objects.filter(blog__name='Beatles Blog')
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 Entry
whose headline
contains 'Lennon'
:
>>> Blog.objects.filter(entry__headline__contains='Lennon')
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:
Blog.objects.filter(entry__authors__name='Lennon')
(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 isnull
. Thus:
Blog.objects.filter(entry__authors__name__isnull=True)
will return 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:
Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)
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 Blog
/Entry
relationship (Blog
to 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 ManyToManyField
called 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 filter()
and exclude()
calls. Everything inside a single filter()
call is applied simultaneously to filter out items matching all those requirements.
Successive 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 filter()
call.
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:
Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008)
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:
Blog.objects.filter(entry__headline__contains='Lennon').filter( entry__pub_date__year=2008)
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 Entry
items.
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 filter()
or exclude()
calls that refer to the same relation may end up filtering on different linked objects.
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?
Django provides 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:
>>> from django.db.models import F >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
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:
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)
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:
>>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
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:
>>> Entry.objects.filter(authors__name=F('blog__name'))
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:
>>> from datetime import timedelta >>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
The F()
objects support bitwise operations by .bitand()
and .bitor()
, for example:
>>> F('somefield').bitand(16)
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:
>>> Blog.objects.get(id__exact=14) # Explicit form >>> Blog.objects.get(id=14) # __exact is implied >>> Blog.objects.get(pk=14) # pk implies id__exact
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:
# Get blogs entries with id 1, 4 and 7 >>> Blog.objects.filter(pk__in=[1,4,7]) # Get all blog entries with id > 14 >>> Blog.objects.filter(pk__gt=14)
pk
lookups also work across joins. For example, these three statements are equivalent:
>>> Entry.objects.filter(blog__id__exact=3) # Explicit form >>> Entry.objects.filter(blog__id=3) # __exact is implied >>> Entry.objects.filter(blog__pk=3) # __pk implies __id__exact
The field lookups that equate to LIKE
SQL statements (iexact
, contains
, icontains
, startswith
, istartswith
, endswith
, and iendswith
) will automatically escape the two special characters used in LIKE
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:
>>> Entry.objects.filter(headline__contains='%')
Django takes care of the quoting for you; the resulting SQL will look something like this:
SELECT ... WHERE headline LIKE '%\%%';
Same goes for underscores. Both percentage signs and underscores are handled for you transparently.
Each 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
class' cache and returns the results that have been explicitly requested (for example, 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 QuerySet
correctly. For example, the following will create two QuerySet
, evaluate them, and throw them away:
>>> print([e.headline for e in Entry.objects.all()]) >>> print([e.pub_date for e in Entry.objects.all()])
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:
>>> queryset = Entry.objects.all() >>> print([p.headline for p in queryset]) # Evaluate the query set. >>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation.
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:
>>> queryset = Entry.objects.all() >>> print queryset[5] # Queries the database >>> print queryset[5] # Queries the database again
However, if the entire queryset has already been evaluated, the cache will be checked instead:
>>> queryset = Entry.objects.all() >>> [entry for entry in queryset] # Queries the database >>> print queryset[5] # Uses cache >>> print queryset[5] # Uses cache
Here are some examples of other actions that will result in the entire queryset being evaluated and therefore populate the cache:
>>> [entry for entry in queryset] >>> bool(queryset) >>> entry in queryset >>> list(queryset)