Querying with the ORM · Django

The ORM (object-relational mapper) lets you query the database in Python instead of SQL. Every example here runs against an in-memory database, filled by this setup at the top of each one:

Python

import django
from django.conf import settings

settings.configure(
    INSTALLED_APPS=["django.contrib.contenttypes"],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
)
django.setup()

from django.db import connection, models


class Course(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.IntegerField()

    class Meta:
        app_label = "courses"

    def __str__(self):
        return self.title


with connection.schema_editor() as editor:
    editor.create_model(Course)

for title, category, price in [("Python", "Programming", 499), ("SQL", "Data", 299), ("React", "Web", 399),
                               ("Pandas", "Data", 349), ("CSS", "Web", 199), ("Java", "Programming", 449)]:
    Course.objects.create(title=title, category=category, price=price)

print(Course.objects.count(), "courses")
print(Course.objects.all())

Course.objects is the model's manager, the starting point of every query.

Reading: filter, exclude, get

Python

import django
from django.conf import settings

settings.configure(INSTALLED_APPS=["django.contrib.contenttypes"],
                   DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}})
django.setup()

from django.db import connection, models


class Course(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.IntegerField()

    class Meta:
        app_label = "courses"

    def __str__(self):
        return self.title


with connection.schema_editor() as editor:
    editor.create_model(Course)
for title, category, price in [("Python", "Programming", 499), ("SQL", "Data", 299), ("React", "Web", 399),
                               ("Pandas", "Data", 349), ("CSS", "Web", 199), ("Java", "Programming", 449)]:
    Course.objects.create(title=title, category=category, price=price)

print(Course.objects.filter(category="Data"))
print(Course.objects.exclude(category="Data").filter(price__lt=400))
print(Course.objects.get(title="React").price)

try:
    Course.objects.get(title="Rust")
except Course.DoesNotExist:
    print("No course called Rust")

filter(...) keeps matching rows and exclude(...) removes them. Both return a QuerySet, which can be filtered further.
get(...) returns exactly one object, and raises DoesNotExist if there's none, or MultipleObjectsReturned if there's more than one.

Field lookups

A double underscore after a field name adds a lookup:

Lookup	Means	SQL
`price__gt=300`, `__gte`, `__lt`, `__lte`	greater than, and so on	`price > 300`
`title__startswith="P"`	starts with	`LIKE 'P%'`
`title__icontains="script"`	contains, ignoring case	`LIKE '%script%'`
`category__in=["Data", "Web"]`	one of a list	`IN (...)`
`price__range=(200, 400)`	between, inclusive	`BETWEEN`
`description__isnull=True`	is NULL	`IS NULL`

Ordering, slicing and values

Python

import django
from django.conf import settings

settings.configure(INSTALLED_APPS=["django.contrib.contenttypes"],
                   DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}})
django.setup()

from django.db import connection, models


class Course(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.IntegerField()

    class Meta:
        app_label = "courses"

    def __str__(self):
        return self.title


with connection.schema_editor() as editor:
    editor.create_model(Course)
for title, category, price in [("Python", "Programming", 499), ("SQL", "Data", 299), ("React", "Web", 399),
                               ("Pandas", "Data", 349), ("CSS", "Web", 199), ("Java", "Programming", 449)]:
    Course.objects.create(title=title, category=category, price=price)

print(Course.objects.order_by("-price")[:3])                          # the three most expensive
print(list(Course.objects.order_by("title").values_list("title", flat=True)))
print(list(Course.objects.filter(category="Web").values("title", "price")))
print(Course.objects.filter(price__range=(300, 450)).exists())

query = Course.objects.filter(category="Data").order_by("price")
print(query.query)                                                    # roughly the SQL Django will run

QuerySets are lazy: building one runs no SQL. Django queries the database only when you actually use the results, by looping, printing, slicing, count() and so on. print(qs.query) shows the SQL, with the values filled in for display; Django really sends them separately, as parameters, which is what protects you from SQL injection.

Aggregates and annotations

Python

import django
from django.conf import settings

settings.configure(INSTALLED_APPS=["django.contrib.contenttypes"],
                   DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}})
django.setup()

from django.db import connection, models
from django.db.models import Avg, Count, Max, Sum


class Course(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.IntegerField()

    class Meta:
        app_label = "courses"


with connection.schema_editor() as editor:
    editor.create_model(Course)
for title, category, price in [("Python", "Programming", 499), ("SQL", "Data", 299), ("React", "Web", 399),
                               ("Pandas", "Data", 349), ("CSS", "Web", 199), ("Java", "Programming", 449)]:
    Course.objects.create(title=title, category=category, price=price)

print(Course.objects.aggregate(Avg("price"), Max("price"), total=Sum("price")))

per_category = Course.objects.values("category").annotate(courses=Count("id"), average=Avg("price")).order_by("category")
for row in per_category:
    print(row)

aggregate summarises the whole QuerySet into a dictionary, like SQL's SUM or AVG with no grouping.
values(...).annotate(...) groups by the given fields and calculates per group: Django's GROUP BY.

Updating and deleting

Python

import django
from django.conf import settings

settings.configure(INSTALLED_APPS=["django.contrib.contenttypes"],
                   DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}})
django.setup()

from django.db import connection, models
from django.db.models import F, Q


class Course(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.IntegerField()

    class Meta:
        app_label = "courses"

    def __str__(self):
        return f"{self.title} ({self.price})"


with connection.schema_editor() as editor:
    editor.create_model(Course)
for title, category, price in [("Python", "Programming", 499), ("SQL", "Data", 299), ("React", "Web", 399),
                               ("Pandas", "Data", 349), ("CSS", "Web", 199), ("Java", "Programming", 449)]:
    Course.objects.create(title=title, category=category, price=price)

css = Course.objects.get(title="CSS")
css.price = 149
css.save()                                                    # update one object

n = Course.objects.filter(category="Data").update(price=F("price") - 50)
print(n, "Data courses discounted")                           # update many at once

print(Course.objects.filter(Q(category="Web") | Q(price__gt=450)))   # OR
deleted, _ = Course.objects.filter(price__lt=200).delete()
print(deleted, "deleted;", Course.objects.count(), "left")

Change attributes and call save() to update one object.
QuerySet.update(...) changes many rows in a single SQL statement.
F("price") refers to the column's current value in the database, so F("price") - 50 subtracts 50 from each row's own price.
Q(...) | Q(...) combines conditions with OR; plain keyword arguments to filter are always combined with AND.

Following relationships

Python

# lessons in Python courses: follow the ForeignKey with __
Lesson.objects.filter(course__title="Python")

# fetch each lesson's course in the same query, instead of one query per lesson
for lesson in Lesson.objects.select_related("course"):
    print(lesson.course.title, lesson.title)

# courses with more than 10 lessons
Course.objects.annotate(n=Count("lessons")).filter(n__gt=10)

course__title reaches across the relationship, as a SQL join would. select_related avoids the classic "N+1 queries" problem, where looping over 100 lessons and touching lesson.course would run 101 queries.