Django QuerySets

A QuerySet is a list of rows from the database. You build it with Python, and Django writes the SQL for you.

The examples use the Course model from the models lesson, with three courses saved: Web Development (Rs 8000, 6 months), Graphic Designing (Rs 6000, 3 months) and SEO (Rs 5000, 2 months, online). Try them in python manage.py shell.

Getting Data

Every model has a manager called objects:

Example

python

from courses.models import Course

all_courses = Course.objects.all()
online = Course.objects.filter(is_online=True)
not_online = Course.objects.exclude(is_online=True)
web = Course.objects.get(name="Web Development")

print(all_courses.count())
print(web.fee)

Output

3
8000

Method	Returns
`all()`	Every row
`filter(...)`	Rows that match
`exclude(...)`	Rows that do not match
`get(...)`	Exactly one row (error if none or many)
`first()` / `last()`	One row or None
`order_by("name")`	Sorted rows; `"-name"` sorts backwards
`count()`	Number of rows
`exists()`	True if any row matches
`values("name", "fee")`	Dictionaries instead of objects

Field Lookups

Add a double underscore and a lookup name to a field for more powerful filters:

Example

python

cheap = Course.objects.filter(fee__lt=6000)
long_courses = Course.objects.filter(duration_months__gte=6)
design = Course.objects.filter(name__icontains="design")
chosen = Course.objects.filter(id__in=[1, 3])

for c in cheap.order_by("fee"):
    print(c.name, c.fee)

Output

SEO 5000

Lookup	Meaning
`fee__lt=5000`	Less than
`fee__lte=5000`	Less than or equal
`fee__gt=5000`	Greater than
`fee__gte=5000`	Greater than or equal
`name__icontains="web"`	Contains, ignoring case
`name__startswith="W"`	Starts with
`id__in=[1, 2]`	Is in the list
`course__name="SEO"`	Field of a related model

Chaining

QuerySets can be chained. Django only runs the query when you actually use the results, for example in a loop.

Example

python

result = (
    Course.objects
    .filter(is_online=False)
    .exclude(fee__gt=7000)
    .order_by("-fee")
)
print([c.name for c in result])

Output

['Graphic Designing']

Creating, Updating and Deleting

Example

python

# Create
seo = Course.objects.create(name="Advanced SEO", fee=7000)

# Update one object
seo.fee = 6500
seo.save()

# Update many rows at once
Course.objects.filter(is_online=False).update(duration_months=4)

# Delete
seo.delete()

get() raises DoesNotExist if nothing matches. In views, use get_object_or_404() so visitors see a normal 404 page instead of an error.

Using QuerySets in Views

Example

python

from django.shortcuts import render, get_object_or_404
from .models import Course

def course_list(request):
    courses = Course.objects.order_by("name")
    return render(request, "courses/list.html", {"courses": courses})

def course_detail(request, course_id):
    course = get_object_or_404(Course, id=course_id)
    students = course.students.order_by("name")
    return render(request, "courses/detail.html", {"course": course, "students": students})

course.students works because the Student model's ForeignKey has related_name="students".

Exercise

Which QuerySet finds courses with a fee greater than 5000?

Course.objects.filter(fee > 5000)Course.objects.filter(fee__gt=5000)Course.objects.get(fee__gt=5000)Course.objects.filter(fee="more than 5000")