Making Queries

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Django 6.1 (6.1.1 on PyPI; 6.1.2 is scheduled for 2026-10-06), Python 3.12-3.14 and PostgreSQL 15+, checked on 2026-10-03; where Django 5.2 LTS behaves differently the text says so. The listings were run in a scratch project on Python 3.13.16 and Django 6.1.1 with SQLite: manage.py check, four tests (manage.py test), a curl session against runserver and every shell example below passed, and the output shown is the real output. Examples that depend on a PostgreSQL feature say so; none of the code needs anything but SQLite.

The ORM is the part of Django that turns Python method calls into SQL. Its central object is the QuerySet: a lazy, chainable, cached description of a query. This page shows how to build one, when it actually reaches the database, how to filter across relationships, how to express "compare this column with that column" (F) and "this or that" (Q), how to create, update and delete rows singly and in bulk, how to shape results (values, alias, exists, contains) and how to teach the ORM new lookups. Defining the fields and relations being queried is covered on Models & Fields and Relationships; aggregation, subqueries and database functions are on Aggregation, Expressions & Functions; reusable query methods are on Managers & Custom QuerySets; and avoiding the N+1 query problem is the subject of Query Optimization & Fetch Modes. For the SQL side of finding slow queries, see Diagnosing slow queries.

The Bookshelf models used on this page

Every example queries the same three small models of the Bookshelf catalogue, plus Genre for a many-to-many relation. The Meta.ordering on Book matters later (default ordering interacts with first(), union() and slicing):

catalog/models.py
from django.conf import settings
from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=200)
    country = models.CharField(max_length=2, blank=True)

    def __str__(self):
        return self.name


class Genre(models.Model):
    name = models.CharField(max_length=100, unique=True)

    def __str__(self):
        return self.name


class Book(models.Model):
    title = models.CharField(max_length=200)
    isbn = models.CharField(max_length=13, unique=True)
    author = models.ForeignKey(Author, on_delete=models.PROTECT, related_name="books")
    genres = models.ManyToManyField(Genre, related_name="books", blank=True)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    pages = models.PositiveIntegerField(default=0)
    stock = models.PositiveIntegerField(default=0)
    reserved = models.PositiveIntegerField(default=0)
    published = models.DateField(null=True, blank=True)
    is_published = models.BooleanField(default=True)

    class Meta:
        ordering = ["title"]

    def __str__(self):
        return self.title


class Review(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="reviews")
    reviewer = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    rating = models.PositiveSmallIntegerField()
    body = models.TextField(blank=True)

    def __str__(self):
        return f"{self.book} ({self.rating}/5)"

The examples that print results run in python manage.py shell (since Django 5.2 the shell imports the project’s models automatically, but the listings import everything explicitly so they also run as plain scripts after django.setup()). They start from a seeded database with eight books, four authors, three genres and four reviews; the seed script is ordinary create() calls and is not shown. A titles() helper that sorts the titles of a QuerySet keeps the output deterministic, because without order_by() row order is not guaranteed.

Source: Making queries

QuerySets and laziness

A manager (Book.objects) is the entry point; calling all(), filter() and the other query methods on it returns a QuerySet. A QuerySet is a Python object that holds a query description, not rows. Three rules explain almost every ORM surprise:

  1. Building is free. filter(), exclude(), order_by(), annotate(), values(), slicing and the other "returns a new QuerySet" methods never touch the database.

  2. Chaining never mutates. Each of those methods returns a new QuerySet; the original is unchanged and can be reused as a base for other queries.

  3. Evaluation runs the query once and caches the rows. The first time you iterate, call len(), list(), bool() or repr(), index with a step, or pickle a QuerySet, Django executes the SQL and stores the instances in the QuerySet’s result cache; reusing that same object costs no query.

flowchart LR mgr["Book.objects
(manager)"] --> qs1["QuerySet
filter(...)"] qs1 -->|"exclude / order_by / annotate / slice
each returns a NEW QuerySet"| qs2["QuerySet
(still no SQL)"] qs2 -->|"iterate, list, len, bool, repr,
get, first, count, exists, aggregate"| db[("SQL runs")] db -->|"iteration / list / len / bool"| cache["result cache filled
(reuse is free)"] db -->|"get / first / count / exists / aggregate"| value["one object, number
or boolean (no cache)"]
Laziness, caching and slicing (ex/e1_lazy.py)
from django.db import connection, reset_queries
from django.test.utils import CaptureQueriesContext
from catalog.models import Book

with CaptureQueriesContext(connection) as ctx:
    qs = Book.objects.filter(is_published=True)       # builds a query, runs nothing
    qs = qs.exclude(stock=0).order_by("-price")       # each call returns a NEW QuerySet
    print("queries after building:", len(ctx))
    print(qs.query)                                   # the SQL that would run
    titles = [b.title for b in qs]                    # iteration evaluates and fills the cache
    print("queries after iterating:", len(ctx))
    len(qs); bool(qs); list(qs)                       # served from the result cache
    print("queries after reusing cache:", len(ctx))
    qs.count()                                        # a fully loaded QuerySet answers from its cache
    print("queries after count() on a loaded QuerySet:", len(ctx))
    Book.objects.filter(is_published=True).count()    # a fresh QuerySet issues SELECT COUNT(*)
    print("queries after count() on a fresh QuerySet:", len(ctx))
print(titles)

# slicing an unevaluated QuerySet adds LIMIT/OFFSET and returns another QuerySet
page = Book.objects.order_by("title")[2:5]
print(type(page).__name__, "|", page.query)
# a step makes Django evaluate the slice immediately and return a list
print(type(Book.objects.order_by("title")[::2]).__name__)

# QuerySets are immutable: filter() never changes the original
base = Book.objects.all()
cheap = base.filter(price__lt=10)
print(base.count(), cheap.count())
queries after building: 0
SELECT "catalog_book"."id", "catalog_book"."title", ... FROM "catalog_book" WHERE ("catalog_book"."is_published" = True AND NOT ("catalog_book"."stock" = 0)) ORDER BY "catalog_book"."price" DESC
queries after iterating: 1
queries after reusing cache: 1
queries after count() on a loaded QuerySet: 1
queries after count() on a fresh QuerySet: 2
['Kindred', 'The Cyberiad', 'Solaris', 'The Dispossessed', 'Parable of the Sower', 'A Wizard of Earthsea']
QuerySet | SELECT "catalog_book"."id", ... FROM "catalog_book" ORDER BY "catalog_book"."title" ASC LIMIT 3 OFFSET 2
list
8 4

(The long column list is abridged with an ellipsis.) Source: QuerySets are lazy and When QuerySets are evaluated

The details that matter in practice:

  • Slicing. qs[2:5] on an unevaluated QuerySet adds LIMIT 3 OFFSET 2 and returns another QuerySet. A step (qs[::2]) forces evaluation and returns a list. Negative indices are not supported (ValueError: Negative indexing is not supported.), and a sliced QuerySet cannot be filtered further (TypeError: Cannot filter a query once a slice has been taken.) or deleted.

  • str(qs.query) prints the SQL that would run (parameters inlined, for reading only). Do not execute it. For the SQL a block actually executed, wrap it in CaptureQueriesContext, as the listings here do, or use the Debug Toolbar.

  • The cache belongs to the object. Book.objects.filter(…​) called twice builds two QuerySets and runs two queries. Keep the QuerySet in a variable if you will iterate it twice, and pass it to the template rather than a list copy.

  • Which methods use the cache. len(), iteration, bool() and list() evaluate and cache. count() on a QuerySet that is already loaded returns the length of the cache; on a fresh one it runs SELECT COUNT(*). exists() and contains() use the cache when they can and otherwise ask the database for one row.

  • if qs: is a hidden SELECT *. Truthiness evaluates the whole QuerySet. Use qs.exists() when you only need to know if there is a row, qs.count() when you only need the number, and len(qs) only when you will iterate afterwards.

  • iterator() streams rows in chunks and bypasses the cache; it is the tool for very large results (see below).

Where a query runs in the request/response cycle

A QuerySet is evaluated where it is first consumed, which is not always where it was written. A view that returns Book.objects.all() in the template context queries when the template loop starts, after the view has returned; a QuerySet stored at module level is shared by every request and its result cache would be reused forever, so build QuerySets inside the view (or call .all() on a stored one, which returns a fresh copy). The database connection is per thread and managed by Django: opened lazily on the first query of a request and closed at the end of the request unless CONN_MAX_AGE keeps it open (CONN_HEALTH_CHECKS can validate a persistent connection before reuse). With ATOMIC_REQUESTS = True the whole view runs in one transaction, which is covered on Transactions, Raw SQL & Multiple Databases. The complete view and tests at the end of this page show the cycle in action.

Retrieving objects: all, filter, exclude and get

Method Behaviour

all()

A QuerySet for every row (a copy; evaluation is still lazy).

filter(**lookups)

Rows matching all the keyword conditions (joined with AND). Arguments can also be Q objects (below).

exclude(**lookups)

Rows that do not match the combined conditions (NOT (a AND b)).

get(**lookups)

Exactly one object, as an instance, not a QuerySet. Raises Model.DoesNotExist for zero rows and Model.MultipleObjectsReturned for more than one (both subclass ObjectDoesNotExist / MultipleObjectsReturned).

first() / last()

The first or last object by ordering (falling back to the primary key when the QuerySet is unordered), or None. Since 6.1 they no longer add a primary-key ordering when you cleared the ordering with a bare order_by().

earliest(field) / latest(field)

The object with the smallest or largest value of a field; raises DoesNotExist when there are no rows.

order_by(*fields) / reverse()

Ordering; a leading - is descending; F("x").desc(nulls_last=True) controls NULL placement.

distinct()

SELECT DISTINCT; needed when a join on a multi-valued relation repeats rows.

none()

An always-empty QuerySet that never queries; useful for permission filters.

in_bulk(id_list, field_name="pk")

A dict mapping the field value to the instance, in one query. 6.1 allows chaining it after values() / values_list().
get(), DoesNotExist, MultipleObjectsReturned and friends (ex/e2_retrieve.py)
from django.db.models import F
from catalog.models import Author, Book

print(Book.objects.get(isbn="9780156027601"))                 # exactly one row
try:
    Book.objects.get(title="Nonexistent")
except Book.DoesNotExist as exc:
    print("DoesNotExist:", exc)
try:
    Book.objects.get(author__country="US")
except Book.MultipleObjectsReturned as exc:
    print("MultipleObjectsReturned:", exc)

print(Book.objects.filter(is_published=True).count(), Book.objects.exclude(is_published=True).count())
dated = Book.objects.filter(published__isnull=False)
print(dated.earliest("published"), "|", dated.latest("published"), "|", dated.order_by("-price").first())
# NULL ordering is database specific: say where the NULLs go
print([b.title for b in Book.objects.order_by(F("published").desc(nulls_last=True))][-1])
print(Book.objects.none().exists(), Book.objects.filter(pk__in=[]).count())
print(list(Book.objects.values_list("author__country", flat=True).distinct().order_by("author__country")))
print(Book.objects.in_bulk(field_name="isbn").keys())
Solaris
DoesNotExist: Book matching query does not exist.
MultipleObjectsReturned: get() returned more than one Book -- it returned 6!
7 1
Solaris | Parable of the Sower | Kindred
Draft Essays
False 0
['PL', 'US']
dict_keys(['9780547773742', '9780000000001', '9780807083697', '9780446675505', '9780156027601', '9780156027595', '9780061054884', '9780441478125'])

Source: Retrieving objects and Methods that return new QuerySets

Two details are visible in the output. order_by("-published") puts the book with no publication date last on SQLite, because SQLite treats NULL as the smallest value, while PostgreSQL treats NULL as the largest value; the explicit nulls_last=True makes the result the same on every database (the last line shows the undated book at the end of the descending order). And get() is for lookups that are unique by design (pk, a unique field); use filter(…​).first() when "no row" is normal and several rows are acceptable. In a view, get_object_or_404() converts DoesNotExist into an HTTP 404 (see Function-Based Views & Shortcuts).

Field lookups

A lookup is the suffix that says how to compare a field. title="x" is shorthand for titleexact="x". The full form is fieldlookup=value, optionally with transforms in between (publishedyear__gte=1990 is the year transform followed by the gte lookup).

Lookup Meaning

exact, iexact

Equality; iexact is case-insensitive. exact=None is IS NULL.

contains, icontains

Substring (LIKE '%x%'), case-sensitive or not. On SQLite LIKE is case-insensitive for ASCII text, so contains behaves like icontains there; PostgreSQL honours the difference.

startswith, istartswith, endswith, iendswith

Prefix and suffix matches.

in

Membership in a list, tuple or another QuerySet (which becomes a subquery).

gt, gte, lt, lte

Comparisons.

range

BETWEEN low AND high (inclusive); works for numbers, dates and datetimes.

isnull

True or False only; non-boolean right-hand values, which were never documented, stopped being accepted in Django 4.0.

regex, iregex

Regular-expression match, using the database’s regex dialect.

date, year, iso_year, month, day, week, week_day, iso_week_day, quarter, time, hour, minute, second

Date and time component transforms that can be followed by another lookup (publishedyearlt=1965). Datetime fields follow the current time zone when USE_TZ is true.
Common lookups, transforms and subqueries (ex/e3_lookups.py)
import datetime as dt
from django.db.models import F, Q
from catalog.models import Author, Book

def titles(qs):
    return sorted(b.title for b in qs)

print(titles(Book.objects.filter(title__exact="Solaris")))
print(titles(Book.objects.filter(title__iexact="solaris")))
print(titles(Book.objects.filter(title__contains="Earth")), titles(Book.objects.filter(title__icontains="the d")))
print(titles(Book.objects.filter(title__startswith="The "))[:5], titles(Book.objects.filter(title__endswith="Sower")))
wanted = list(Book.objects.filter(title__startswith="S").values_list("pk", flat=True))
print(titles(Book.objects.filter(pk__in=wanted)))
print(titles(Book.objects.filter(price__gte=11)), titles(Book.objects.filter(price__range=(9, 10))))
print(titles(Book.objects.filter(published__year=1969)), titles(Book.objects.filter(published__year__lt=1965)))
print(titles(Book.objects.filter(published__month=11)), titles(Book.objects.filter(published__gt=dt.date(1975, 1, 1))))
print(titles(Book.objects.filter(published__isnull=True)))
print(titles(Book.objects.filter(title__regex=r"^The (Left|Cyb)")))
print(titles(Book.objects.filter(published__year__gte=1990)))   # transform (year) then lookup (gte)
# a queryset as the right-hand side becomes a subquery
cheap_authors = Author.objects.filter(books__price__lt=9).values("pk")
print(titles(Book.objects.filter(author__in=cheap_authors)))
# lookups on annotations and in filters directly (Django 4.0+)
from django.db.models import Value
from django.db.models.lookups import GreaterThan
print(titles(Book.objects.filter(GreaterThan(F("pages"), Value(300)))))
print(Book.objects.filter(GreaterThan(F("pages"), 300)).query.where)
['Solaris']
['Solaris']
['A Wizard of Earthsea'] ['The Dispossessed']
['The Cyberiad', 'The Dispossessed', 'The Left Hand of Darkness'] ['Parable of the Sower']
['Solaris']
['Kindred', 'The Cyberiad', 'The Left Hand of Darkness'] ['Parable of the Sower', 'The Dispossessed']
['The Left Hand of Darkness'] ['Solaris']
['A Wizard of Earthsea'] ['Kindred', 'Parable of the Sower']
['Draft Essays']
['The Cyberiad', 'The Left Hand of Darkness']
['Parable of the Sower']
['A Wizard of Earthsea', 'Draft Essays', 'The Dispossessed', 'The Left Hand of Darkness']
['Parable of the Sower', 'The Dispossessed', 'The Left Hand of Darkness']
(AND: GreaterThan(Col(catalog_book, catalog.Book.pages), Value(300)))

Source: Field lookups and Lookup API reference

Notes on the last two examples: a QuerySet used as the right-hand side of in is compiled into a SQL subquery (it should select a single column, hence .values("pk")), so no intermediate list is built in Python. Lookup expressions such as GreaterThan(F("pages"), 300) can be passed directly to filter() and used in annotations since Django 4.0. pk is an alias for the primary-key field, and pkin is the idiomatic bulk lookup.

Spanning relationships

Double underscores also walk relationships, forwards (authorname), backwards (bookstitle, using related_name or, by default, the model name in lowercase; set related_query_name to change the query name independently) and through many-to-many tables (genres__name). Django adds the `JOIN`s itself. See Relationships for how the relations are declared.

Following relationships in filters (ex/e4_relations.py)
from django.db import connection
from django.test.utils import CaptureQueriesContext
from catalog.models import Author, Book, Genre, Review

def names(qs, attr="title"):
    return sorted(getattr(o, attr) for o in qs)

# forward: follow the ForeignKey with double underscores
print(names(Book.objects.filter(author__name__startswith="Stan")))
# backward: related_name="books" is also the default query name
print(names(Author.objects.filter(books__price__gt=12), "name"))
# many-to-many in both directions
print(names(Book.objects.filter(genres__name="Fantasy")), names(Genre.objects.filter(books__title="Kindred"), "name"))
# across two hops, and to a model without a reverse name (reviews -> reviewer)
print(names(Book.objects.filter(reviews__reviewer__username="ana")))
# isnull across a reverse relation = "has no rows"
print(names(Book.objects.filter(reviews__isnull=True)))
print(names(Author.objects.filter(books__isnull=True), "name"))

# multi-valued relations: one filter() call = one related row must satisfy ALL conditions
one_call = Book.objects.filter(reviews__rating=5, reviews__body="Dense but rewarding")
# chained filter() calls = conditions may be satisfied by DIFFERENT related rows
chained = Book.objects.filter(reviews__rating=5).filter(reviews__body="Dense but rewarding")
print("one call:", names(one_call), "| chained:", names(chained))
print(chained.query.__str__().count("JOIN"), "joins when chained;", one_call.query.__str__().count("JOIN"), "when in one call")

# exclude() on a multi-valued relation is a subquery, and does NOT behave like filter() negated
print(names(Book.objects.exclude(reviews__rating=5, reviews__body="Brilliant")))

# duplicates: a join on a multi-valued relation can repeat the parent row
print(Book.objects.filter(reviews__rating__gte=3).count(), Book.objects.filter(reviews__rating__gte=3).distinct().count())

# related managers are QuerySets scoped to one object
le_guin = Author.objects.get(name__startswith="Ursula")
print(le_guin.books.filter(price__lt=10).count(), le_guin.books.count())
solaris = Book.objects.get(title="Solaris")
print(solaris.reviews.all(), solaris.genres.all())
# traversing from an instance costs a query per access (see the optimisation page)
with CaptureQueriesContext(connection) as ctx:
    for book in Book.objects.all():
        book.author.name
print("queries for the loop:", len(ctx))
['Solaris', 'The Cyberiad']
['Octavia E. Butler']
['A Wizard of Earthsea', 'Kindred'] ['Fantasy', 'Science fiction']
['Solaris', 'The Dispossessed']
['A Wizard of Earthsea', 'Draft Essays', 'Parable of the Sower', 'The Cyberiad', 'The Left Hand of Darkness']
['Unpublished Author']
one call: [] | chained: ['The Dispossessed']
2 joins when chained; 1 when in one call
['A Wizard of Earthsea', 'Draft Essays', 'Kindred', 'Parable of the Sower', 'Solaris', 'The Cyberiad', 'The Left Hand of Darkness']
4 3
3 4
<QuerySet [<Review: Solaris (5/5)>]> <QuerySet [<Genre: Science fiction>]>
queries for the loop: 9

Source: Lookups that span relationships and Spanning multi-valued relationships

Three behaviours in this output decide whether a query is correct:

  • One filter() call versus a chain. With a multi-valued relation (reverse foreign key, many-to-many), conditions in a single filter() call must be satisfied by the same related row, while conditions in chained filter() calls may be satisfied by different related rows. Here no single review is both a 5 and "Dense but rewarding", but The Dispossessed has one review with rating 5 and another with that text, so the chain matches it. The SQL differs accordingly (the chain adds a second JOIN).

  • exclude() does not mirror filter(). On a multi-valued relation exclude(reviewsrating=5, reviewsbody="Brilliant") removes books that have a review matching both conditions; Django builds it with a subquery, and the result here removes only The Dispossessed, whereas exclude(a).exclude(b) would remove every book with a 5 or a "Brilliant" review.

  • Joins repeat rows. Filtering on reviewsratinggte=3 joins Book to Review, so a book with two matching reviews appears twice (4 rows versus 3 with distinct()). Use distinct(), or filter with a subquery (pk__in= or Exists()) when the duplicates matter.

The related manager (author.books, book.reviews, book.genres) is a manager scoped to one object: le_guin.books.filter(price__lt=10) is a normal QuerySet. Every attribute traversal from an instance (book.author) is a separate query when the object was not loaded with the relation: the last loop above issues nine queries for eight books. That is the N+1 problem; the cures (select_related, prefetch_related and the Django 6.1 fetch modes) are on Query Optimization & Fetch Modes.

F expressions: referring to columns

An F("field") object refers to the value of a column inside the database instead of a Python value that was read earlier. It has three jobs: comparing two columns of one row, computing a new value from the current one, and ordering or annotating by an expression. Because the arithmetic happens in SQL, an F() update is atomic with respect to other writers and avoids the lost-update race of "read, add one in Python, save".

F expressions in filters, updates and annotations (ex/e5_fq.py)
from django.db import connection
from django.db.models import F, FloatField
from django.db.models.functions import Cast
from django.test.utils import CaptureQueriesContext
from catalog.models import Book

def titles(qs):
    return sorted(b.title for b in qs)

# F: compare two columns of the same row, inside the database
Book.objects.filter(title="Kindred").update(reserved=5)
Book.objects.filter(title="Solaris").update(reserved=1)
print(titles(Book.objects.filter(reserved__gte=F("stock"))))

# F in update(): one atomic UPDATE ... SET stock = stock - 1
with CaptureQueriesContext(connection) as ctx:
    Book.objects.filter(title="Solaris").update(stock=F("stock") - 1)
print(ctx[0]["sql"])

# the read-modify-write race that F avoids
book = Book.objects.get(title="Solaris")
book.stock = F("stock") + 10      # an expression is saved as SQL, not as a Python number
book.save(update_fields=["stock"])
print(book.stock)                  # 6.0+: reloaded from the database after save() on SQLite/PostgreSQL/Oracle
book.refresh_from_db(fields=["stock"])   # explicit reload; needed on MySQL/MariaDB or before Django 6.0
print(book.stock)

# F in annotate(), order_by() and arithmetic
qs = Book.objects.annotate(per_page=Cast("price", FloatField()) / F("pages")).order_by("-per_page")[:2]
print([(b.title, round(b.per_page, 4)) for b in qs])
print(titles(Book.objects.order_by(F("published").desc(nulls_last=True))[:2]))
['Kindred', 'The Left Hand of Darkness']
UPDATE "catalog_book" SET "stock" = ("catalog_book"."stock" - 1) WHERE "catalog_book"."title" = 'Solaris'
16
16
[('Draft Essays', 0.0625), ('Kindred', 0.0511)]
['Kindred', 'Parable of the Sower']

Source: F() expressions and Filters can reference fields on the model

Points worth knowing:

  • After save() an F-assigned attribute holds the new value only on some databases. Since Django 6.0, fields assigned expressions (and GeneratedField`s) are refreshed from the database after `save() on backends with a RETURNING clause (SQLite, PostgreSQL, Oracle) and marked deferred on MySQL/MariaDB; before 6.0 the attribute stayed an F object until refresh_from_db(). Calling refresh_from_db(fields=[…​]) works everywhere.

  • Do not save an instance again while it still holds an F-expression (MySQL/MariaDB, or any backend before 6.0, until you call refresh_from_db()): the expression would be applied a second time.

  • Division and SQLite. The per-page price uses Cast("price", FloatField()). Dividing the raw decimal column by an integer column is an integer division on SQLite when the stored price happens to be a whole number (a price of 5.00 divided by 80 pages gave 0 in the scratch project), so cast one operand explicitly; other databases return decimals.

  • F() can be negated for booleans (~F("is_published"), Django 4.2), used with transforms and in OuterRef() since 3.2, and F() of a character field can be sliced since 5.1.

  • NULLs. F("a") + 1 is NULL when a is NULL; wrap nullable columns in Coalesce(F("a"), 0).

Q objects: AND, OR, NOT and XOR

Keyword arguments to filter() are always AND`ed. A `Q() object wraps the same keyword conditions so they can be combined with & (and), | (or), ~ (not) and, since Django 4.1, ^ (exclusive or: true when an odd number of the operands is true). Q objects and keyword arguments can be mixed, but the Q objects must come first.

Combining conditions with Q (ex/e6_q.py)
import operator
from functools import reduce
from django.db.models import Q
from catalog.models import Book

def titles(qs):
    return sorted(b.title for b in qs)

scifi_or_cheap = Q(genres__name="Fantasy") | Q(price__lt=9)
print(titles(Book.objects.filter(scifi_or_cheap).distinct()))
print(titles(Book.objects.filter(Q(is_published=True) & ~Q(stock=0))))
print(titles(Book.objects.filter(~Q(author__country="US"))))

# XOR (Django 4.1+): true when an odd number of the conditions are true
xor = Book.objects.filter(Q(pages__gt=300) ^ Q(price__gt=10))
print(titles(xor))
print(xor.query.where)
print(str(Book.objects.filter(Q(pages__gt=300) ^ Q(price__gt=10)).query).split("WHERE")[1][:210])

# Q objects first, keyword arguments afterwards
print(titles(Book.objects.filter(Q(title__startswith="The") | Q(title__startswith="A"), is_published=True)))

# building a filter from user input without a chain of ifs
def search(*, text="", author_country="", max_price=None, in_stock=False):
    q = Q()
    if text:
        q &= Q(title__icontains=text) | Q(author__name__icontains=text)
    if author_country:
        q &= Q(author__country=author_country)
    if max_price is not None:
        q &= Q(price__lte=max_price)
    if in_stock:
        q &= Q(stock__gt=0)
    return Book.objects.filter(q)

print(titles(search(text="le guin", in_stock=True)), titles(search(author_country="PL", max_price=11)))
print(search().count())                       # an empty Q() matches everything

# combining a list of terms
terms = ["earth", "sower", "kin"]
q = reduce(operator.or_, (Q(title__icontains=t) for t in terms))
print(titles(Book.objects.filter(q)))
# QuerySets combine with | & ^ as well
print(titles(Book.objects.filter(stock=0) | Book.objects.filter(pages__lt=100)))
['A Wizard of Earthsea', 'Draft Essays', 'Kindred']
['A Wizard of Earthsea', 'Kindred', 'Parable of the Sower', 'Solaris', 'The Cyberiad', 'The Dispossessed']
['Solaris', 'The Cyberiad']
['Kindred', 'Parable of the Sower', 'Solaris', 'The Cyberiad', 'The Dispossessed']
(AND: (XOR: IntegerGreaterThan(Col(catalog_book, catalog.Book.pages), 300), GreaterThan(Col(catalog_book, catalog.Book.price), Decimal('10'))))
 (("catalog_book"."pages" > 300 OR "catalog_book"."price" > 10) AND 1 = (CASE WHEN "catalog_book"."pages" > 300 THEN 1 ELSE 0 END + CASE WHEN "catalog_book"."price" > 10 THEN 1 ELSE 0 END)) ORDER BY ...
['A Wizard of Earthsea', 'The Cyberiad', 'The Dispossessed', 'The Left Hand of Darkness']
['A Wizard of Earthsea', 'Draft Essays', 'The Dispossessed'] ['Solaris']
8
['A Wizard of Earthsea', 'Kindred', 'Parable of the Sower']
['Draft Essays', 'The Left Hand of Darkness']

Source: Complex lookups with Q objects

  • XOR support. MariaDB and MySQL have a native XOR; on PostgreSQL and SQLite Django rewrites it, as the printed SQL shows (for more than two operands the official page defines the result as "an odd number of operands match" and documents the conversion).

  • An empty Q() matches everything, which makes q = Q() followed by q &= …​ a clean way to build filters from optional input; it is how the search_books() function below works.

  • ~Q(author__country="US") across a multi-valued relation uses the same subquery rule as exclude().

  • Q objects are reusable and picklable, so they can be defined once (for example as a default_filter constant or inside Meta.constraints, limit_choices_to, or a manager) and combined later.

  • Never build a Q from raw user-controlled keys (Q(**request.GET.dict())): the lookups a client can name include relations to other models (reviewsreviewerpassword__startswith=…​ is a valid lookup in these models). Whitelist the filters, or use a filter library (django-filter) that does.

get_or_create and update_or_create

get_or_create(defaults=None, kwargs) looks the row up with get(kwargs); if it does not exist it creates it from kwargs plus defaults and returns (object, created). update_or_create() does the same but, when the row exists, applies defaults as updates (via save()), and returns (object, created). Both are not single SQL statements: Django wraps them in a transaction (a savepoint when already inside one), and relies on a unique constraint to resolve races.

get_or_create and update_or_create (ex/e7_getorcreate.py)
from django.db import IntegrityError, connection
from django.test.utils import CaptureQueriesContext
from catalog.models import Author, Genre, Book

# get_or_create: lookups, plus defaults used only when creating
genre, created = Genre.objects.get_or_create(name="Poetry")
print(genre, created)
genre, created = Genre.objects.get_or_create(name="Poetry")
print(genre, created)

author, created = Author.objects.get_or_create(
    name="Italo Calvino", defaults={"country": "IT"},
)
print(author.country, created)
# existing row: defaults are ignored
author, created = Author.objects.get_or_create(name="Italo Calvino", defaults={"country": "XX"})
print(author.country, created)

# update_or_create: update the row if it exists, otherwise create it
book, created = Book.objects.update_or_create(
    isbn="9780156453806",
    defaults={"title": "Invisible Cities", "author": author, "price": "11.95", "pages": 165},
)
print(book, created)
book, created = Book.objects.update_or_create(isbn="9780156453806", defaults={"price": "12.95"})
print(book.price, created)

# create_defaults (Django 5.0): different values for the create branch than for the update branch
book, created = Book.objects.update_or_create(
    isbn="9780156453813",
    defaults={"stock": 5},                                                    # used on update
    create_defaults={"title": "If on a winter's night", "author": author,     # used on create
                     "price": "10.00", "pages": 260, "stock": 0},
)
print(book.title, book.stock, created)
book, created = Book.objects.update_or_create(
    isbn="9780156453813", defaults={"stock": 5},
    create_defaults={"title": "ignored", "author": author, "price": "1", "pages": 1},
)
print(book.title, book.stock, created)

# the lookup must be unique: get_or_create relies on get() and on a unique constraint for races
Author.objects.create(name="Twin"); Author.objects.create(name="Twin")
try:
    Author.objects.get_or_create(name="Twin")
except Author.MultipleObjectsReturned as exc:
    print("MultipleObjectsReturned:", exc)

# the SQL: get, then a savepoint + INSERT; an IntegrityError triggers one more get()
with CaptureQueriesContext(connection) as ctx:
    Genre.objects.get_or_create(name="Drama")
for q in ctx:
    print(q["sql"][:110])

# cleanup of rows created here
Author.objects.filter(name__in=["Twin", "Italo Calvino"]).exclude(books__isnull=False).delete()
Poetry True
Poetry False
IT True
IT False
Invisible Cities True
12.95 False
If on a winter's night 0 True
If on a winter's night 5 False
MultipleObjectsReturned: get() returned more than one Author -- it returned 2!
SELECT "catalog_genre"."id", "catalog_genre"."name" FROM "catalog_genre" WHERE "catalog_genre"."name" = 'Drama
BEGIN
INSERT INTO "catalog_genre" ("name") VALUES ('Drama') RETURNING "catalog_genre"."id"
COMMIT

Source: get_or_create() and update_or_create()

  • defaults is applied only on creation for get_or_create, and the keyword arguments are the lookup. Putting a non-lookup value (such as price) in the keyword arguments makes the lookup too narrow: the row exists with a different price and a duplicate is created or, with a unique column, an IntegrityError follows.

  • Concurrency needs a database-enforced unique constraint on the lookup fields. Without it two concurrent calls can both create a row, and later calls fail with MultipleObjectsReturned, as the "Twin" authors show. With a unique constraint, the loser of the race gets an IntegrityError that Django catches and answers with a second get().

  • create_defaults (Django 5.0) gives update_or_create() (and aupdate_or_create()) different values for the insert than for the update, so a "created" row can be built with fields that an "update" must not overwrite (the title and price above are only used on creation).

  • Callable defaults in defaults are called only when needed. update_or_create() uses save(), so signals and auto_now fields behave normally; on update it runs select_for_update() inside the transaction on databases that support it.

  • Do not use them in a loop for large imports. That is one to three queries per row; use bulk_create(update_conflicts=True) below.

  • In views build the lookup from validated input only; a get_or_create on request.POST data is a write triggered by user input and needs the same CSRF and validation care as any form handler.

Creating, updating and deleting in bulk

Operation What it does and what it skips

create(**kwargs) / save()

One INSERT or UPDATE; runs save() overrides, pre_save/post_save signals and field auto_now handling.

bulk_create(objs, batch_size=None, ignore_conflicts=False, update_conflicts=False, update_fields=None, unique_fields=None)

One INSERT (or several, per batch_size). Does not call save() or send signals, does not work with multi-table-inheritance children or many-to-many data, and casts objs to a list.

bulk_update(objs, fields, batch_size=None)

One UPDATE …​ CASE WHEN per batch; returns the number of rows matched (Django 4.0). No save() and no signals; cannot update the primary key.

update(**kwargs)

A single UPDATE …​ WHERE on the filtered rows; returns the number of rows matched. No save(), no signals, and auto_now fields are not refreshed unless you set them.

delete()

Deletes the matching rows (and, per on_delete, their dependants); returns the total and a per-model count dictionary. Cannot be called on a sliced QuerySet.
bulk_create, upserts, bulk_update, update and delete (ex/e8_bulk.py)
from django.db import connection
from django.db.models import F
from django.test.utils import CaptureQueriesContext
from catalog.models import Author, Book, Genre

author = Author.objects.get(name="Stanisław Lem")

# bulk_create: one INSERT for many rows; save() and signals are NOT called
with CaptureQueriesContext(connection) as ctx:
    created = Book.objects.bulk_create(
        [
            Book(title="His Master's Voice", isbn="9780156401739", author=author, price="10.00", pages=199),
            Book(title="Fiasco", isbn="9780156305471", author=author, price="10.50", pages=320),
        ],
    )
print(len(ctx), "query;", [b.pk is not None for b in created])

# ignore_conflicts: skip rows that violate a unique constraint (primary keys are NOT set on the instances)
dupes = [Book(title="Fiasco again", isbn="9780156305471", author=author, price="1.00", pages=1)]
Book.objects.bulk_create(dupes, ignore_conflicts=True)
print(Book.objects.filter(isbn="9780156305471").values_list("title", flat=True)[0], dupes[0].pk)

# update_conflicts (Django 4.1): an upsert -- INSERT ... ON CONFLICT (isbn) DO UPDATE SET price, stock
rows = [
    Book(title="Fiasco", isbn="9780156305471", author=author, price="9.50", pages=320, stock=3),   # existing row
    Book(title="Memoirs Found in a Bathtub", isbn="9780156586221", author=author, price="11.00", pages=216, stock=1),  # new row
]
with CaptureQueriesContext(connection) as ctx:
    Book.objects.bulk_create(rows, update_conflicts=True, unique_fields=["isbn"], update_fields=["price", "stock"])
print(ctx[-1]["sql"][:60], "...", ctx[-1]["sql"][-90:])
print(Book.objects.get(isbn="9780156305471").price, [b.pk is not None for b in rows])   # pk set with update_conflicts: Django 5.0+

# bulk_update: one UPDATE ... CASE WHEN ...; returns the number of rows matched
books = list(Book.objects.filter(author=author))
for book in books:
    book.price = book.price + 1
with CaptureQueriesContext(connection) as ctx:
    updated = Book.objects.bulk_update(books, ["price"], batch_size=100)
print(updated, len(ctx))

# update(): one UPDATE, no save(), no signals, no auto_now refresh; returns rows matched
print(Book.objects.filter(stock=0).update(is_published=False))
print(Book.objects.filter(author=author).update(reserved=F("stock")))

# delete(): returns (total, per-model counts) and cascades in Python (signals fire) or in SQL
total, per_model = Book.objects.filter(isbn__in=["9780156401739", "9780156305471", "9780156586221"]).delete()
print(total, per_model)
# a protected relation refuses
from django.db.models import ProtectedError
try:
    author.delete()
except ProtectedError as exc:
    print("ProtectedError:", str(exc)[:70])
# save() with update_fields touches only the named columns
b = Book.objects.get(title="Solaris"); b.title = "Solaris (rev.)"; b.pages = 1
b.save(update_fields=["title"])
print(Book.objects.filter(title="Solaris (rev.)").values_list("pages", flat=True)[0])
b.title = "Solaris"; b.pages = 204; b.save()
1 query; [True, True]
Fiasco None
INSERT INTO "catalog_book" ("title", "isbn", "author_id", "p ... E SET "price" = EXCLUDED."price", "stock" = EXCLUDED."stock" RETURNING "catalog_book"."id"
9.50 [True, True]
5 3
2
5
3 {'catalog.Book': 3}
ProtectedError: ("Cannot delete some instances of model 'Author' because they are refe
204

Source: bulk_create(), bulk_update(), update() and delete()

The upsert is the headline: bulk_create(…​, update_conflicts=True, unique_fields=[…​], update_fields=[…​]) compiles to INSERT …​ ON CONFLICT (isbn) DO UPDATE SET …​, so a nightly price import is one statement for any number of rows, instead of a get_or_create per row. The facts to keep in mind:

  • Added in Django 4.1 (confirmed in the 4.1 release notes: "QuerySet.bulk_create() now supports updating fields when a row insertion fails uniqueness constraints"), supported on MariaDB, MySQL, PostgreSQL and SQLite 3.24+; the 6.1 baseline is SQLite 3.37, so it is available everywhere Django 6.1 runs, except Oracle.

  • unique_fields is required on PostgreSQL and SQLite (the conflict target) and ignored on MySQL/MariaDB, which choose the target themselves; update_fields is the list of columns to overwrite. Give both on every backend so the code is portable.

  • Primary keys. With update_conflicts=True Django 5.0 and later sets the primary key on every instance when the database supports it (the [True, True] line); ignore_conflicts=True never sets primary keys (the duplicate instance shows None). Before 5.0 the keys were missing for upserts, and on SQLite bulk_create only returned primary keys from 4.0 (SQLite 3.35+).

  • ignore_conflicts swallows more than duplicates on MySQL and MariaDB (invalid values, non-nullable violations become warnings); the official page carries that warning.

  • Signals and validation are skipped. No save(), no pre_save/post_save, no model full_clean(); validate before inserting.

  • Batching. SQLite and Oracle limit the number of query parameters, so Django splits large inserts; set batch_size explicitly for very large lists (and to bound memory in bulk_update, which builds all WHEN clauses before running anything).

  • update() returns rows matched (which can be more than the rows whose value actually changed). Use the return value to detect "nothing to update" for compare-and-set logic: filter(pk=…​, stock__gt=F("reserved")).update(reserved=F("reserved") + 1) == 1, shown in reserve_copy() below.

  • save(update_fields=[…​]) writes only the named columns (the last line prints the unchanged pages), which also narrows the window for lost updates between concurrent edits of different fields.

  • delete() collects related objects in Python to honour on_delete and signals, unless the relation and the database allow a fast path; Django 6.1’s DB_CASCADE, DB_SET_NULL and DB_SET_DEFAULT options move that work into the database (see Relationships). PROTECT raises ProtectedError, as the author with books shows.

Shaping results: values, values_list, alias, exists and contains

values() returns dictionaries and values_list() tuples (or bare values with flat=True, or named tuples with named=True) containing only the requested columns or expressions: no model instances, no save(), no methods, but also less to fetch and construct. alias() (Django 3.2) computes an expression for use in filters, ordering and later annotations without selecting it. exists() and contains() answer yes/no questions cheaply.

values, values_list, alias, exists and contains (ex/e9_values.py)
from django.db import connection
from django.db.models import F, Count, FloatField, Q
from django.db.models.functions import Cast, Upper
from django.test.utils import CaptureQueriesContext
from catalog.models import Author, Book

print(Book.objects.values("title", "price").order_by("price")[:2])           # dicts
print(list(Book.objects.values_list("title", "pages").order_by("-pages")[:2]))  # tuples
print(list(Book.objects.values_list("title", flat=True).order_by("title")[:3]))  # flat values
Row = Book.objects.values_list("title", "pages", named=True).order_by("pages").first()
print(Row.title, Row.pages)
# values() follows relations and accepts expressions
print(list(Book.objects.values(writer=F("author__name"), shout=Upper("title")).order_by("shout")[:2]))
# values() before annotate() changes GROUP BY
print(list(Book.objects.values("author__name").annotate(n=Count("pk")).order_by("-n", "author__name")))
# in_bulk() after values() (Django 6.1)
print(Book.objects.order_by("pk").values("title").in_bulk([Book.objects.earliest("pk").pk]))
print(list(Book.objects.dates("published", "year", order="DESC")[:2]))

# alias() (Django 3.2): compute an expression without selecting it
expensive = Book.objects.alias(per_page=Cast("price", FloatField()) / F("pages")).filter(per_page__gt=0.05)
print(sorted(b.title for b in expensive), hasattr(expensive[0], "per_page"))
annotated = Book.objects.annotate(per_page=Cast("price", FloatField()) / F("pages")).filter(per_page__gt=0.05)
print(hasattr(annotated[0], "per_page"))
print(list(expensive.query.annotation_select), list(annotated.query.annotation_select))   # selected annotations
# alias() then annotate() to expose it later
shown = Book.objects.alias(per_page=Cast("price", FloatField()) / F("pages")).annotate(cheap=Q(per_page__lt=0.04))
print(sorted(b.title for b in shown if b.cheap))

# exists / contains / count / len / bool
kindred = Book.objects.get(title="Kindred")
with CaptureQueriesContext(connection) as ctx:
    print(Book.objects.filter(stock=0).exists())
    print(Book.objects.filter(stock__gt=0).contains(kindred))      # Django 4.0
    print(Book.objects.filter(stock__gt=0).count())
print([q["sql"][:90] for q in ctx])
<QuerySet [{'title': 'Draft Essays', 'price': Decimal('5.00')}, {'title': 'A Wizard of Earthsea', 'price': Decimal('8.50')}]>
[('The Dispossessed', 387), ('Parable of the Sower', 345)]
['A Wizard of Earthsea', 'Draft Essays', 'Kindred']
Draft Essays 80
[{'writer': 'Ursula K. Le Guin', 'shout': 'A WIZARD OF EARTHSEA'}, {'writer': 'Ursula K. Le Guin', 'shout': 'DRAFT ESSAYS'}]
[{'author__name': 'Ursula K. Le Guin', 'n': 4}, {'author__name': 'Octavia E. Butler', 'n': 2}, {'author__name': 'Stanisław Lem', 'n': 2}]
{24: {'title': 'The Dispossessed'}}
[datetime.date(1993, 1, 1), datetime.date(1979, 1, 1)]
['Draft Essays', 'Kindred', 'Solaris'] False
True
[] ['per_page']
['Parable of the Sower', 'The Dispossessed', 'The Left Hand of Darkness']
True
True
7
['SELECT 1 AS "a" FROM "catalog_book" WHERE "catalog_book"."stock" = 0 LIMIT 1', 'SELECT 1 AS "a" FROM "catalog_book" WHERE ("catalog_book"."stock" > 0 AND "catalog_book"."', 'SELECT COUNT(*) AS "__count" FROM "catalog_book" WHERE "catalog_book"."stock" > 0']

Source: values(), values_list(), alias(), exists() and contains()

(The primary key 24 in the in_bulk() line depends on how many rows the scratch database created before; yours will differ.)

Question Cheapest correct call

Is there at least one matching row?

qs.exists() (SELECT 1 …​ LIMIT 1)

How many rows match?

qs.count() (SELECT COUNT(*))

Is this specific instance in the QuerySet? (Django 4.0)

qs.contains(obj) (a primary-key lookup, or a cache scan if the QuerySet is already loaded) instead of obj in qs, which loads the whole result

I will loop over the rows anyway

len(qs) or plain iteration; one query, cached

  • alias() versus annotate(). Both add the expression to the query; only annotate() adds it to the SELECT list (the [] ['per_page'] line). Use alias() for filter-only or sort-only helper values and annotate only what the template or serializer reads.

  • Ordering and values(). Since 5.2 the SELECT columns of values() / values_list() follow the order you list the expressions (previously a set of unintuitive rules applied), which matters when combining queries with union().

  • values() before annotate() sets the GROUP BY (values("author__name").annotate(n=Count("pk")) is "books per author name"); the other order annotates every row and then restricts the columns. The details are on Aggregation, Expressions & Functions.

  • values_list(flat=True) needs one explicit field. Calling it with flat=True and no field name is deprecated in 6.1; pass "pk" instead.

  • dates() and datetimes() return the distinct dates or datetimes truncated to year, month, week or day (datetimes also hour, minute, second); there is no decade kind.

  • only() / defer() select fewer columns but still return instances; touching a deferred field costs one query per instance (see the next page in the section on optimisation).

Combining QuerySets and iterating large results

union(), intersection() and difference() run the set operations in SQL; |, & and ^ on two QuerySets of the same model combine their WHERE clauses instead. iterator(chunk_size=…​) streams results without caching.

union, iterator, ordering and deferred fields (ex/e11_misc.py)
from django.db import connection
from django.db.models import Q
from django.test.utils import CaptureQueriesContext
from catalog.models import Author, Book, Genre

# iterator(): stream rows in chunks instead of caching the whole result
with CaptureQueriesContext(connection) as ctx:
    total = sum(b.pages for b in Book.objects.iterator(chunk_size=3))
print(total, len(ctx))

# union / intersection / difference: restricted follow-up operations
# Meta.ordering would add ORDER BY to each part, which compound statements reject: clear it first
cheap = Book.objects.filter(price__lt=9).order_by().values_list("title", flat=True)
long_ = Book.objects.filter(pages__gt=340).order_by().values_list("title", flat=True)
print(sorted(cheap.union(long_)), sorted(cheap.intersection(long_)), sorted(cheap.difference(long_)))
try:
    cheap.union(long_).filter(title__startswith="A")
except Exception as exc:
    print(type(exc).__name__, exc)

# reverse(), distinct(), order_by() with several keys, random-free ordering
print([b.title for b in Book.objects.order_by("author__name", "-price")][:3])
print(Book.objects.order_by("title").reverse().first())
print(Book.objects.order_by("pk").totally_ordered, Book.objects.order_by("price").totally_ordered)   # Django 6.1

# selecting less: only/defer (details on the optimisation page)
b = Book.objects.only("title").get(title="Solaris")
with CaptureQueriesContext(connection) as ctx:
    b.price
print(len(ctx), "extra query for a deferred field")

# slicing + count + exists with sliced querysets
print(Book.objects.all()[:3].count(), Book.objects.all()[3:].exists())

# queries are lazy objects -- pickle-safe, composable, reusable as subqueries
authors_with_stock = Book.objects.filter(stock__gt=0).values("author")
print(Author.objects.filter(pk__in=authors_with_stock).count())
2062 1
['A Wizard of Earthsea', 'Draft Essays', 'Parable of the Sower', 'The Dispossessed'] [] ['A Wizard of Earthsea', 'Draft Essays']
NotSupportedError Calling QuerySet.filter() after union() is not supported.
['Kindred', 'Parable of the Sower', 'The Cyberiad']
The Left Hand of Darkness
True False
1 extra query for a deferred field
3 True
3

Source: union(), iterator() and totally_ordered

  • Combined queries are restricted. After union(), intersection() or difference() only count(), order_by(), values(), values_list() and slicing are allowed (the NotSupportedError above); every part must select compatible columns.

  • Default ordering. A model’s Meta.ordering is applied to combined QuerySets since Django 6.1, so a field of Options.ordering that is not selected by values() raises DatabaseError; call order_by() with no arguments on the parts (as the listing does) or after combining to clear it. SQLite additionally rejects ORDER BY inside the parts of a compound statement.

  • iterator() and prefetching. iterator() reads rows in chunks from the database cursor and does not fill the result cache (the sum above issued one query). Since Django 4.1 prefetch_related() works with iterator() provided you pass chunk_size; before that no prefetching happened. Drivers differ: on PostgreSQL server-side cursors are used (and are incompatible with transaction pooling in PgBouncer unless disabled with DISABLE_SERVER_SIDE_CURSORS).

  • totally_ordered (Django 6.1) tells whether a QuerySet’s ordering is deterministic (a unique field is included), which is what pagination needs. Ordering by price alone is not totally ordered; ordering by pk is.

  • Pagination with Paginator issues a COUNT(*) and a sliced SELECT; give it an ordered QuerySet or Django warns (UnorderedObjectListWarning). See Pagination strategies for keyset pagination at scale.

Custom lookups and transforms

When no built-in lookup says what you mean, register your own. A Lookup subclass (lookup_name, as_sql()) adds a comparison such as ne; a Transform subclass (function, an output_field) adds a step that can be chained with further lookups, such as pagesabs__lt. Register them on a field class with Field.register_lookup() (a decorator form exists), import the module from AppConfig.ready() so registration happens at startup, and remember that the lookup’s as_sql() returns the SQL fragment and its parameters, never user data interpolated into the string.

catalog/lookups.py
from django.db.models import CharField, Field, IntegerField, Lookup, Transform
from django.db.models.functions import Lower


@Field.register_lookup
class NotEqual(Lookup):
    """title__ne="x"  ->  title <> 'x'"""

    lookup_name = "ne"

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        return f"{lhs} <> {rhs}", (*lhs_params, *rhs_params)


@IntegerField.register_lookup
class AbsoluteValue(Transform):
    """pages__abs  ->  ABS(pages)"""

    lookup_name = "abs"
    function = "ABS"

    @property
    def output_field(self):
        return IntegerField()


# Lower is a Transform that Django ships but does not register on any field
CharField.register_lookup(Lower)
catalog/apps.py
from django.apps import AppConfig


class CatalogConfig(AppConfig):
    name = "catalog"

    def ready(self):
        from . import lookups  # noqa: F401  (importing registers the lookups)
Using the lookups, and a lookup registered on one field instance (ex/e10_custom.py)
from django.db.models import CharField
from catalog.models import Author, Book

print(sorted(b.title for b in Book.objects.filter(author__country__ne="US"))[:3])
print(Book.objects.filter(title__ne="Solaris").query.where)
print(Book.objects.exclude(title__ne="Solaris").count())          # works inside exclude() too

print(sorted(b.title for b in Book.objects.filter(title__lower__startswith="the d")))
print(Book.objects.filter(title__lower="solaris").count())

# the transform is chainable and usable in order_by(); build a column that can be negative
from django.db.models import F
print([b.title for b in Book.objects.annotate(delta=F("pages") - 250).filter(delta__abs__lt=50).order_by("title")])
print(Book.objects.filter(pages__abs__lt=100).count())

# Django 4.2: register a lookup on one Field instance only (not on the whole class)
from django.db.models import Lookup
class ShortTitle(Lookup):
    lookup_name = "short"
    prepare_rhs = False        # keep the right-hand side an int; CharField would turn it into a str
    def as_sql(self, compiler, connection):
        lhs, lp = self.process_lhs(compiler, connection)
        rhs, rp = self.process_rhs(compiler, connection)
        return f"LENGTH({lhs}) <= {rhs}", (*lp, *rp)
title_field = Book._meta.get_field("title")
title_field.register_lookup(ShortTitle)
print(sorted(b.title for b in Book.objects.filter(title__short=7)))
try:
    Author.objects.filter(name__short=7)
except Exception as exc:
    print(type(exc).__name__, exc)
['Solaris', 'The Cyberiad']
(AND: NotEqual(Col(catalog_book, catalog.Book.title), 'Solaris'))
1
['The Dispossessed']
1
['Kindred', 'Solaris', 'The Cyberiad']
1
['Kindred', 'Solaris']
FieldError Unsupported lookup 'short' for CharField or join on the field not permitted.

Source: How to write custom lookups and Lookup API reference

  • Register early. A lookup is only known after its module is imported; importing it in ready() guarantees it exists before any query is built. Import errors there fail at startup, which is the point.

  • Lookup versus Transform. A Transform takes one expression and returns a new one (ABS(pages)), so it can be followed by a lookup or another transform; a Lookup takes a left and right side and returns a boolean condition, so it ends the chain. Django’s database functions can be registered as transforms too (FloatField.register_lookup(Abs) in the official database-functions reference); the listing registers Lower on CharField to enable titlelowerstartswith (it is not registered by default).

  • Per-field registration (Django 4.2). Book._meta.get_field("title").register_lookup(ShortTitle) registers a lookup on a single field instance; other fields of the same class do not see it (the FieldError for Author.name). Earlier versions could only register on field classes.

  • prepare_rhs. By default the right-hand value is converted with the left field’s get_prep_value(): on a CharField the integer 7 would become the string '7' and SQLite would compare a number with text and match every row. Set prepare_rhs = False (as ShortTitle does) or override get_prep_lookup() when the right-hand side has a different type.

  • Database-specific SQL. Define as_postgresql(), as_mysql(), as_oracle() or as_sqlite() next to as_sql() to emit different SQL per vendor; Django picks the method matching connection.vendor.

  • bilateral = True on a Transform applies it to both sides (a case-insensitive comparison via UPPER on both the column and the value).

  • PostgreSQL extras. django.contrib.postgres registers its own lookups (unaccent, trigram_similar, full-text search); see PostgreSQL & Database Backends.

The Bookshelf search: queries, view, tests and curl

The pieces combine into a small search endpoint. The function builds one composable QuerySet from optional filters, select_related("author") loads the author in the same query, the view paginates and returns JSON, and the tests pin both behaviour and query counts with assertNumQueries.

catalog/queries.py
from decimal import Decimal

from django.db.models import F, Q

from .models import Book


def search_books(*, text: str = "", country: str = "", max_price: Decimal | None = None, in_stock: bool = False):
    """Build one composable QuerySet from optional filters; nothing runs until it is evaluated."""
    q = Q(is_published=True)
    if text:
        q &= Q(title__icontains=text) | Q(author__name__icontains=text)
    if country:
        q &= Q(author__country=country)
    if max_price is not None:
        q &= Q(price__lte=max_price)
    if in_stock:
        q &= Q(stock__gt=F("reserved"))
    return Book.objects.filter(q).select_related("author")


def reserve_copy(book_id: int) -> bool:
    """Atomic compare-and-set: succeeds only while an unreserved copy is left."""
    updated = Book.objects.filter(pk=book_id, stock__gt=F("reserved")).update(reserved=F("reserved") + 1)
    return updated == 1


def import_prices(rows: list[dict]) -> None:
    """Upsert (INSERT ... ON CONFLICT DO UPDATE) keyed on the unique ISBN."""
    Book.objects.bulk_create(
        [Book(**row) for row in rows],
        update_conflicts=True,
        unique_fields=["isbn"],
        update_fields=["price", "stock"],
    )
catalog/views.py
from decimal import Decimal, InvalidOperation

from django.core.paginator import Paginator
from django.http import JsonResponse
from django.views.decorators.http import require_GET

from .queries import search_books


@require_GET
def book_search(request):
    try:
        max_price = Decimal(request.GET["max_price"]) if "max_price" in request.GET else None
    except InvalidOperation:
        return JsonResponse({"error": "max_price must be a number"}, status=400)
    books = search_books(
        text=request.GET.get("q", ""),
        country=request.GET.get("country", ""),
        max_price=max_price,
        in_stock=request.GET.get("in_stock") == "1",
    )
    page = Paginator(books.order_by("title"), per_page=3).get_page(request.GET.get("page"))   # COUNT + sliced SELECT
    return JsonResponse({
        "count": page.paginator.count,
        "results": [{"title": b.title, "author": b.author.name, "price": str(b.price)} for b in page],
    })
catalog/urls.py (included at books/ in config/urls.py)
from django.urls import path

from . import views

app_name = "catalog"
urlpatterns = [path("search/", views.book_search, name="search")]
catalog/tests.py
from decimal import Decimal

from django.test import TestCase
from django.urls import reverse

from .models import Author, Book
from .queries import import_prices, reserve_copy, search_books


class QueryTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        cls.lem = Author.objects.create(name="Stanisław Lem", country="PL")
        cls.leguin = Author.objects.create(name="Ursula K. Le Guin", country="US")
        cls.solaris = Book.objects.create(title="Solaris", isbn="9780156027601", author=cls.lem, price="10.25", stock=2)
        Book.objects.create(title="The Dispossessed", isbn="9780061054884", author=cls.leguin, price="9.99", stock=0)
        Book.objects.create(title="Draft", isbn="9780000000001", author=cls.leguin, price="1.00", stock=1, is_published=False)

    def test_search_builds_one_query_with_the_author_joined(self):
        with self.assertNumQueries(1):
            books = list(search_books(text="le guin"))
            self.assertEqual([b.title for b in books], ["The Dispossessed"])   # unpublished "Draft" is excluded
            self.assertEqual(books[0].author.name, "Ursula K. Le Guin")        # no extra query: select_related

    def test_reserve_copy_stops_at_stock(self):
        self.assertTrue(reserve_copy(self.solaris.pk))
        self.assertTrue(reserve_copy(self.solaris.pk))
        self.assertFalse(reserve_copy(self.solaris.pk))                         # stock=2, reserved=2
        self.solaris.refresh_from_db()
        self.assertEqual(self.solaris.reserved, 2)

    def test_import_prices_is_an_upsert(self):
        import_prices([
            {"title": "Solaris", "isbn": "9780156027601", "author_id": self.lem.pk, "price": Decimal("8.00"), "stock": 9},
            {"title": "Fiasco", "isbn": "9780156305471", "author_id": self.lem.pk, "price": Decimal("10.50"), "stock": 1},
        ])
        self.assertEqual(Book.objects.count(), 4)
        self.solaris.refresh_from_db()
        self.assertEqual((self.solaris.price, self.solaris.stock), (Decimal("8.00"), 9))

    def test_search_view(self):
        with self.assertNumQueries(2):                                           # COUNT(*) + the page SELECT
            response = self.client.get(reverse("catalog:search"), {"q": "sol", "max_price": "11"})
        self.assertEqual(response.json()["count"], 1)
        self.assertEqual(response.json()["results"][0]["author"], "Stanisław Lem")
        self.assertEqual(self.client.get(reverse("catalog:search"), {"max_price": "abc"}).status_code, 400)
python manage.py test catalog
Creating test database for alias 'default'...
....
----------------------------------------------------------------------
Ran 4 tests in 0.006s

OK
curl -s "http://127.0.0.1:8000/books/search/?q=guin&in_stock=1&max_price=10"
curl -s -w "%{http_code}\n" "http://127.0.0.1:8000/books/search/?max_price=abc"
{"count": 2, "results": [{"title": "A Wizard of Earthsea", "author": "Ursula K. Le Guin", "price": "8.50"}, {"title": "The Dispossessed", "author": "Ursula K. Le Guin", "price": "9.99"}]}
{"error": "max_price must be a number"}400

Source: assertNumQueries, Pagination and select_related()

Notes: the unpublished Draft book is filtered out by the base Q(is_published=True); reserve_copy() is the compare-and-set pattern with F() and the update() return value; import_prices() is the upsert; the view validates max_price before it reaches the ORM (Decimal raises InvalidOperation, which becomes a 400); and the search view runs exactly two queries (COUNT plus one page, with the authors joined), a number the test locks in so a later change that adds a per-row query fails loudly. @require_GET keeps the endpoint read-only, so there is nothing to protect with CSRF; any write endpoint built from the operations on this page must be a POST with the CSRF token (see Forms).

Sync and async behaviour

Since Django 4.1 every QuerySet method that hits the database has an a-prefixed coroutine (aget, acreate, acount, aexists, afirst, aupdate, adelete, abulk_create, aget_or_create, aiterator, …​) and a QuerySet can be consumed with async for. Django 4.2 added Model.asave(), adelete() and arefresh_from_db() and the async related-manager methods (aadd, aclear, aremove, aset); 5.0 added aprefetch_related_objects(), prefetching with aiterator() and create_defaults for aupdate_or_create(). Methods that only build a QuerySet (filter, exclude, order_by, …​) are plain synchronous calls in both worlds.

Async QuerySet usage (ex/e12_async.py)
import asyncio
from django.core.exceptions import SynchronousOnlyOperation
from catalog.models import Author, Book

async def main():
    book = await Book.objects.aget(isbn="9780156027601")                 # get()
    print(book.title, await Book.objects.filter(stock__gt=0).acount(), await Book.objects.filter(stock=0).aexists())
    print((await Book.objects.order_by("title").afirst()).title)
    print([b.title async for b in Book.objects.filter(pages__gt=340).order_by("title")])   # async for
    print([b.title async for b in Book.objects.order_by("title").aiterator(chunk_size=2)][:2])
    author, created = await Author.objects.aget_or_create(name="Async Author", defaults={"country": "ES"})
    print(created, await Author.objects.filter(name="Async Author").aupdate(country="PT"))
    print(await Author.objects.filter(name="Async Author").adelete())
    # QuerySet methods that need the database are blocked from async code unless awaited
    try:
        list(Book.objects.all())
    except SynchronousOnlyOperation as exc:
        print("SynchronousOnlyOperation:", exc)
    # related objects: await the relation, or fetch it in the query (select_related)
    book = await Book.objects.select_related("author").aget(isbn="9780156027601")
    print(book.author.name)
    rev = [r async for r in book.reviews.all()]
    print(len(rev))

asyncio.run(main())
Solaris 7 True
A Wizard of Earthsea
['Parable of the Sower', 'The Dispossessed']
['A Wizard of Earthsea', 'Draft Essays']
True 1
(1, {'catalog.Author': 1})
SynchronousOnlyOperation: You cannot call this from an async context - use a thread or sync_to_async.
Stanisław Lem
1

Source: Asynchronous support: queries and the ORM and the a-prefixed methods in the QuerySet API reference

  • The async API still uses a thread. The ORM drivers are synchronous; a-methods run the sync code in a thread pool (sync_to_async), so they keep the event loop responsive but do not make the database faster. Transactions are not supported across await points: transaction.atomic() cannot wrap async code, so put the atomic part in a sync function and call it with sync_to_async.

  • Lazy attribute access is blocked. book.author on an unloaded relation, list(qs) or if qs: inside a coroutine raise SynchronousOnlyOperation; load relations up front with select_related() or prefetch_related(), or await them.

  • Connections. The ORM calls run in worker threads and each thread has its own database connection; see Async Support for how persistent connections and async code interact.

  • Sync views under ASGI still work: Django runs them in a thread. Choose async views only when the view awaits other I/O (HTTP calls, channels) besides the ORM.

Pitfalls checklist

Errors Django raises for common mistakes (ex/e13_pitfalls.py)
from catalog.models import Book

for label, thunk in {
    "negative index": lambda: Book.objects.all()[-1],
    "filter after slice": lambda: Book.objects.all()[:3].filter(stock=0),
    "delete a slice": lambda: Book.objects.all()[:3].delete(),
    "unknown field": lambda: Book.objects.filter(titel="x"),
    "get on empty slice": lambda: Book.objects.all()[100],
}.items():
    try:
        thunk()
    except Exception as exc:
        print(f"{label}: {type(exc).__name__}: {exc}")

# truthiness loads the whole result; exists() asks the database for one row
from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    qs = Book.objects.all()
    if qs:                                      # SELECT of every column of every row
        pass
    Book.objects.all().exists()                 # SELECT 1 ... LIMIT 1
print([q["sql"][:38] for q in ctx], "| rows loaded by `if qs:` =", len(qs))
negative index: ValueError: Negative indexing is not supported.
filter after slice: TypeError: Cannot filter a query once a slice has been taken.
delete a slice: TypeError: Cannot use 'limit' or 'offset' with delete().
unknown field: FieldError: Cannot resolve keyword 'titel' into field. Choices are: author, author_id, genres, id, is_published, isbn, pages, price, published, reserved, reviews, stock, title
get on empty slice: IndexError: list index out of range
['SELECT "catalog_book"."id", "catalog_b', 'SELECT 1 AS "a" FROM "catalog_book" LI'] | rows loaded by `if qs:` = 8

Source: QuerySet API reference

  • Evaluating by accident. if qs:, len(qs) and list(qs) load everything; use exists(), count() and slicing.

  • Querying in a loop. book.author.name inside for book in books (nine queries for eight books above); load relations in the query.

  • Treating filter().filter() like one filter() on multi-valued relations (and exclude() like a negated filter()).

  • Forgetting distinct() after filtering through a many-to-many or reverse foreign key.

  • get() for non-unique data, and unhandled DoesNotExist in views (use get_object_or_404).

  • Read-modify-write without F() (lost updates) or without a transaction and select_for_update() when the logic cannot be expressed as one UPDATE.

  • Assuming bulk operations call save(). bulk_create, bulk_update, update and delete on a QuerySet skip custom save(), signals and auto_now.

  • Large IN lists. A list of tens of thousands of ids can exceed SQLite’s variable limit and slow other databases; use a subquery (pk__in=other_queryset) or a join.

  • NULL ordering and comparisons differ by database; state nulls_last / nulls_first and use isnull explicitly.

  • Integer division and type affinity on SQLite (see the F-expression notes): cast operands and test on the production database, because SQLite is more permissive than PostgreSQL.

  • Raw user-controlled lookups (filter(**request.GET.dict()), order_by(request.GET["sort"])): whitelist field names. Raw SQL belongs with parameters (see Transactions, Raw SQL & Multiple Databases).

  • Shared module-level QuerySets keep their result cache for the process lifetime.

  • select_related() with no arguments is deprecated in 6.1; name the relations.

  • Testing query counts. Pin hot paths with assertNumQueries and use setUpTestData for read-only fixtures; see Testing.

Where the books are outdated

Books written for Django 1.11 to 3.x describe the ORM correctly in outline (QuerySets, lookups, F/Q, get_or_create, values), but several parts have moved on:

  • Missing features. They predate QuerySet.alias() (3.2), QuerySet.contains() (4.0), Q XOR (4.1), bulk_create upserts (4.1), create_defaults (5.0), per-field register_lookup (4.2), the async ORM (4.1/4.2), QuerySet.totally_ordered and fetch modes (6.1); pre-4.1 code typically reaches for raw SQL, extra() or a get_or_create loop where these now exist. The official documentation calls extra() a last resort that is expected to be deprecated.

  • Boolean checks. Old advice to write if qs.count() > 0 or if len(qs) is superseded by exists() (and contains() for a given instance).

  • NullBooleanField was removed in 4.0 (use BooleanField(null=True)), and non-boolean values for isnull are no longer accepted (4.0); django.utils.six and Python 2 idioms (iteritems, unicode) are gone.

  • bulk_update() returns the number of updated rows since 4.0; bulk_create() sets primary keys on SQLite since 4.0 and on upserts since 5.0.

  • Defaults. Primary keys are BigAutoField by default since 6.0, so the ids printed in older listings (AutoField) differ in the migrations they generate.

  • assertQuerysetEqual is assertQuerySetEqual (4.2; old name removed in 5.1) and no longer calls repr() on strings.

  • Shell and Python. Interactive examples that import models by hand predate the shell auto-imports of 5.2; code based on pytz or timezone-naive datetimes predates zoneinfo (4.0) and the USE_TZ = True default (5.0).

What changed recently

Version Query-related change

6.1

QuerySet.fetch_mode() and fetch modes (FETCH_ONE, FETCH_PEERS, FETCH_RAISE); totally_ordered; in_bulk() after values()/values_list(); first()/last() stop forcing a primary-key ordering after a bare order_by(); default Meta.ordering applied to combined QuerySets; JSONNull; UUID4/UUID7 functions; BitAnd/BitOr/BitXor aggregates; deprecations of select_related() without arguments and values_list(flat=True) without a field. SQLite 3.37 and PostgreSQL 15 are the minimums.

6.0

StringAgg and AnyValue aggregates, order_by for aggregates; expression-assigned fields and GeneratedField`s refreshed after `save() on SQLite, PostgreSQL and Oracle; Model.NotUpdated; AsyncPaginator.

5.2 LTS

Composite primary keys (see Constraints, Indexes & Composite Keys); values()/values_list() columns follow the requested order; JSONArray; explain() options; shell auto-imports.

5.1

F() and OuterRef() slicing of character fields; Model.refresh_from_db(from_queryset=…​); explain(generic_plan=True).

5.0

create_defaults for update_or_create(); bulk_create(update_conflicts=True) sets primary keys; aprefetch_related_objects(); aiterator() honours prefetch_related(); aget_object_or_404().

4.2

Per-field register_lookup(); ~F() for boolean expressions; Model.asave()/adelete()/arefresh_from_db() and async related managers; prefetch_related() with sliced Prefetch querysets.

4.1

Async ORM (aget, acount, async for); Q and QuerySet XOR; bulk_create(update_conflicts=True); iterator() with prefetch_related() when chunk_size is given.

4.0

QuerySet.contains(); non-boolean values for isnull rejected; lookup expressions usable in filters and annotations; bulk_update() returns a count; aggregates accept default.

3.2

QuerySet.alias(); transforms in F(), OuterRef() and aggregates; When(condition=…​) with lookups.

Source: Django 6.1 release notes, 6.0, 5.2, 5.1, 5.0, 4.2, 4.1, 4.0 and 3.2

References