Django QuerySets

last modified November 13, 2026 Python 3.15

In this article we query data with Django QuerySets. We retrieve single objects and collections, filter them with field lookups and across relationships, combine conditions with Q objects, compare fields with F expressions, sort and slice the results, see that QuerySets are lazy, and update and delete rows. The examples use Django 6.1, Python 3.15, and the default SQLite database.

QuerySets

A QuerySet represents a collection of rows from the database. It is created by a model's manager, which is by default called objects: Book.objects.all() returns a QuerySet with every row. A QuerySet can be filtered, sorted, and sliced; each of these operations returns a new QuerySet, so the calls can be chained. The ORM translates the final QuerySet into one SQL statement and executes it only when we need the results. The models used in this article are described in Django models.

The example project

The project is created with uv; the steps are explained in Django introduction. The commands below create a project directory, install Django, generate the project with the configuration package config, start the app shop, and create the folder demos for demo scripts.

$ mkdir queryset
$ cd queryset
$ uv init --bare
$ uv add django
$ uv run django-admin startproject config .
$ uv run python manage.py startapp shop
$ mkdir demos

The trailing dot in startproject config . creates manage.py and the config package in the current directory instead of one level deeper. A new app is ignored by Django until it is listed in INSTALLED_APPS, so we add the app to the list in config/settings.py; the other entries are generated by startproject.

config/settings.py

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'shop',
]

The listings in this article are of two kinds. A listing with a solid blue border and the label Project file is a file of the application: models, views, templates, tests, and settings. A listing with a dashed amber border and the label Demo script is a small helper in the demos folder; it sets up Django, runs a few lines of code, and prints the results, so that we can watch the behaviour. Demo scripts are not part of the application; we run one from the project directory with uv run python -m demos.name.

The models describe authors and their books. The tables are created with makemigrations and migrate (see Django migrations).

shop/models.py

from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=100)
    country = models.CharField(max_length=50)

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name


class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
    year = models.PositiveSmallIntegerField()
    pages = models.PositiveIntegerField()
    price = models.DecimalField(max_digits=6, decimal_places=2)
    rating = models.FloatField(null=True, blank=True)
    stock = models.PositiveIntegerField(default=0)
    sold = models.PositiveIntegerField(default=0)

    class Meta:
        ordering = ['title']

    def __str__(self):
        return self.title

A book has an author, a year, a number of pages, a price, an optional rating, and the numbers of copies in stock and sold. The ordering by title in Meta makes the results deterministic. A script fills the tables with five authors and eight books.

demos/seed.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from decimal import Decimal

from shop.models import Author, Book

authors = {
    name: Author.objects.create(name=name, country=country)
    for name, country in [
        ('George Orwell', 'UK'), ('Jane Austen', 'UK'), ('Frank Herbert', 'US'),
        ('Ursula K. Le Guin', 'US'), ('Stanislaw Lem', 'Poland'),
    ]
}

rows = [
    ('Nineteen Eighty-Four', 'George Orwell', 1949, 328, '9.99', 4.6, 5, 12),
    ('Animal Farm', 'George Orwell', 1945, 112, '6.50', 4.3, 20, 8),
    ('Emma', 'Jane Austen', 1815, 474, '7.50', 4.0, 3, 1),
    ('Pride and Prejudice', 'Jane Austen', 1813, 432, '8.25', 4.7, 7, 15),
    ('Dune', 'Frank Herbert', 1965, 612, '12.90', 4.5, 4, 9),
    ('The Dispossessed', 'Ursula K. Le Guin', 1974, 387, '11.00', 4.4, 2, 2),
    ('Solaris', 'Stanislaw Lem', 1961, 204, '10.50', 4.2, 6, 3),
    ('Fiasco', 'Stanislaw Lem', 1986, 320, '10.00', None, 1, 0),
]
for title, author, year, pages, price, rating, stock, sold in rows:
    Book.objects.create(title=title, author=authors[author], year=year, pages=pages,
                        price=Decimal(price), rating=rating, stock=stock, sold=sold)

print(Author.objects.count(), 'authors,', Book.objects.count(), 'books')

The script sets up Django, creates the authors first and keeps them in a dictionary, and creates the books with the authors as foreign keys. The Decimal prices are given as strings. The Fiasco book has no rating, which is stored as NULL.

$ uv run python manage.py makemigrations shop
Migrations for 'shop':
  shop/migrations/0001_initial.py
    + Create model Author
    + Create model Book
$ uv run python manage.py migrate shop
Operations to perform:
  Apply all migrations: shop
Running migrations:
  Applying shop.0001_initial... OK
$ uv run python -m demos.seed
5 authors, 8 books

Retrieving objects

The get() method returns exactly one object. It raises Model.DoesNotExist when nothing matches and Model.MultipleObjectsReturned when several rows match, so it should be used with a unique field. The count() method returns the number of rows with a SELECT COUNT(*) query, first() and last() return the first or the last object of the ordering, or None, and exists() checks whether any row matches without loading it.

demos/basic.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from shop.models import Book

print(Book.objects.count())
print(Book.objects.get(title='Dune'))
print(Book.objects.first(), '|', Book.objects.last())
print(Book.objects.filter(year__lt=1800).exists())
print(Book.objects.filter(year__lt=1900).exists())

try:
    Book.objects.get(title='Missing')
except Book.DoesNotExist:
    print('DoesNotExist')

try:
    Book.objects.get(author__country='UK')
except Book.MultipleObjectsReturned:
    print('MultipleObjectsReturned')

The script calls each of these methods. Four books are by UK authors, so the second get() raises MultipleObjectsReturned. The books are sorted by title, so the first one is Animal Farm and the last one is The Dispossessed.

$ uv run python -m demos.basic
8
Dune
Animal Farm | The Dispossessed
False
True
DoesNotExist
MultipleObjectsReturned

filter() and exclude()

The filter() method returns the rows that match all the given conditions, and exclude() returns the rows that do not. Keyword arguments are joined with AND. Since both return a QuerySet, we chain them: Book.objects.exclude(author__country='UK').filter(pages__gt=300) selects the non-UK books with more than 300 pages.

Field lookups

The keyword of a condition has the form field__lookup=value, where the lookup is separated from the field by two underscores; without a lookup, exact is used. The most important lookups are the following.

exact, iexact - equal, and equal ignoring case
contains, icontains - substring match, case sensitive or not
startswith, endswith - prefix and suffix match
gt, gte, lt, lte - comparisons
in - the value is in a list
range - between two values, both inclusive
isnull - the value is NULL; field=None does the same
year, month, date - parts of date fields, as in published__year=2020
regex, iregex - regular expression match

demos/lookups.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from shop.models import Book


def titles(queryset):
    return [book.title for book in queryset]


print(titles(Book.objects.filter(year__gte=1960)))
print(titles(Book.objects.filter(title__icontains='an')))
print(titles(Book.objects.filter(title__startswith='The')))
print(titles(Book.objects.filter(year__in=[1813, 1815, 1949])))
print(titles(Book.objects.filter(year__range=(1940, 1970))))
print(titles(Book.objects.filter(rating__isnull=True)))
print(titles(Book.objects.exclude(author__country='UK').filter(pages__gt=300)))
print(titles(Book.objects.filter(author__name__startswith='Stan')))

The script prints only the titles. Reading the output with the data in mind: the first line holds the books from 1960 on, the second one the titles that contain an in any case, the third one the single title that starts with The. The fourth and fifth lines use in and range. The sixth one finds the book without a rating. The last two lines combine exclude() with a relation, and filter across a relation.

$ uv run python -m demos.lookups
['Dune', 'Fiasco', 'Solaris', 'The Dispossessed']
['Animal Farm', 'Pride and Prejudice']
['The Dispossessed']
['Emma', 'Nineteen Eighty-Four', 'Pride and Prejudice']
['Animal Farm', 'Dune', 'Nineteen Eighty-Four', 'Solaris']
['Fiasco']
['Dune', 'Fiasco', 'The Dispossessed']
['Fiasco', 'Solaris']

Lookups across relationships

Two underscores also follow a relation. author__country='UK' joins the author table and compares its column; author__name__startswith='Stan' combines a relation with a lookup. The lookups work in the reverse direction as well: with the related_name of the ForeignKey, an author query can filter by book values, as in Author.objects.filter(books__year__lt=1900). Django builds the JOIN clauses for us. The reverse direction is shown in the output of the section on F expressions below.

Q objects

Keyword arguments in filter() can express only AND. For OR and NOT we use Q objects, which can be combined with the operators |, &, and ~, and nested with parentheses.

Book.objects.filter(Q(year__lt=1900) | Q(pages__gt=500))
Book.objects.filter(Q(author__country='US') & ~Q(year__gt=1970))

The first query selects the books that are older than 1900 or have more than 500 pages. The second selects the books by US authors, excluding the ones published after 1970.

F expressions

An F object refers to the value of a field in the same row. It lets the database compare two columns or compute with them, without loading the rows into Python.

Book.objects.filter(sold__gt=F('stock'))

The query selects the books that sold more copies than are in stock. The F object can also be used in an update, where it avoids a race condition: update(stock=F('stock') + 10) is performed by the database in one atomic statement instead of reading the value, adding to it in Python, and writing it back.

demos/q_f.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from django.db.models import F, Q

from shop.models import Author, Book


def titles(queryset):
    return [book.title for book in queryset]


print(titles(Book.objects.filter(Q(year__lt=1900) | Q(pages__gt=500))))
print(titles(Book.objects.filter(Q(author__country='US') & ~Q(year__gt=1970))))
print(titles(Book.objects.filter(sold__gt=F('stock'))))
print(Author.objects.filter(books__year__lt=1900).distinct())

The script runs the Q and F queries and an author query across the reverse relation. The distinct() method removes the duplicate rows that a join produces: an author with several old books would otherwise appear several times. Here only Jane Austen has books from before 1900.

$ uv run python -m demos.q_f
['Dune', 'Emma', 'Pride and Prejudice']
['Dune']
['Dune', 'Nineteen Eighty-Four', 'Pride and Prejudice']
<QuerySet [<Author: Jane Austen>]>

Ordering and slicing

The order_by() method sorts the results; a minus sign in front of the field name reverses the order, and several fields can be given. Without order_by() the Meta.ordering of the model applies. Slicing a QuerySet with Python's slice syntax translates to LIMIT and OFFSET: [:3] gives the first three rows and [2:4] skips two rows and takes two. Negative indices are not supported. On databases, a NULL value sorts first or last depending on the backend; SQLite puts it first in ascending order, so descending order by rating puts NULL last.

values() and values_list()

The values() method returns dictionaries instead of model objects, and it selects only the requested columns. values_list() returns tuples, or plain values with flat=True when there is a single field. Both can follow relations: values_list('author__country', flat=True) gives the country of each book's author. They avoid the cost of building model instances when we need only a few columns.

demos/order.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from shop.models import Book

print([book.title for book in Book.objects.order_by('-price')[:3]])
print([book.title for book in Book.objects.order_by('year')[2:4]])
print([book.title for book in Book.objects.order_by('-rating')[:2]])
print(Book.objects.filter(year__gt=1960).values('title', 'year'))
print(list(Book.objects.values_list('title', flat=True)[:3]))
print(list(Book.objects.values_list('author__country', flat=True).distinct().order_by('author__country')))

The script prints the three most expensive books, the third and fourth oldest books, the two best rated books, a values() QuerySet, the first three titles as plain values, and the distinct countries. The values() QuerySet is shown by its representation, which lists the dictionaries.

$ uv run python -m demos.order
['Dune', 'The Dispossessed', 'Solaris']
['Animal Farm', 'Nineteen Eighty-Four']
['Pride and Prejudice', 'Nineteen Eighty-Four']
<QuerySet [{'title': 'Dune', 'year': 1965}, {'title': 'Fiasco', 'year': 1986}, {'title': 'Solaris', 'year': 1961}, {'title': 'The Dispossessed', 'year': 1974}]>
['Animal Farm', 'Dune', 'Emma']
['Poland', 'UK', 'US']

Lazy evaluation

Creating and chaining QuerySets does not touch the database. The query is executed when the results are needed: when we iterate over the QuerySet, slice it with a step, convert it with list(), call len(), or test its truth value. The result of the first evaluation is stored in the QuerySet's cache, so a second iteration does not run the query again. The query attribute shows the SQL that the QuerySet would run. In the script below the CaptureQueriesContext helper counts the queries sent to the database, so that we see when they happen.

demos/lazy.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from django.db import connection
from django.test.utils import CaptureQueriesContext

from shop.models import Book

queryset = Book.objects.filter(year__gte=1960).order_by('-year')
print(queryset.values('title').query)

with CaptureQueriesContext(connection) as context:
    books = Book.objects.filter(pages__gt=300)
    books = books.exclude(author__country='UK')
    print('after building:', len(context))
    list(books)
    print('after first evaluation:', len(context))
    list(books)
    print('after second evaluation:', len(context))
    print(books.count(), 'books (count from the cache)')
    print('after count:', len(context))
    Book.objects.filter(pages__gt=300).count()
    print('after a new count query:', len(context))

The first statement prints the SQL of a QuerySet that was never evaluated. Building the QuerySet by chaining filter() and exclude() executes nothing. The first list() runs one query, the second one uses the cache, and count() on an evaluated QuerySet counts the cached rows. A new QuerySet needs a new query. Because of the cache, keep a QuerySet in a variable when we use it several times; call exists() or count() on a QuerySet that we do not need to load.

$ uv run python -m demos.lazy
SELECT "shop_book"."title" AS "title" FROM "shop_book" WHERE "shop_book"."year" >= 1960 ORDER BY "shop_book"."year" DESC
after building: 0
after first evaluation: 1
after second evaluation: 1
3 books (count from the cache)
after count: 1
after a new count query: 2

Creating, updating, and deleting

A QuerySet can also change data. The update() method updates all matching rows with one UPDATE statement and returns the number of rows; it does not call save() and does not update the auto_now fields. The delete() method deletes the matching rows and returns the number of deleted objects per model. The get_or_create() method returns an existing object or creates it from the lookup values and the optional defaults, and returns a tuple of the object and a boolean flag. The update_or_create() method updates the object when it exists. The bulk_create() method inserts many objects with one query.

demos/modify.py

import os

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from decimal import Decimal

from django.db.models import F

from shop.models import Author, Book

print(Book.objects.filter(stock__lt=3).update(stock=F('stock') + 10))
print(Book.objects.get(title='Fiasco').stock)

lem = Author.objects.get(name='Stanislaw Lem')
book, created = Book.objects.get_or_create(
    title='His Master\'s Voice', author=lem,
    defaults={'year': 1968, 'pages': 199, 'price': Decimal('9.00')})
print(book, created)
book, created = Book.objects.get_or_create(title='His Master\'s Voice', author=lem)
print(book, created)

book, created = Book.objects.update_or_create(
    title='His Master\'s Voice', author=lem, defaults={'price': Decimal('9.50')})
print(book.price, created)

Book.objects.bulk_create([
    Book(title=f'Test {n}', author=lem, year=2000, pages=100, price=Decimal('1.00'))
    for n in range(3)
])
print(Book.objects.filter(title__startswith='Test').count())
print(Book.objects.filter(title__startswith='Test').delete())
print(Book.objects.count())

The script adds 10 to the stock of the books that have fewer than 3 copies, using an F expression; two books match, The Dispossessed and Fiasco, and the stock of Fiasco becomes 11. The following calls show that a second get_or_create() finds the existing object, and the price is updated with update_or_create(). The script then creates three test books with bulk_create() and deletes them again. It ends with the number of books.

$ uv run python -m demos.modify
2
11
His Master's Voice True
His Master's Voice False
9.50 False
3
(3, {'shop.Book': 3})
9

Testing queries

The tests create a few rows in setUpTestData(), which runs once for the test class, and check the queries. The assertNumQueries() method counts the queries in its block; the test confirms that two iterations over one QuerySet run a single query.

shop/tests.py

from decimal import Decimal

from django.db.models import F, Q
from django.test import TestCase

from .models import Author, Book


class BookQueryTests(TestCase):

    @classmethod
    def setUpTestData(cls):
        uk = Author.objects.create(name='Jane Austen', country='UK')
        us = Author.objects.create(name='Frank Herbert', country='US')
        Book.objects.create(title='Emma', author=uk, year=1815, pages=474,
                            price=Decimal('7.50'), stock=3, sold=1)
        Book.objects.create(title='Dune', author=us, year=1965, pages=612,
                            price=Decimal('12.90'), stock=4, sold=9)

    def titles(self, queryset):
        return [book.title for book in queryset]

    def test_filter_and_exclude(self):
        self.assertEqual(self.titles(Book.objects.filter(year__gte=1900)), ['Dune'])
        self.assertEqual(self.titles(Book.objects.exclude(author__country='UK')), ['Dune'])

    def test_q_objects(self):
        queryset = Book.objects.filter(Q(year__lt=1900) | Q(pages__gt=500))
        self.assertEqual(self.titles(queryset), ['Dune', 'Emma'])

    def test_f_expression_compares_fields(self):
        self.assertEqual(self.titles(Book.objects.filter(sold__gt=F('stock'))), ['Dune'])

    def test_queryset_is_lazy_and_cached(self):
        with self.assertNumQueries(1):
            books = Book.objects.filter(pages__gt=100)
            list(books)
            list(books)

    def test_update_with_f(self):
        Book.objects.update(stock=F('stock') + 1)
        self.assertEqual(Book.objects.get(title='Emma').stock, 4)

    def test_get_or_create(self):
        author = Author.objects.get(name='Jane Austen')
        defaults = {'year': 1813, 'pages': 432, 'price': Decimal('8.25')}
        _, created = Book.objects.get_or_create(title='Persuasion', author=author, defaults=defaults)
        self.assertTrue(created)
        _, created = Book.objects.get_or_create(title='Persuasion', author=author, defaults=defaults)
        self.assertFalse(created)

Every test runs in a transaction that is rolled back, so the changes made by update() and get_or_create() in the tests do not leak into other tests.

$ uv run python manage.py test
Found 6 test(s).
System check identified no issues (0 silenced).
Creating test database for alias 'default'...
......
----------------------------------------------------------------------
Ran 6 tests in 0.005s

OK
Destroying test database for alias 'default'...

Source

Django documentation: Making queries

Django documentation: QuerySet API reference

Django documentation: Lookup API reference

In this article we have retrieved objects with get(), filter(), and exclude(), used field lookups, relations, Q and F objects, ordered and sliced the results, and seen that QuerySets are lazy and cached. The data is defined by Django models and created by Django migrations. The next steps are aggregation and annotation, and avoiding unnecessary queries.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Django tutorials.

Ebooks