Django ORM Common operations and principles

Django ORM (Object-Relational Mapping, object-relational mapping) allows developers to use Python to operate on relational databases without having to write SQL directly.

ORM maps tables in the database to Python Model, maps database records to Model instances, and builds and executes database queries with QuerySet.

1. Basic query

The main difference between :

• all(): Returns QuerySet composed of all records.
• filter(): Returns QuerySet that meets the conditions and may contain zero or more records.
• get(): Returns a single Model object, not QuerySet.
• first(), last(): Returns a single object or None.
• none(): Returns QuerySet without records.

Note: get() throws DoesNotExist when no record is found, and MultipleObjectsReturned is thrown when multiple records are matched.

Also, get() is not equivalent to simply LIMIT 1. Django typically uses a restricted query to get enough results to determine whether multiple records were matched.

2. WHERE condition

Django Use double underline __ to represent the field search condition (Field Lookup).

Commonly used meanings of Lookup:

• exact: Exact match.
• contains: Contains.
• icontains: Ignore case-inclusive matches.
• iexact: Exact match ignoring case.
• gt, gte: greater than, greater than or equal to.
• lt, lte: less than, less than or equal to.
• in: Belongs to the specified collection.
• isnull: Determine whether it is SQL NULL.

Note: The actual behavior of case-insensitive queries such as icontains may be affected by the database collation (Collation).

Date query

Assume that the model has the created_at field:

In actual development, if you need to query all records on a certain day, you can usually use the left-closed and right-open time range:

Model.objects.filter(
    created_at__gte=start,
    created_at__lt=end,
)

This is especially common when using DateTimeField.

3. AND / OR / NOT conditions

from django.db.models import Q

Q is used to build complex query conditions, supporting & (AND), | (OR), ~ (NOT).

Example of complex conditions:

Model.objects.filter(
    Q(name__icontains="Tokyo")
    | Q(name__icontains="Kyoto"),
    score__gte=80,
)

Corresponding logic:

WHERE
    (name LIKE '%Tokyo%' OR name LIKE '%Kyoto%')
    AND score >= 80

Note:

• Several common keyword arguments use AND by default.
• Q can be nested and combined, and parentheses are recommended for complex expressions.
• When mixing Q and keyword parameters in filter(), the Q parameter must be placed first.
• When negating a nullable field, the processing of SQL NULL requires special attention and cannot be simply understood as NOT (field=value) in all cases.

4. SELECT: Specify the return field

The difference between values and only

# Return dictionary
Model.objects.values("id", "name")

# Return Model Example
Model.objects.only("id", "name")

values() does not construct a normal Model instance, while only() returns a Model instance that just lazily loads some fields.

For deferred fields, additional SQL queries may be triggered if it is accessed later.

5. ORDER BY

Model.objects.order_by("-score", "id")

The priority is arranged in descending order of score, and the same time sharing is arranged in ascending order of id.

Note: Random sorting may incur significant performance overhead with large data volumes.

6. LIMIT / OFFSET

Example:

Model.objects.order_by("id")[10:20]

It is recommended to use stable sorting when paging to prevent the order of results from being uncertain.

For large-scale data, a larger OFFSET may cause query efficiency to decrease, and paging based on primary keys or cursors can be considered.

7. Aggregate: aggregate function

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

Example:

Model.objects.aggregate(
    total=Count("id"),
    avg_score=Avg("score"),
    max_score=Max("score"),
)

Return:

{
    "total": 100,
    "avg_score": 85.5,
    "max_score": 99,
}

aggregate() returns the aggregation result dictionary, QuerySet is not returned.

8. GROUP BY and annotate

from django.db.models import Count, Avg

Example:

POI.objects.values("city_id").annotate(
    total=Count("id"),
    avg_score=Avg("score"),
)

Conceptually corresponds to:

SELECT
    city_id,
    COUNT(id) AS total,
    AVG(score) AS avg_score
FROM poi
GROUP BY city_id;

The difference between aggregate and annotate

• aggregate(): Calculate statistical values for the entire query results and return the dictionary.
• annotate(): Add a calculated field to the object or group in the query result, returning QuerySet.

HAVING

POI.objects.values("city_id").annotate(
    total=Count("id")
).filter(total__gt=10)

Conceptually corresponds to:

SELECT city_id, COUNT(id) AS total
FROM poi
GROUP BY city_id
HAVING COUNT(id) > 10;

Note: The calling order of values(), annotate() and filter() may affect the grouping method and aggregation results.

9. JOIN: Related query

Suppose there are two Model:

class City(models.Model):
    name = models.CharField(max_length=100)


class POI(models.Model):
    name = models.CharField(max_length=100)
    city = models.ForeignKey(
        City,
        on_delete=models.CASCADE,
        related_name="pois",
    )

Forward correlation query

For example:

POI.objects.filter(city__name="Tokyo")

Conceptually corresponds to:

SELECT poi.*
FROM poi
INNER JOIN city
    ON poi.city_id = city.id
WHERE city.name = 'Tokyo';

Reverse correlation query

City.objects.filter(pois__name="Tokyo Tower")

Query City from related_name="pois" for its associated POI.

Also possible:

city = City.objects.get(id=1)

city.pois.all()

Returns all POI associated with this city.

If related_name is not specified, the reverse association manager default is usually poi_set, and the field name for the reverse query is usually poi.

10. select_related and prefetch_related

These are two very important methods in Django ORM performance optimization, used to solve the N+1 Query problem.

N+1 Query Question

pois = POI.objects.all()

for poi in pois:
    print(poi.city.name)

May produce:

1 times SQL：Query all POI
N times SQL：Query each separately POI of City

If there are 100 POI, in the worst case SQL may be executed 101 times.

select_related

pois = POI.objects.select_related("city")

Load related objects in the same query via SQL JOIN.

Applies to:

• ForeignKey
• OneToOneField

Features: SQL query is usually executed only once, but JOIN may increase query result width and complexity.

prefetch_related

cities = City.objects.prefetch_related("pois")

for city in cities:
    for poi in city.pois.all():
        print(poi.name)

Usually SQL is executed twice:

SQL 1：Query City
SQL 2：Batch query related POI

The organization of the relationships is then completed in Python.

Applies to:

• ManyToManyField
• Reverse ForeignKey (one-to-many)
• ForeignKey
• OneToOneField

Contrast

It is generally recommended that single-value correlation give priority to select_related, and multi-value correlation use prefetch_related.

Custom Prefetch

from django.db.models import Prefetch

cities = City.objects.prefetch_related(
    Prefetch(
        "pois",
        queryset=POI.objects.filter(score__gte=80),
        to_attr="high_score_pois",
    )
)

In this way, only POI that meets the conditions can be preloaded and saved to the custom attribute high_score_pois.

11. F expression

from django.db.models import F

F allows field values to be referenced directly in the database for calculations or comparisons between fields.

Update field

Product.objects.filter(id=1).update(
    stock=F("stock") - 1
)

Corresponds to:

UPDATE product
SET stock = stock - 1
WHERE id = 1;

Compare between fields

Product.objects.filter(
    sale_price__lt=F("original_price")
)

Corresponds to:

WHERE sale_price < original_price

Why use F?

Traditional writing:

product = Product.objects.get(id=1)
product.stock -= 1
product.save()

This is read first, then modify, then write.

When multiple requests are executed at the same time, the problem of lost updates may occur.

F() allows the database to directly perform field calculations, avoiding the race conditions caused by the above-mentioned simple read-modify mode.

But it cannot replace all concurrency control mechanisms. For example, complex inventory deductions also need to check the update conditions and the number of affected rows.

12. Case / When conditional expression

from django.db.models import Case, When, Value, CharField

Similar to CASE WHEN in SQL.

POI.objects.annotate(
    level=Case(
        When(score__gte=90, then=Value("high")),
        When(score__gte=60, then=Value("medium")),
        default=Value("low"),
        output_field=CharField(),
    )
)

Conceptually corresponds to:

CASE
    WHEN score >= 90 THEN 'high'
    WHEN score >= 60 THEN 'medium'
    ELSE 'low'
END

Suitable for dynamic classification, conditional sorting, statistical grouping and other scenarios.

13. Subquery and OuterRef

from django.db.models import Subquery, OuterRef

Allows building correlated subqueries in Django ORM.

Assuming that there are multiple POIs in each city, you need to get the POI name with the highest score in each city:

top_poi = POI.objects.filter(
    city_id=OuterRef("pk")
).order_by("-score", "id")

cities = City.objects.annotate(
    top_poi_name=Subquery(
        top_poi.values("name")[:1]
    )
)

Two core concepts:

• Subquery(): Use a QuerySet as a SQL subquery.
• OuterRef(): References a field in the outer query.

OuterRef("pk") here represents the primary key that refers to the outer City query.

Similar to SQL:

SELECT
    city.*,
    (
        SELECT name
        FROM poi
        WHERE poi.city_id = city.id
        ORDER BY score DESC, id ASC
        LIMIT 1
    ) AS top_poi_name
FROM city;

It is suitable for processing the latest records of each group, the highest score of each group, and related subqueries.

14. Exists expression

from django.db.models import Exists, OuterRef

For example, query the cities with at least one POI:

pois = POI.objects.filter(
    city_id=OuterRef("pk")
)

cities = City.objects.filter(
    Exists(pois)
)

Conceptually corresponds to:

SELECT *
FROM city
WHERE EXISTS (
    SELECT 1
    FROM poi
    WHERE poi.city_id = city.id
);

Exists is suitable for determining whether the associated record exists without actually returning the fields of the associated object.

15. JSONField query

Assumptions:

name_translations = models.JSONField(default=dict)

Database content:

{
  "en": "Tokyo",
  "zh": "Tokyo",
  "ja": "Tokyo"
}

Frequently Asked Questions:

Note:

• name_translations__en is a JSON keypath lookup, not plain SQL JOIN.
• The SQL implementation of JSON query depends on the specific database.
• Operations such as JSONField's contains are not supported by all database backends. For example, SQLite does not support JSONField's contains lookup.
• When performing large queries on dynamic JSON keys, you need to pay attention to database index and query performance.

16. exists / count / len

POI.objects.filter(score__gte=80).exists()

Usually generates something like:

SELECT 1
FROM poi
WHERE score >= 80
LIMIT 1;

For large QuerySet not yet loaded:

• Only determine whether it exists: usually use exists().
• Only quantity is required: usually count() is used.
• All records need to be traversed: QuerySet can be traversed directly to avoid unnecessary repeated queries.

Note: If QuerySet has been fully evaluated and the result cached, len(queryset) can directly utilize the cache without having to execute SQL again.

17. CREATE / UPDATE / DELETE

create

obj, created = Model.objects.get_or_create(
    name="Tokyo",
    defaults={"score": 80},
)

created indicates whether this call creates a new object.

In actual use, it is recommended to ensure the uniqueness of the search field corresponding to get_or_create() through database unique constraints to reduce the risk of concurrent creation of duplicate records.

renew

Model.objects.filter(id=1).update(name="A")

Corresponds to:

UPDATE table
SET name = 'A'
WHERE id = 1;

update() directly performs database-level updates, does not call the save() method of Model, and does not trigger the pre_save and post_save signals.

Delete

Model.objects.filter(id=1).delete()

Conceptually corresponds to:

DELETE FROM table
WHERE id = 1;

However, please note: when Django deletes associated data, additional query and deletion operations may be performed based on the on_delete configuration.

18. Batch operations

bulk_create

POI.objects.bulk_create([
    POI(name="A", city_id=1),
    POI(name="B", city_id=1),
    POI(name="C", city_id=2),
])

Inserting data in batches is usually more efficient than calling create() in a loop.

bulk_update

pois = list(POI.objects.filter(id__in=[1, 2]))

for poi in pois:
    poi.score = 90

POI.objects.bulk_update(
    pois,
    ["score"],
)

Update specified fields in batches.

Note: bulk_create() and bulk_update() do not call save() for each object, nor do they trigger the corresponding model save signal.

For large amounts of data, you can use batch_size to control the size of each batch operation.

19. Transaction Transaction

from django.db import transaction

Transactions are used to ensure that multiple database operations are executed as a whole.

atomic

with transaction.atomic():
    Order.objects.create(...)
    Product.objects.filter(id=1).update(
        stock=F("stock") - 1
    )

If an exception occurs within the transaction that causes the transaction to be rolled back, the database modifications will be rolled back as a whole.

Typical application scenarios:

• Create an order and deduct inventory.
• Transfer operations.
• Update multiple business-related data tables at the same time.

select_for_update

with transaction.atomic():
    product = Product.objects.select_for_update().get(
        id=1
    )

    if product.stock > 0:
        product.stock -= 1
        product.save(update_fields=["stock"])

select_for_update() adds row-level locks to selected records in supported databases, usually until the end of the transaction.

Suitable for concurrent businesses that need to read data first, judge conditions, and then modify it.

Note: Specific lock behavior depends on the database backend and transaction isolation level. SQLite does not support the SELECT ... FOR UPDATE row lock in the usual sense.

20. QuerySet Lazy execution and caching

An important feature of Django QuerySet is that Lazy Evaluation (lazy evaluation).

For example:

qs = POI.objects.filter(score__gte=80)

This usually just builds QuerySet, but does not actually execute SQL.

Execution:

for poi in qs:
    print(poi.name)

Only then will the database actually be accessed.

Common operations that trigger queries

• Traverse QuerySet.
• Convert to list().
• Use len().
• Use bool().
• Access the index of QuerySet that has not yet been evaluated.
• Use get(), count(), exists(), etc. methods to perform database queries.

QuerySet result cache

qs = POI.objects.all()

list(qs)  # execute SQL，and fill QuerySet Result caching

list(qs)  # Usually reuse cache

But if you rebuild QuerySet:

POI.objects.all()
POI.objects.all()

They are different QuerySet and do not automatically share the result cache.

iterator

for poi in POI.objects.iterator(chunk_size=2000):
    process(poi)

iterator() is suitable for processing large amounts of data, avoiding the use of full result caching at the QuerySet level.

It should be noted that this does not mean that the database driver must obtain data row by row. The actual memory and data acquisition method also depend on the database backend and cursor behavior.

21. Query performance optimization

1. Solve N+1 query

POI.objects.select_related("city")

Or:

City.objects.prefetch_related("pois")

2. Avoid querying unnecessary fields

POI.objects.values("id", "name")

3. Use exists only to determine whether it exists

POI.objects.filter(name="Tokyo").exists()

4. Use database aggregation instead of Python traversal

Not recommended:

total = len(list(POI.objects.all()))

It's usually more appropriate:

total = POI.objects.count()

5. Properly set database indexes

class POI(models.Model):
    name = models.CharField(max_length=100)
    score = models.IntegerField()

    class Meta:
        indexes = [
            models.Index(fields=["name"]),
            models.Index(fields=["-score"]),
        ]

Indexes can speed up qualified queries, but also increase storage space and write maintenance costs.

For ordinary B-tree indexes, wildcard queries before and after icontains may not be able to effectively use the index.

6. Use explain to analyze the execution plan

qs = POI.objects.filter(score__gte=80)

print(qs.explain())

explain() is used to obtain the database query execution plan and assist in judgment:

• Whether to use indexes.
• Whether to perform a full table scan.
• Does JOIN bring higher costs.
• Whether filtering or sorting conditions need to be optimized.

Supported explain() options are database-specific. When using the Execute Analysis class option, be aware that it may actually execute the query.

22. Other commonly used methods

union(), intersection(), and difference() correspond to the SQL set operation, and there are restrictions on field compatibility, sorting, and slicing operations.

23. The relationship between Django ORM and SQL

Django ORM is not a replacement database for SQL, but a high-level abstraction upon which SQL is built.

Overall workflow:

Python ORM
    ↓
QuerySet Build
    ↓
SQL Compiler
    ↓
SQL + Parameters
    ↓
Database driven
    ↓
PostgreSQL / MySQL / SQLite
    ↓
Query results
    ↓
Model / Dict / Tuple

You can use the following method to view SQL generated by QuerySet:

qs = POI.objects.filter(
    score__gte=80
).order_by("-score")

print(qs.query)

str(qs.query) is suitable for debugging, but it should not be taken directly as a parameterized SQL that can be safely re-executed.

If you need to view SQL and parameters, you can use:

sql, params = qs.query.sql_with_params()

In actual operation, Django uses the database-driven parameterized query mechanism to process variables to avoid splicing ordinary parameters directly into the SQL string.

Summary

Django ORM The focus of learning can be divided into four levels:

For daily Django backend development, familiarity with filter(), Q(), F(), annotate(), select_related() and prefetch_related() should be given priority.

For more complex data processing, learn more about Subquery(), OuterRef(), Exists(), transactions and database lock mechanisms.

Django ORM can cover a large number of common database operations, but you still need to understand SQL, indexes, JOIN, transactions, and database execution plans to write efficient and reliable queries.

---

reference document:

• Django QuerySet API Reference
• Django Making Queries
• Django Query Expressions
• Django Database Transactions