The ORM: Queries

Reviewed & published by Brayan K

Django's ORM (Object-Relational Mapper) lets you query your database using Python instead of raw SQL — you call methods on a model's manager to create, retrieve, filter, update, and delete rows, and Django translates those calls into SQL for you.

Part of the free Django course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll learn how to create and fetch objects, filter and order them with field lookups, and update, delete, count, and aggregate rows — all without writing a single line of SQL.

What You'll Learn in This Lesson

• Creating and saving rows with create() and save()
• Fetching rows with all() and get()
• Filtering with filter() and exclude()
• Field lookups like __gte, __contains, __startswith
• Ordering with order_by() and slicing QuerySets
• Updating, deleting, counting, and aggregating

1 Creating and Fetching Objects

Every model has a manager, available as Model.objects. The manager is your gateway to the database. To add a new row you can either build an instance and call save(), or do both at once with create().

# Two ways to add a row to the database

# Option A: build then save
post = Post(title="Hello World", views=0)
post.save()                       # INSERT runs here

# Option B: create() does build + save in one call
post = Post.objects.create(title="Django Tips", views=10)

# Fetch every row as a QuerySet
all_posts = Post.objects.all()

# Fetch exactly one row by a unique field
one_post = Post.objects.get(id=1)   # raises DoesNotExist if missing

all() returns a QuerySet (a lazy collection of rows). get() returns a single instance and expects exactly one match.

# Worked Example: a tiny in-memory "manager" that mimics the Django ORM.
# Each "row" is a dict. PostManager plays the role of Post.objects.

class PostManager:
    def __init__(self):
        self._rows = []
        self._next_id = 1

    def create(self, **fields):
        row = {"id": self._next_id, **fields}
        self._rows.append(row)
        self._next_id += 1
        return row

    def all(self):
        return list(self._rows)

    def get(self, **lookups):
        matches = [r for r in self._rows
                   if all(r.get(k) == v for k, v in lookups.items())]
        if len(matches) == 0:
            raise Exception("DoesNotExist")
        if len(matches) > 1:
            raise Exception("MultipleObjectsReturned")
        return matches[0]

Post = PostManager()
Post.create(title="Hello World", views=0)
Post.create(title="Django Tips", views=10)

print("All posts:", Post.all())
print("Get id=2:", Post.get(id=2))

# ✅ Expected output:
# All posts: [{'id': 1, 'title': 'Hello World', 'views': 0}, {'id': 2, 'title': 'Django Tips', 'views': 10}]
# Get id=2: {'id': 2, 'title': 'Django Tips', 'views': 10}

2 Filtering, Lookups, and Ordering

filter() keeps the rows that match a condition; exclude() drops them. Conditions are written as keyword arguments using field lookups — a field name, two underscores, and a lookup type.

• views__gte=100 — views greater than or equal to 100
• title__contains="Django" — title contains that text
• title__startswith="A" — title begins with "A"
• order_by("-views") — sort by views, highest first

# Real Django ORM calls — all chainable and lazy

popular   = Post.objects.filter(views__gte=100)
not_draft = Post.objects.exclude(status="draft")
django_ps = Post.objects.filter(title__contains="Django")
a_posts   = Post.objects.filter(title__startswith="A")

# Order by views, highest first, then take the top 3 (slicing)
top_three = Post.objects.order_by("-views")[:3]

# Chaining builds ONE efficient SQL query
recent = Post.objects.filter(views__gte=10).order_by("-id")[:5]

# Worked Example: filter, exclude, lookups, order_by, and slicing
# over plain lists of dicts, mirroring how the ORM behaves.

posts = [
    {"id": 1, "title": "Django Basics",   "views": 250, "status": "live"},
    {"id": 2, "title": "Advanced Django", "views": 90,  "status": "draft"},
    {"id": 3, "title": "Python Tips",     "views": 500, "status": "live"},
    {"id": 4, "title": "About Me",        "views": 30,  "status": "live"},
]

# filter: views >= 100  (the __gte lookup)
popular = [p for p in posts if p["views"] >= 100]
print("views__gte=100:", [p["title"] for p in popular])

# exclude: drop drafts
not_draft = [p for p in posts if p["status"] != "draft"]
print("exclude draft:", [p["title"] for p in not_draft])

# title__contains="Django"
django_posts = [p for p in posts if "Django" in p["title"]]
print("contains Django:", [p["title"] for p in django_posts])

# order_by("-views") then slice top 2
top2 = sorted(posts, key=lambda p: p["views"], reverse=True)[:2]
print("top 2 by views:", [p["title"] for p in top2])

# ✅ Expected output:
# views__gte=100: ['Django Basics', 'Python Tips']
# exclude draft: ['Django Basics', 'Python Tips', 'About Me']
# contains Django: ['Django Basics', 'Advanced Django']
# top 2 by views: ['Python Tips', 'Django Basics']

3 Updating, Deleting, and Aggregating

You can change a single object by setting an attribute and calling save(), or update many rows at once with the QuerySet's update(). Removing rows is done with delete(). To summarise data, use count(), exists(), and aggregate().

from django.db.models import Count, Avg

# Update one object
post = Post.objects.get(id=1)
post.views = 999
post.save()

# Bulk update every matching row in a single SQL UPDATE
Post.objects.filter(status="draft").update(status="live")

# Delete rows
Post.objects.get(id=4).delete()          # one row
Post.objects.filter(views__lt=10).delete()  # many rows

# Counting and existence checks
total      = Post.objects.count()
has_live   = Post.objects.filter(status="live").exists()

# Aggregation returns a dict
stats = Post.objects.aggregate(Avg("views"), Count("id"))
# {'views__avg': 215.0, 'id__count': 4}

update() and bulk delete() run in a single SQL statement and do not call save() on each object, which makes them fast for changing many rows at once.

# Worked Example: update, delete, count, and aggregate in plain Python.

posts = [
    {"id": 1, "title": "Django Basics",   "views": 250, "status": "draft"},
    {"id": 2, "title": "Advanced Django", "views": 90,  "status": "draft"},
    {"id": 3, "title": "Python Tips",     "views": 500, "status": "live"},
    {"id": 4, "title": "About Me",        "views": 30,  "status": "live"},
]

# update(): set every draft to live (like .filter(...).update(...))
for p in posts:
    if p["status"] == "draft":
        p["status"] = "live"
print("after update:", [p["status"] for p in posts])

# delete(): remove rows with views < 50 (like .filter(views__lt=50).delete())
posts = [p for p in posts if p["views"] >= 50]
print("after delete:", [p["title"] for p in posts])

# count() and exists()
print("count:", len(posts))
print("any live?", any(p["status"] == "live" for p in posts))

# aggregate(): average of views (like aggregate(Avg('views')))
avg_views = sum(p["views"] for p in posts) / len(posts)
print("views__avg:", avg_views)

# ✅ Expected output:
# after update: ['live', 'live', 'live', 'live']
# after delete: ['Django Basics', 'Advanced Django', 'Python Tips']
# count: 3
# any live? True
# views__avg: 280.0

🎯 Your Turn: Pick the Right Lookup

Fill in the blank with the correct field lookup so the query keeps only posts with at least 100 views. (Hint: the lookup for "greater than or equal" is __gte.)

# 🎯 YOUR TURN: replace ___ with the lookup name "gte"
# so the filter keeps posts whose views are >= 100.

posts = [
    {"title": "Popular", "views": 300},
    {"title": "Medium",  "views": 120},
    {"title": "Quiet",   "views": 40},
]

lookup = "___"   # <-- replace ___ with: gte

def matches(post, lookup, threshold):
    if lookup == "gte":
        return post["views"] >= threshold
    return False

result = [p["title"] for p in posts if matches(p, lookup, 100)]
print("Django call: Post.objects.filter(views__" + lookup + "=100)")
print("Kept:", result)

# EXPECTED OUTPUT:
# Django call: Post.objects.filter(views__gte=100)
# Kept: ['Popular', 'Medium']

Common Errors (And How to Fix Them)

❌ DoesNotExist / MultipleObjectsReturned from get()

get() demands exactly one row — it raises DoesNotExist when nothing matches and MultipleObjectsReturned when several do.

✅ Fix: use filter() when many results are possible, or look up by a unique field like the primary key.

❌ Expecting a QuerySet to have already hit the database

QuerySets are lazy — building one runs no SQL, so changes made afterward can surprise you.

✅ Fix: force evaluation when you need the data now with list(qs), a loop, or len(qs).

❌ Using = instead of a lookup inside filter()

Writing filter(title="x") is fine for exact matches, but you cannot write Python comparison operators like filter(views > 100).

✅ Fix: use a lookup — filter(views__gt=100) — or __exact to be explicit about equality.

📋 Quick Reference

🏆 Mini Challenge: Build a Query Report

Combine filtering, ordering, counting, and averaging into a single report — the everyday work of querying with the ORM.

# 🏆 Mini Challenge: produce a small analytics report from "rows".
# This mirrors: Post.objects.filter(status="live").order_by("-views")
#               plus .count() and .aggregate(Avg("views")).

posts = [
    {"title": "Django Basics",   "views": 250, "status": "live"},
    {"title": "Advanced Django", "views": 90,  "status": "draft"},
    {"title": "Python Tips",     "views": 500, "status": "live"},
    {"title": "About Me",        "views": 30,  "status": "live"},
]

# 1. filter to live posts
live = [p for p in posts if p["status"] == "live"]

# 2. order_by("-views")
live_sorted = sorted(live, key=lambda p: p["views"], reverse=True)

# 3. count() and aggregate(Avg("views"))
count = len(live_sorted)
avg = sum(p["views"] for p in live_sorted) / count

print("Live posts (most viewed first):")
for p in live_sorted:
    print(f"  - {p['title']} ({p['views']} views)")
print("count:", count)
print("views__avg:", avg)

# EXPECTED: 3 live posts, Python Tips first, avg = 260.0

❓ Frequently Asked Questions

Lesson complete — you can query the database in Python!

You now know how to create and fetch objects, filter and exclude rows with field lookups, order and slice QuerySets, and update, delete, count, and aggregate data — all through the ORM instead of raw SQL.

Practice quiz

Continue this course

• Previous: Models & Migrations
• Next: Relationships (FK, M2M) — Connect models with ForeignKey, ManyToManyField, and OneToOneField
• Quick reference: Django cheat sheet › Models & ORM

Frequently asked questions

What is the Django ORM?

The ORM (Object-Relational Mapper) is the layer that lets you work with your database using Python objects instead of writing raw SQL. Each model class maps to a database table, each instance maps to a row, and each field maps to a column. You call methods like all(), filter(), create(), and delete() on a model's manager, and Django generates and runs the equivalent SQL for you. This keeps your code portable across PostgreSQL, MySQL, SQLite, and others.

What is the difference between get() and filter()?

filter() always returns a QuerySet — a collection that may contain zero, one, or many objects — so it is safe even when nothing matches. get() returns exactly one object, and it raises DoesNotExist if nothing matches or MultipleObjectsReturned if more than one row matches. Use get() when you expect a single unique row (like looking up by primary key), and filter() when you want a set of results you can loop over.

Why are Django QuerySets called 'lazy'?

A QuerySet does not hit the database when you create it. Django waits until you actually use the results — by iterating over them, slicing with a step, calling len(), or converting to a list. This laziness lets you chain methods like Post.objects.filter(...).exclude(...).order_by(...) and have Django build one efficient SQL query, instead of running a separate query for each method call.

What are field lookups in Django?

Field lookups are the double-underscore suffixes you add to a field name in filter(), exclude(), and get() to express conditions other than exact equality. For example views__gte=100 means 'views greater than or equal to 100', title__contains='Django' means the title contains that substring, and name__startswith='A' matches names beginning with A. Django translates each lookup into the matching SQL WHERE clause.