SQL Counting & Inspection · Django — interview questions

In Django, how do you see the SQL your ORM code runs with connection.queries and str(queryset.query), and how do the two differ?

juniormust knowfreq 58%

answer

one is a log, one is a preview
only records while DEBUG is on
list of dicts: sql and time
parameters pasted in, not quoted

basics

~10 s

str(queryset.query) previews the SELECT one QuerySet would run, with parameters crudely pasted in; django.db.connection.queries logs every statement actually executed on that connection, with timings, but only while DEBUG is True.

solid answer

~40 s

There are two tools with different jobs. str(qs.query) compiles one unevaluated QuerySet into SQL without running it; the parameters are substituted with Python string formatting, so quoting is not guaranteed and the text is for reading, not for pasting into psql. from django.db import connection; connection.queries is a log of what really hit the database on that connection, as a list of {'sql': ..., 'time': ...} dicts, and it includes the lazy lookups a template loop fires, plus BEGIN/COMMIT. It records only when DEBUG = True (or while a capture tool forces it), keeps at most the last 9,000 entries, and reset_queries() clears it; Django also clears it at the start of every request. For another database alias, use connections['alias'].queries.

There are two tools with different jobs. `str(qs.query)` compiles one unevaluated `QuerySet` into SQL without running it; the parameters are substituted with Python string formatting, so quoting is not guaranteed and the text is for reading, not for pasting into `psql`. `from django.db import connection; connection.queries` is a log of what really hit the database on that connection, as a list of `{'sql': ..., 'time': ...}` dicts, and it includes the lazy lookups a template loop fires, plus `BEGIN`/`COMMIT`. It records only when `DEBUG = True` (or while a capture tool forces it), keeps at most the last 9,000 entries, and `reset_queries()` clears it; Django also clears it at the start of every request. For another database alias, use `connections['alias'].queries`.

code

python · 12 lines

python

from django.db import connection, reset_queries

from school.models import Lesson

qs = Lesson.objects.filter(weekday=1)
print(qs.query)
sql, params = qs.query.sql_with_params()

reset_queries()
names = [lesson.teacher.name for lesson in qs]
print(len(connection.queries), "statements")
print(sum(float(q["time"]) for q in connection.queries), "seconds")

go deeper

for a junior

Know both tools by name: str(qs.query) previews one QuerySet's SQL, connection.queries lists what really ran, and the latter needs DEBUG = True.

for a middle

Explain why the preview hides per-row lookups, why its parameters are not safely quoted, and that the log is per alias, per thread and cleared on request_started.

for a senior

Show you know where the log fails you: empty with DEBUG off, capped at 9,000 entries, never reset in workers, and blind to other aliases.

for a principal

Frame these as developer-time tools and say what the team uses instead to see query volume where DEBUG is off.

Two different questions

When a Django page is slow, you usually want to answer one of two questions:

What SQL will this particular QuerySet produce? That is a question about one object you are holding in a shell. str(queryset.query) answers it.
What SQL did this block of code actually send? That is a question about everything that happened, including queries you never wrote yourself (a template touching lesson.teacher, the session lookup, the user lookup). connection.queries answers it.

Confusing the two is the most common beginner mistake: str(qs.query) shows one clean SELECT and hides the hundred extra queries that the loop over that QuerySet fires later.

`str(queryset.query)`: a preview of one statement

A QuerySet is lazy; its .query attribute holds the internal Query object that will be compiled when the QuerySet is evaluated. Calling str() on it compiles the SQL without executing it.

The parameters are substituted with Python's % formatting. The source says it plainly: values "won't necessarily be quoted correctly, since that is done by the database interface at execution time". Treat the text as something to read, not a statement to paste into a database console.
queryset.query.sql_with_params() returns the SQL with placeholders and the parameter tuple separately, which is what you want when you do need to rerun it.
The string is compiled for the default database alias, so on a multi-database project the dialect can differ from what the router would really use.
It shows only this QuerySet's own statement. prefetch_related() lookups run as separate queries at evaluation time and do not appear in it, and neither do the per-row lookups a loop may trigger.

`connection.queries`: a log of what really ran

django.db.connection is the default alias's connection for the current thread. Its queries property returns a list of dictionaries in execution order:

sql: the statement as the backend rendered it, with parameters filled in by the driver where the backend can do so.
time: the duration in seconds, as a string formatted to three decimals (for example '0.002').
It includes INSERT, UPDATE and DELETE statements, and transaction statements such as BEGIN, COMMIT, ROLLBACK and savepoints.
An executemany() call (as used by some bulk paths) is logged once, prefixed with the number of parameter sets.
Other databases have their own logs: connections['reporting'].queries.

Three behaviours matter in practice:

It only records when logging is on. A connection logs while settings.DEBUG is True or while its force_debug_cursor flag is set (which is how the test tools capture queries with DEBUG = False). In production with DEBUG = False, the list stays empty.
It is bounded. The log is a deque holding the last 9,000 entries; reading queries once it is full emits a warning that only the last 9,000 are returned. Older Django versions leaked memory here in long-running processes; the cap is why that no longer grows without limit.
It is reset per request. reset_queries() is connected to the request_started signal, so each request starts with an empty log. In a shell, a management command or a worker loop there is no request, so call reset_queries() yourself before the block you want to measure.

Side by side

	`str(qs.query)`	`connection.queries`
Runs SQL?	No	Records SQL that already ran
Scope	One QuerySet's own statement	Everything on that connection and thread
Needs `DEBUG = True`?	No	Yes, unless forced by a capture tool
Shows lazy per-row lookups?	No	Yes
Timing	None	`time` string per statement
Parameters	Pasted in, quoting not guaranteed	As rendered by the backend

A shell session

python

from django.db import connection, reset_queries
from school.models import Lesson

qs = Lesson.objects.filter(weekday=1)
print(qs.query)            # compiled, not executed

reset_queries()
for lesson in qs:
    print(lesson.teacher.name)
print(len(connection.queries))
for q in connection.queries[:3]:
    print(q["time"], q["sql"])

With DEBUG = True, the count printed is one query for the lessons plus one per lesson for its teacher: the preview showed one statement, the log shows the truth.

Common mistakes

Concluding that a view is efficient because str(qs.query) shows one statement.
Reading connection.queries in production and assuming nothing ran because the list is empty; it was never recording.
Summing q['time'] without converting it: the values are strings.
Forgetting that the log is per alias and per thread, so work on another database or another thread is not in connection.queries.

## Two different questions When a Django page is slow, you usually want to answer one of two questions: 1. **What SQL will this particular QuerySet produce?** That is a question about one object you are holding in a shell. `str(queryset.query)` answers it. 2. **What SQL did this block of code actually send?** That is a question about everything that happened, including queries you never wrote yourself (a template touching `lesson.teacher`, the session lookup, the user lookup). `connection.queries` answers it. Confusing the two is the most common beginner mistake: `str(qs.query)` shows one clean `SELECT` and hides the hundred extra queries that the loop over that QuerySet fires later. ## `str(queryset.query)`: a preview of one statement A `QuerySet` is lazy; its `.query` attribute holds the internal `Query` object that will be compiled when the QuerySet is evaluated. Calling `str()` on it compiles the SQL **without executing it**. - The parameters are substituted with Python's `%` formatting. The source says it plainly: values "won't necessarily be quoted correctly, since that is done by the database interface at execution time". Treat the text as something to read, not a statement to paste into a database console. - `queryset.query.sql_with_params()` returns the SQL with placeholders and the parameter tuple separately, which is what you want when you do need to rerun it. - The string is compiled for the default database alias, so on a multi-database project the dialect can differ from what the router would really use. - It shows only this QuerySet's own statement. `prefetch_related()` lookups run as separate queries at evaluation time and do not appear in it, and neither do the per-row lookups a loop may trigger. ## `connection.queries`: a log of what really ran `django.db.connection` is the default alias's connection for the current thread. Its `queries` property returns a list of dictionaries in execution order: - `sql`: the statement as the backend rendered it, with parameters filled in by the driver where the backend can do so. - `time`: the duration in seconds, as a **string** formatted to three decimals (for example `'0.002'`). - It includes INSERT, UPDATE and DELETE statements, and transaction statements such as `BEGIN`, `COMMIT`, `ROLLBACK` and savepoints. - An `executemany()` call (as used by some bulk paths) is logged once, prefixed with the number of parameter sets. - Other databases have their own logs: `connections['reporting'].queries`. Three behaviours matter in practice: - **It only records when logging is on.** A connection logs while `settings.DEBUG` is `True` or while its `force_debug_cursor` flag is set (which is how the test tools capture queries with `DEBUG = False`). In production with `DEBUG = False`, the list stays empty. - **It is bounded.** The log is a deque holding the last 9,000 entries; reading `queries` once it is full emits a warning that only the last 9,000 are returned. Older Django versions leaked memory here in long-running processes; the cap is why that no longer grows without limit. - **It is reset per request.** `reset_queries()` is connected to the `request_started` signal, so each request starts with an empty log. In a shell, a management command or a worker loop there is no request, so call `reset_queries()` yourself before the block you want to measure. ## Side by side | | `str(qs.query)` | `connection.queries` | |---|---|---| | Runs SQL? | No | Records SQL that already ran | | Scope | One QuerySet's own statement | Everything on that connection and thread | | Needs `DEBUG = True`? | No | Yes, unless forced by a capture tool | | Shows lazy per-row lookups? | No | Yes | | Timing | None | `time` string per statement | | Parameters | Pasted in, quoting not guaranteed | As rendered by the backend | ## A shell session ```python from django.db import connection, reset_queries from school.models import Lesson qs = Lesson.objects.filter(weekday=1) print(qs.query) # compiled, not executed reset_queries() for lesson in qs: print(lesson.teacher.name) print(len(connection.queries)) for q in connection.queries[:3]: print(q["time"], q["sql"]) ``` With `DEBUG = True`, the count printed is one query for the lessons plus one per lesson for its teacher: the preview showed one statement, the log shows the truth. ## Common mistakes 1. Concluding that a view is efficient because `str(qs.query)` shows one statement. 2. Reading `connection.queries` in production and assuming nothing ran because the list is empty; it was never recording. 3. Summing `q['time']` without converting it: the values are strings. 4. Forgetting that the log is per alias and per thread, so work on another database or another thread is not in `connection.queries`.

Why is connection.queries empty in a production shell even though the code clearly hit the database?
The connection only appends to its log while `settings.DEBUG` is `True` or its `force_debug_cursor` flag is set. Production runs with `DEBUG = False`, so nothing is recorded. Use a capture tool such as `CaptureQueriesContext`, which forces logging for its block, or enable SQL logging for a short, controlled window instead of switching `DEBUG` on.
Can a long-running worker with DEBUG = True still exhaust memory through connection.queries?
Not without bound in current Django: the log is a deque capped at 9,000 entries, and reading it once full emits a warning. Outside the test framework it is cleared by `reset_queries()`, which runs on `request_started`; a worker or management command never sends that signal, so the log simply sits at the cap. Running workers with `DEBUG = True` is still wrong for other reasons.

saying these in an interview costs you the question

str(qs.query) output can be pasted straight into psql unchanged
connection.queries records queries in production with DEBUG off
One SELECT in str(qs.query) proves the view runs one query
connection.queries only lists SELECT statements
connection.queries grows forever and must never be read

open as a page

With Django's ORM, what does QuerySet.explain() return, and why is explain(analyze=True) riskier than a plain explain()?

middleshould knowfreq 36%

basics

~20 s

QuerySet.explain() returns the database's execution plan for that QuerySet's SQL as a string; analyze=True makes PostgreSQL, MySQL or MariaDB actually run the statement, which costs real time and can change data through triggers or called functions.

open as a page

A Django school timetable page runs 400 queries per request; how would you use CaptureQueriesContext and the emitted SQL to find which code fires them?

seniorshould knowfreq 47%

basics

~20 s

Wrap one request to the page in django.test.utils.CaptureQueriesContext, normalise each captured statement's literals, and count them; the fingerprint repeated hundreds of times names the table and filter, which leads straight to the attribute access in the view or template.

open as a page

As a Django team lead, what policy would you set so that N+1 query regressions are caught before a release rather than by users?

principalnice to knowfreq 26%

basics

~20 s

Make query volume a tested property: CI captures the queries of each important page at two data sizes and fails if the count grows with the data or exceeds its budget, backed by visible SQL in development and fetch restrictions on hot paths.

open as a page

§explore

▾roadmaps that cover this2

§questions

In Django, how do you see the SQL your ORM code runs with connection.queries and str(queryset.query), and how do the two differ?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two different questions

str(queryset.query): a preview of one statement

connection.queries: a log of what really ran

Side by side

A shell session

Common mistakes

▾common follow-ups2 questions

▾red flags5 flags

With Django's ORM, what does QuerySet.explain() return, and why is explain(analyze=True) riskier than a plain explain()?

basics

A Django school timetable page runs 400 queries per request; how would you use CaptureQueriesContext and the emitted SQL to find which code fires them?

basics

As a Django team lead, what policy would you set so that N+1 query regressions are caught before a release rather than by users?

basics

Two different questions

str(queryset.query): a preview of one statement

connection.queries: a log of what really ran

Side by side

A shell session

Common mistakes

explore

questions

answer

go deeper

`str(queryset.query)`: a preview of one statement

`connection.queries`: a log of what really ran

`str(queryset.query)`: a preview of one statement

`connection.queries`: a log of what really ran