Skip to content

Making Queries

Django's ORM builds the SQL for us from method calls on a model, and returns the rows as instances of the model.

QuerySets

In the shell:

Python REPL
>>> Animal.objects.all()
<QuerySet [<Animal: Rocket>, <Animal: Peanut>, <Animal: Smokey>]>

Every query starts from Animal.objects. Its all() returns a QuerySet. That represents a query against the model's table and, once it runs, the rows it returns. The shell shows each row using the animal's __str__.

We didn't ask for an order, so the database returns the rows in whatever order it finds them.

Filtering with filter() and exclude()

We often need only some of the rows, such as the animals of one species:

Python REPL
>>> Animal.objects.filter(species="cat")
<QuerySet [<Animal: Peanut>, <Animal: Smokey>]>
>>> Animal.objects.exclude(species="cat")
<QuerySet [<Animal: Rocket>]>

Each keyword argument to filter() becomes a condition in the SQL's WHERE clause, and only the rows that match come back. exclude() keeps the rows that don't match. Both return a QuerySet, and count() on either runs a SELECT COUNT(*).

Python REPL
>>> Animal.objects.filter(species="cat").count()
2

More than one keyword argument means every condition has to match, joined with AND:

Python REPL
>>> Animal.objects.filter(species="cat", status="available")
<QuerySet [<Animal: Peanut>]>

You may also see Q(), which provides OR and NOT. F() refers to another field in the same row, so a query can compare two fields or update a value without reading it first.

Getting One Row with get()

When we need exactly one row, such as the animal a page is about, we use get():

Python REPL
>>> rocket = Animal.objects.get(name="Rocket")
>>> rocket.breed
'Beagle mix'
>>> rocket.age_label()
'Adult'

get() returns a model instance, not a QuerySet, so the model's methods are there to call. If no row matches, or more than one does, it raises an exception:

Python REPL
>>> Animal.objects.get(name="Nemo")
Traceback (most recent call last):
  ...
animals.models.Animal.DoesNotExist: Animal matching query does not exist.
>>> Animal.objects.get(species="cat")
Traceback (most recent call last):
  ...
animals.models.Animal.MultipleObjectsReturned: get() returned more than one Animal -- it returned 2!

filter() doesn't raise in either case: a filter that matches nothing returns an empty QuerySet.

Field Lookups

An exact match only goes so far. To find the animals whose names start with "P", we add a lookup to the field's name:

Python REPL
>>> Animal.objects.filter(name__startswith="P")
<QuerySet [<Animal: Peanut>]>
>>> Animal.objects.filter(breed__icontains="beagle")
<QuerySet [<Animal: Rocket>]>
>>> Animal.objects.filter(birth_date__lt=today - 2 * year)
<QuerySet [<Animal: Rocket>, <Animal: Smokey>]>

A field lookup is the field's name, two underscores, and the lookup. filter(species="cat") is short for filter(species__exact="cat"). The last query finds the animals born more than two years ago.

The lookups we'll use most
  • exact and iexact: Equal to the value. iexact ignores case.
  • contains and icontains: Contains the value. icontains ignores case.
  • startswith: Starts with the value.
  • in: Equal to any value in a list, such as species__in=["cat", "rabbit"].
  • gt and gte: Greater than, and greater than or equal to, for numbers and dates.
  • lt and lte: Less than, and less than or equal to, for numbers and dates.
  • isnull: True matches the rows where the column is NULL, and False the rows where it isn't.

Django's documentation lists every field lookup.

Ordering with order_by()

To sort the results, we pass field names to order_by():

Python REPL
>>> Animal.objects.order_by("birth_date")
<QuerySet [<Animal: Smokey>, <Animal: Rocket>, <Animal: Peanut>]>

The oldest animal comes first. A - in front of a field's name sorts it in descending order. When we pass more than one field, each one breaks the ties left by the field before it.

To get only the first few rows, we slice the QuerySet:

Python REPL
>>> Animal.objects.order_by("birth_date")[:2]
<QuerySet [<Animal: Smokey>, <Animal: Rocket>]>

Slicing adds a LIMIT to the SQL, so the database returns only those rows, not every row for Python to cut down.