skip to content

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?

level: juniorimportance: must knowfreq 58%

answer

  1. one is a log, one is a preview
  2. only records while DEBUG is on
  3. list of dicts: sql and time
  4. 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`.

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: 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