How show raw SQL queries for your django queries or querysets
Django's Object-Relational Mapper (ORM) is designed to abstract database dialect differences away from developers. However, building performant, production-grade applications requires knowing exactly what SQL is generated under the hood.
Understanding where and how to inspect SQL depends entirely on the lifecycle stage of a QuerySet—from lazy definition in Python memory to final execution over the network wire.
The QuerySet Lifecycle & SQL Inspection Map
┌───────────────────────────────────────────────────────────────────────────────┐
│ QUERYSET LIFECYCLE │
└───────────────────────────────────────────────────────────────────────────────┘
│
1. CONSTRUCTION / CHAINING ▼ [Lazy AST in Python memory]
Book.objects.filter(...) ──────► str(qs.query)
│
2. COMPILATION / PARAMETERIZATION ▼ [SQL Template + Param Tuple]
qs.query.get_compiler() ──────► compiler.as_sql()
│
3. QUERY PLAN ESTIMATION ▼ [Database Query Optimizer]
qs.explain() ──────► EXPLAIN / EXPLAIN ANALYZE
│
4. EXECUTION / DISPATCH ▼ [Driver Cursor / Network Transit]
Iterating, .save(), .update() ──────► connection.execute_wrapper()
│
5. POST-EXECUTION AUDIT ▼ [In-Memory Query Buffer]
assertNumQueries, list(qs) ──────► connection.queries
│
6. GLOBAL TELEMETRY / LOGGING ▼ [Application-Wide I/O Stream]
settings.py / shell_plus ──────► django.db.backends / --print-sql
1. Domain Model Setup
We will use an Author and Book relational schema containing one-to-many relationships and aggregations:
# bookstore/models.py
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
email = models.EmailField(unique=True)
rating = models.FloatField(default=5.0)
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"
)
price = models.DecimalField(max_digits=6, decimal_places=2)
published_date = models.DateField()
is_published = models.BooleanField(default=True)
def __str__(self):
return self.title
Stage 1: Construction & Chaining (Pre-Execution)
When you write qs = Book.objects.filter(...), Django builds an abstract query node tree without dispatching network calls.
Technique: str(queryset.query)
Use this to preview the generated SELECT statement directly in Python.
from datetime import date
from django.db.models import Q
from bookstore.models import Book
qs = (
Book.objects.filter(
Q(price__lt=20.00) | Q(author__rating__gte=4.5),
published_date__gte=date(2023, 1, 1),
is_published=True,
)
.select_related("author")
.order_by("-price")
)
print(str(qs.query))
Output:
SELECT "bookstore_book"."id",
"bookstore_book"."title",
"bookstore_book"."author_id",
"bookstore_book"."price",
"bookstore_book"."published_date",
"bookstore_book"."is_published",
"bookstore_author"."id",
"bookstore_author"."name",
"bookstore_author"."email",
"bookstore_author"."rating"
FROM "bookstore_book"
INNER JOIN "bookstore_author" ON ("bookstore_book"."author_id" = "bookstore_author"."id")
WHERE (
("bookstore_book"."price" < 20.0 OR "bookstore_author"."rating" >= 4.5)
AND "bookstore_book"."published_date" >= 2023-01-01
AND "bookstore_book"."is_published" = true
)
ORDER BY "bookstore_book"."price" DESC
Limitations:
str(qs.query)only works on QuerySets. It does not work on mutating calls like.create(),.update(),.bulk_create(), or raw model instances (instance.save()).- The output inlines parameters for readability; it does not show driver placeholders.
Stage 2: Compilation & Parameterization
Before querying the database, Django compiles the QuerySet into a parameterized SQL string and a tuple of sanitized arguments to prevent SQL injection.
Technique: compiler.as_sql()
from bookstore.models import Book
qs = Book.objects.filter(title__startswith="Django", price__gt=35.50)
# Resolve SQL compiler for the target database alias
compiler = qs.query.get_compiler(using="default")
sql_template, params = compiler.as_sql()
print("Parameterized SQL:\n", sql_template)
print("\nParameter Tuple:\n", params)
Output:
Parameterized SQL:
SELECT "bookstore_book"."id", "bookstore_book"."title", "bookstore_book"."author_id", "bookstore_book"."price", "bookstore_book"."published_date", "bookstore_book"."is_published" FROM "bookstore_book" WHERE ("bookstore_book"."price" > %s AND "bookstore_book"."title"::text LIKE %s)
Parameter Tuple:
('35.50', 'Django%')
Stage 3: Query Plan Estimation (EXPLAIN)
To inspect how the database engine indexes, scans, and optimizes your QuerySet, use explain().
Technique: qs.explain(analyze=True)
from bookstore.models import Book
# Explain a complex subquery lookup
qs = Book.objects.filter(
author__in=Book.objects.filter(price__gt=50).values("author_id")
)
# Analyze runs the query inside EXPLAIN for true execution times (PostgreSQL/MySQL)
print(qs.explain(analyze=True, costs=True))
Output (PostgreSQL):
Hash Join (cost=21.45..42.80 rows=12 width=148) (actual time=0.082..0.086 rows=5 loops=1)
Hash Cond: (bookstore_book.author_id = subquery.author_id)
-> Seq Scan on bookstore_book (cost=0.00..18.50 rows=850 width=148) (actual time=0.011..0.024 rows=120 loops=1)
-> Hash (cost=21.30..21.30 rows=12 width=4) (actual time=0.051..0.052 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=21.18..21.30 rows=12 width=4) (actual time=0.038..0.040 rows=3 loops=1)
Group Key: bookstore_book_sub.author_id
-> Seq Scan on bookstore_book bookstore_book_sub (cost=0.00..20.62 rows=225 width=4) (actual time=0.008..0.021 rows=15 loops=1)
Filter: (price > '50'::numeric)
Planning Time: 0.215 ms
Execution Time: 0.141 ms
Stage 4: Execution / Dispatch (Capturing Real-Time Write & Read SQL)
Operations like .create(), .bulk_update(), .delete(), and raw cursor calls bypass qs.query. To capture what reaches the database driver in real time, use connection.execute_wrapper().
Technique: Custom Capture Context Manager
import time
from contextlib import contextmanager
from django.db import connection
from bookstore.models import Author, Book
@contextmanager
def capture_raw_sql():
records = []
def wrapper(execute, sql, params, many, context):
start_time = time.perf_counter()
try:
return execute(sql, params, many, context)
finally:
duration = (time.perf_counter() - start_time) * 1000 # in ms
records.append(
{
"sql": sql,
"params": params,
"duration_ms": round(duration, 3),
"many": many,
}
)
with connection.execute_wrapper(wrapper):
yield records
# Execute both read and write operations inside the context
with capture_raw_sql() as query_log:
# 1. INSERT
author = Author.objects.create(
name="Frank Herbert", email="frank@arrakis.org", rating=4.9
)
# 2. BULK INSERT
Book.objects.bulk_create(
[
Book(
title="Dune",
author=author,
price=25.00,
published_date="1965-08-01",
),
Book(
title="Dune Messiah",
author=author,
price=22.50,
published_date="1969-07-01",
),
]
)
# 3. UPDATE
Book.objects.filter(author=author).update(is_published=True)
# Inspect the captured entries
for i, item in enumerate(query_log, 1):
print(f"[{i}] Duration: {item['duration_ms']}ms | Many: {item['many']}")
print(f"SQL: {item['sql']}")
print(f"PARAMS: {item['params']}\n" + "-" * 70)
Output:
[1] Duration: 1.124ms | Many: False
SQL: INSERT INTO "bookstore_author" ("name", "email", "rating") VALUES (%s, %s, %s) RETURNING "bookstore_author"."id"
PARAMS: ('Frank Herbert', 'frank@arrakis.org', 4.9)
----------------------------------------------------------------------
[2] Duration: 0.845ms | Many: False
SQL: INSERT INTO "bookstore_book" ("title", "author_id", "price", "published_date", "is_published") VALUES (%s, %s, %s, %s, %s), (%s, %s, %s, %s, %s) RETURNING "bookstore_book"."id"
PARAMS: ('Dune', 1, '25.00', '1965-08-01', True, 'Dune Messiah', 1, '22.50', '1969-07-01', True)
----------------------------------------------------------------------
[3] Duration: 0.612ms | Many: False
SQL: UPDATE "bookstore_book" SET "is_published" = %s WHERE "bookstore_book"."author_id" = %s
PARAMS: (True, 1)
----------------------------------------------------------------------
Stage 5: Post-Execution Inspection & Unit Testing
When running automated test suites or debugging in DEBUG = True mode, you can inspect execution logs recorded in memory.
Technique 5.1: connection.queries
from django.db import connection, reset_queries
from bookstore.models import Author
# Clear the query log
reset_queries()
# Evaluate queries via list()
authors = list(
Author.objects.prefetch_related("books").filter(name__icontains="Herbert")
)
# Inspect connection.queries
print(f"Total SQL queries executed: {len(connection.queries)}")
for q in connection.queries:
print(f"[{q['time']}s] {q['sql']}")
Output:
Total SQL queries executed: 2
[0.0008s] SELECT "bookstore_author"."id", "bookstore_author"."name", "bookstore_author"."email", "bookstore_author"."rating" FROM "bookstore_author" WHERE UPPER("bookstore_author"."name"::text) LIKE UPPER('%Herbert%')
[0.0012s] SELECT "bookstore_book"."id", "bookstore_book"."title", "bookstore_book"."author_id", "bookstore_book"."price", "bookstore_book"."published_date", "bookstore_book"."is_published" FROM "bookstore_book" WHERE "bookstore_book"."author_id" IN (1)
Technique 5.2: Unit Test Assertions (assertNumQueries)
Ensure code does not introduce $N+1$ query regressions during test runs:
from django.test import TestCase
from bookstore.models import Author, Book
class BookQueryPerformanceTest(TestCase):
def setUp(self):
author = Author.objects.create(name="Author A", email="a@test.com")
for i in range(10):
Book.objects.create(
title=f"Book {i}",
author=author,
price=10.0,
published_date="2024-01-01",
)
def test_book_listing_query_budget(self):
# Assert that fetching 10 books and their authors executes exactly 1 query
with self.assertNumQueries(1):
books = list(Book.objects.select_related("author").all())
for book in books:
_ = book.author.name # Does not trigger additional queries
Stage 6: Application-Wide Telemetry & Development Logs
Technique 6.1: Real-time Terminal Logging via settings.py
Pipe every SQL query executed across your entire project directly into standard output:
# settings.py
LOGGING = {
"version": 1,
"disable_existing_loggers": False,
"handlers": {
"console": {
"level": "DEBUG",
"class": "logging.StreamHandler",
},
},
"loggers": {
"django.db.backends": {
"handlers": ["console"],
"level": "DEBUG",
"propagate": False,
},
},
}
Output Stream in Terminal:
(0.001) SELECT "bookstore_book"."id", "bookstore_book"."title" FROM "bookstore_book" WHERE "bookstore_book"."is_published" LIMIT 5; args=()
(0.000) SAVEPOINT "s140735824969536_x1"; args=None
(0.002) UPDATE "bookstore_author" SET "rating" = 4.8 WHERE "bookstore_author"."id" = 1; args=(4.8, 1)
(0.000) RELEASE SAVEPOINT "s140735824969536_x1"; args=None
Technique 6.2: Terminal Inspection with django-extensions
If you use django-extensions, run shell_plus with SQL printing enabled:
python manage.py shell_plus --print-sql
>>> from bookstore.models import Book
>>> Book.objects.filter(price__lte=15.00).first()
Output:
SELECT "bookstore_book"."id",
"bookstore_book"."title",
"bookstore_book"."author_id",
"bookstore_book"."price",
"bookstore_book"."published_date",
"bookstore_book"."is_published"
FROM "bookstore_book"
WHERE "bookstore_book"."price" <= '15.00'
ORDER BY "bookstore_book"."id" ASC
LIMIT 1
Execution time: 0.000412s [Database: default]
<Book: Basic Programming>
Complete Lifecycle Comparison Matrix
| Stage | Inspection Target | Method | Read/Write Supported? | Safe for Production? |
|---|---|---|---|---|
| 1. Construction | Query string preview | str(qs.query) |
Reads Only | Yes |
| 2. Compilation | Placeholder & Parameter tuple | qs.query.get_compiler().as_sql() |
Reads Only | Yes |
| 3. Plan Estimation | Optimizer execution cost | qs.explain(analyze=True) |
Reads Only | Exercise caution with analyze=True on heavy DBs |
| 4. Execution | Live cursor wrapper | connection.execute_wrapper() |
Reads & Writes (INSERT/UPDATE/DELETE) |
Yes |
| 5. Post-Execution | Historical session buffer | connection.queries |
Reads & Writes | No (DEBUG = True required) |
| 6. System-wide | Standard I/O output stream | django.db.backends logger |
Reads & Writes | No (creates high logging volume) |
