PYTHON / DJANGO
The ORM: querysets and filters
Build, chain, and evaluate Django QuerySets with filter, exclude, and field lookups, and know exactly when a query hits the database.
What you will learn
- Chain filter() and exclude() to compose one SQL WHERE clause
- Use double-underscore lookups like pages__gt or title__startswith
- Name the moment a QuerySet is evaluated and count the queries it costs
- Combine OR conditions with Q objects instead of two filter() calls
Understanding The ORM: querysets and filters
Book.objects.filter(pages__gt=300) does not touch the database. It returns a QuerySet, which is an object holding a description of a SQL query: which table, which WHERE conditions, which ORDER BY. Because it is only a description, you can keep refining it, and every refinement returns a brand new QuerySet rather than mutating the old one. That immutability is why long_books stays unchanged after you call long_books.filter(...) and assign the result to a different name.
Conditions are expressed as keyword arguments whose name encodes a field and a lookup, separated by a double underscore: pages__gt=300 becomes pages > 300, title__startswith='Python' becomes a LIKE clause, author__in=['Bader'] becomes IN. Several keyword arguments in one filter() call are ANDed together, and exclude() wraps its conditions in NOT. Since these compile to SQL, the comparison happens in the database engine, not in Python, so lookups only work on real model fields, never on Python properties or methods you added to the model.
The SQL runs at the moment you need actual rows: iterating the QuerySet, calling list() or len() on it, testing it with bool(), slicing with a step, or asking for its repr in a shell. When that happens the results are stored in the QuerySet's internal cache, so iterating the same QuerySet twice costs one query. Anything that derives a new QuerySet, such as another filter() or a different order_by(), starts with an empty cache and will run its own query. count(), exists(), first(), and update() bypass the cache entirely and always send a statement of their own.
import django
from django.conf import settings
settings.configure(
DEBUG=True,
INSTALLED_APPS=['__main__'],
DATABASES={'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': ':memory:'}},
)
django.setup()
from django.db import connection, models
class Book(models.Model):
title = models.CharField(max_length=100)
author = models.CharField(max_length=50)
pages = models.IntegerField()
with connection.schema_editor() as editor:
editor.create_model(Book)
Book.objects.bulk_create([
Book(title='Fluent Python', author='Ramalho', pages=792),
Book(title='Python Tricks', author='Bader', pages=302),
Book(title='Clean Code', author='Martin', pages=464),
Book(title='Django for APIs', author='Vincent', pages=256),
])
base = len(connection.queries)
long_books = Book.objects.filter(pages__gt=300)
print('queries after building filter:', len(connection.queries) - base)
python_long = long_books.filter(title__startswith='Python').order_by('-pages')
print('queries after chaining:', len(connection.queries) - base)
print(list(python_long.values_list('title', 'pages')))
print('queries after list():', len(connection.queries) - base)
print(long_books.count(), long_books.exclude(author='Martin').count())A QuerySet is a lazy, immutable description of a SQL query, so filtering is free and only evaluation costs a database round trip.
Worked examples
OR conditions with Q, and exclude
Shows that keyword filters can only be ANDed, so an OR needs Q objects, while exclude inverts a condition.
import django
from django.conf import settings
settings.configure(
INSTALLED_APPS=['__main__'],
DATABASES={'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': ':memory:'}},
)
django.setup()
from django.db import connection, models
from django.db.models import Q
class Book(models.Model):
title = models.CharField(max_length=100)
author = models.CharField(max_length=50)
pages = models.IntegerField()
with connection.schema_editor() as editor:
editor.create_model(Book)
Book.objects.bulk_create([
Book(title='Fluent Python', author='Ramalho', pages=792),
Book(title='Python Tricks', author='Bader', pages=302),
Book(title='Clean Code', author='Martin', pages=464),
Book(title='Django for APIs', author='Vincent', pages=256),
])
hits = Book.objects.filter(Q(pages__gt=700) | Q(author='Bader')).order_by('title')
print(list(hits.values_list('title', 'author')))
print(list(Book.objects.exclude(pages__gte=300).values_list('title', flat=True)))
print(Book.objects.filter(title__icontains='python').count())Example explained
Line 1Q(pages__gt=700) | Q(author='Bader') produces a single WHERE clause with OR, which plain keyword arguments cannot express.
Line 2exclude(pages__gte=300) becomes NOT (pages >= 300), so only the 256-page book survives.
Line 3values_list('title', flat=True) returns bare strings instead of one-element tuples.
Line 4icontains asks the database for a case-insensitive LIKE, which is why lowercase 'python' matches two titles.
The result cache versus a new QuerySet
Counts real SQL statements to show that reusing an evaluated QuerySet is free but deriving a new one is not.
import django
from django.conf import settings
settings.configure(
DEBUG=True,
INSTALLED_APPS=['__main__'],
DATABASES={'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': ':memory:'}},
)
django.setup()
from django.db import connection, models
class Book(models.Model):
title = models.CharField(max_length=100)
author = models.CharField(max_length=50)
pages = models.IntegerField()
with connection.schema_editor() as editor:
editor.create_model(Book)
Book.objects.bulk_create([
Book(title='Fluent Python', author='Ramalho', pages=792),
Book(title='Python Tricks', author='Bader', pages=302),
Book(title='Clean Code', author='Martin', pages=464),
Book(title='Django for APIs', author='Vincent', pages=256),
])
base = len(connection.queries)
short = Book.objects.filter(pages__lt=500).order_by('pages')
print([b.title for b in short])
print([b.title for b in short])
print(len(connection.queries) - base)
print([b.title for b in short[:2]])
print(len(connection.queries) - base)
by_bader = short.filter(author='Bader')
print(by_bader.count(), len(connection.queries) - base)Example explained
Line 1The first iteration runs the SELECT and fills short._result_cache.
Line 2The second iteration reads that cache, so the query counter is still 1.
Line 3short[:2] also comes from the cache because the QuerySet was already evaluated; slicing an unevaluated QuerySet would instead add LIMIT to the SQL.
Line 4short.filter(author='Bader') is a different QuerySet object with an empty cache, so count() sends a second statement.
Important notes
Lookups run in the database, so case sensitivity of contains and startswith depends on the backend: SQLite's LIKE is case-insensitive for ASCII while PostgreSQL's is not. Use icontains and istartswith when you want case-insensitivity guaranteed.
QuerySets do not support negative indexing, so qs[-1] raises; order by the reverse field and take qs[0], or call qs.last().
Common mistakes
Writing qs.filter(author='Bader') on its own line and expecting qs to change; filter() returns a new QuerySet, so the discarded return value means the extra condition never appears in the SQL.
Misspelling the separator, as in filter(pages_gt=300) or filter(pages__greaterthan=300); Django reads the name as a field or unknown lookup and raises FieldError instead of silently ignoring it.
Calling qs.count() to test emptiness and then iterating qs; count() never fills the result cache, so you pay two round trips where bool(qs) or exists() plus one iteration would do.
Try it yourself
Change, predict, then run
Using the Book table from the main example, build a QuerySet of books with more than 250 pages that are not by Martin, ordered from longest to shortest, and print the titles together with the number of SQL statements that ordering and filtering cost.
Open the Python workspaceCheck your understanding
You write qs = Book.objects.filter(pages__gt=300), then on the next line qs.filter(author='Bader') with no assignment, then iterate qs. What do you get?
- Every book over 300 pages, because filter() returns a new QuerySet and leaves qs untouched
- Only Bader's books over 300 pages, because filter() calls accumulate on the QuerySet
- Every book over 300 pages, but two SELECT statements run because filter() was called twice
- A FieldError, because a QuerySet cannot be filtered again once it has been assigned
Show answer
filter() clones the QuerySet and returns the clone, so an unassigned call builds an object that is immediately discarded and qs still describes only pages > 300. Option 2 is tempting because chaining looks like mutation, but chaining only works when you keep the returned value; option 3 is also wrong because the discarded QuerySet is never evaluated and therefore never queries the database.