In Django, when do you use Manager.raw() rather than connection.cursor(), and what does each one give you back?

juniormust knowfreq 55%

answer

one maps rows to models, one does not
column names matched to field names
primary key column is mandatory
writes and non-model rows need the cursor

basics

~20 s

Manager.raw() runs a SELECT and returns a lazy RawQuerySet of model instances, matched to fields by column name, so the primary key must be selected. connection.cursor() bypasses models: it runs any statement and returns plain tuples.

solid answer

~40 s

Person.objects.raw(sql, params) returns a RawQuerySet: nothing runs until you iterate it, and each row becomes a Person built by matching column names to field names (use AS aliases or the translations dict when they differ). The primary key must be in the select list or Django raises FieldDoesNotExist; columns you leave out become deferred fields loaded one query each on access, and extra columns become plain attributes. raw() only makes sense for statements that return rows of that model. For anything else — aggregates that match no model, UPDATE/INSERT/DELETE, calling a stored procedure — use with connection.cursor() as cursor: cursor.execute(sql, params) and read fetchone()/fetchall(), which give tuples; turn them into dicts yourself from cursor.description.

`Person.objects.raw(sql, params)` returns a `RawQuerySet`: nothing runs until you iterate it, and each row becomes a `Person` built by matching column names to field names (use `AS` aliases or the `translations` dict when they differ). The primary key must be in the select list or Django raises `FieldDoesNotExist`; columns you leave out become deferred fields loaded one query each on access, and extra columns become plain attributes. `raw()` only makes sense for statements that return rows of that model. For anything else — aggregates that match no model, `UPDATE`/`INSERT`/`DELETE`, calling a stored procedure — use `with connection.cursor() as cursor: cursor.execute(sql, params)` and read `fetchone()`/`fetchall()`, which give tuples; turn them into dicts yourself from `cursor.description`.

code

python · 15 lines

python

from django.db import models


class Person(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)


people = Person.objects.raw(
    "SELECT p.id, p.first_name, COUNT(o.id) AS order_count "
    "FROM myapp_person p LEFT JOIN myapp_order o ON o.person_id = p.id "
    "GROUP BY p.id, p.first_name"
)
for p in people:  # the query runs here, not above
    print(p.first_name, p.order_count)  # last_name would be a deferred load

go deeper

for a junior

Recall the two tools and their return types: raw() yields model instances, the cursor yields tuples. Remember the primary key must be selected.

for a middle

Explain name-based mapping, translations, deferred fields on omitted columns and extra columns becoming attributes, and why the cursor is the tool for writes.

for a senior

Show you know the costs: omitted columns cause per-row queries, raw SQL skips signals and save(), and you keep raw SQL isolated behind a manager method.

for a principal

Frame raw SQL as a boundary decision: which queries earn a hand-written statement, and how the team keeps them reviewed, tested against the production engine and few in number.

Two ways out of the ORM

Django gives you two built-in escape hatches when a QuerySet cannot express the SQL you need:

Manager.raw() — you write a SELECT, Django still builds model instances from the rows.
connection.cursor() — you talk to the database through a DB-API cursor and get plain Python tuples back; the model layer is not involved at all.

Choosing between them is mostly about the shape of the result: if every row is "a Person", use raw(); if the rows are something else, or the statement returns no rows, use the cursor.

How `raw()` maps rows to a model

Person.objects.raw("SELECT id, first_name, last_name FROM myapp_person WHERE ...", [params]) returns a RawQuerySet. The mapping rules:

Matching is by name, not position. Column order in the SELECT does not matter; each column name is compared with the database column of each model field — first_name, or publisher_id for a ForeignKey named publisher.
Different names need a mapping. Either alias in SQL (SELECT pk AS id, first AS first_name ...) or pass translations={"first": "first_name", "pk": "id"}.
The primary key is mandatory. Django identifies instances by primary key; if it is missing, iteration raises django.core.exceptions.FieldDoesNotExist with the message "Raw query must include the primary key".
Missing columns become deferred fields. Leave out last_name and the instances behave like those from defer(): touching p.last_name fires one extra query per instance under the default fetch mode (6.1's fetch_mode(FETCH_PEERS) batches such loads across the result set).
Extra columns become attributes. A computed column such as COUNT(*) AS order_count is set as p.order_count on each instance, which is how you get annotations out of raw SQL.

The RawQuerySet is lazy and caches its results once evaluated, like a normal QuerySet. It supports iteration, len(), indexing, using(), iterator() and prefetch_related(), but not filter(), order_by() or select_related() — it is a finished statement, not a query builder. Indexing ([0]) is done in Python after fetching, so write LIMIT 1 in the SQL if you only want one row. Django performs no validation of the statement: if it returns no rows at all (a plain UPDATE, say), you get a confusing error.

How the cursor works

from django.db import connection gives you the default database's connection (connections["alias"] for others). The idiom is:

python

from django.db import connection

def monthly_totals(year):
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT date_trunc('month', created) AS m, SUM(total) "
            "FROM shop_order WHERE EXTRACT(year FROM created) = %s GROUP BY m ORDER BY m",
            [year],
        )
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

Key facts:

cursor.execute(sql, params) follows PEP 249 with Django's %s placeholder on every backend (not SQLite's native ?).
fetchone()/fetchall() return tuples; Django ships no dict-cursor, so the docs show a small dictfetchall() helper built from cursor.description, or a namedtuple variant.
The cursor can run writes: UPDATE, INSERT, DELETE, DDL, and cursor.callproc() for stored procedures.
Use it as a context manager so the cursor is closed even on error.

Side-by-side

	`Manager.raw()`	`connection.cursor()`
Returns	`RawQuerySet` of model instances	tuples from `fetchone()`/`fetchall()`
Statement types	row-returning `SELECT` for one model	anything, including writes
Laziness	lazy, cached after first evaluation	runs immediately on `execute()`
Missing columns	deferred fields, extra queries	not applicable
Primary key	must be selected	not needed
Prefetching	`prefetch_related()` supported	none

Mistakes that show up in review

Using raw() for a report that is not one model. A GROUP BY region summary has no primary key of any model, so raw() fails; that belongs in a cursor.
Selecting too little. A raw query that fetches only id and name, followed by a template that prints email, quietly issues one query per row.
Indexing instead of limiting. Person.objects.raw(sql)[0] fetches every row and discards the rest; write LIMIT 1.
Forgetting the cursor is not a model. Rows from fetchall() have no methods, no related managers and no field conversion beyond what the driver does.
Hard-coding table names. Take them from Model._meta.db_table so a db_table change does not break the SQL.

What neither does for you

Both bypass the ORM's query building, so neither is portable across database engines, and neither participates in model-level behaviour that lives in Python: a raw UPDATE through the cursor sends no pre_save/post_save signals and runs no save() override. Both still run inside Django's transaction management — in autocommit mode each statement commits on its own unless you wrap it in an atomic block. And both take parameters separately from the SQL string; interpolating values into the string is how raw SQL becomes an injection hole.

## Two ways out of the ORM Django gives you two built-in escape hatches when a `QuerySet` cannot express the SQL you need: - **`Manager.raw()`** — you write a `SELECT`, Django still builds **model instances** from the rows. - **`connection.cursor()`** — you talk to the database through a **DB-API cursor** and get plain Python tuples back; the model layer is not involved at all. Choosing between them is mostly about the shape of the result: if every row is "a `Person`", use `raw()`; if the rows are something else, or the statement returns no rows, use the cursor. ## How `raw()` maps rows to a model `Person.objects.raw("SELECT id, first_name, last_name FROM myapp_person WHERE ...", [params])` returns a **`RawQuerySet`**. The mapping rules: 1. **Matching is by name, not position.** Column order in the `SELECT` does not matter; each column name is compared with the database column of each model field — `first_name`, or `publisher_id` for a `ForeignKey` named `publisher`. 2. **Different names need a mapping.** Either alias in SQL (`SELECT pk AS id, first AS first_name ...`) or pass `translations={"first": "first_name", "pk": "id"}`. 3. **The primary key is mandatory.** Django identifies instances by primary key; if it is missing, iteration raises `django.core.exceptions.FieldDoesNotExist` with the message "Raw query must include the primary key". 4. **Missing columns become deferred fields.** Leave out `last_name` and the instances behave like those from `defer()`: touching `p.last_name` fires one extra query per instance under the default fetch mode (6.1's `fetch_mode(FETCH_PEERS)` batches such loads across the result set). 5. **Extra columns become attributes.** A computed column such as `COUNT(*) AS order_count` is set as `p.order_count` on each instance, which is how you get annotations out of raw SQL. The `RawQuerySet` is **lazy** and caches its results once evaluated, like a normal `QuerySet`. It supports iteration, `len()`, indexing, `using()`, `iterator()` and `prefetch_related()`, but not `filter()`, `order_by()` or `select_related()` — it is a finished statement, not a query builder. Indexing (`[0]`) is done in Python after fetching, so write `LIMIT 1` in the SQL if you only want one row. Django performs **no validation** of the statement: if it returns no rows at all (a plain `UPDATE`, say), you get a confusing error. ## How the cursor works `from django.db import connection` gives you the default database's connection (`connections["alias"]` for others). The idiom is: ```python from django.db import connection def monthly_totals(year): with connection.cursor() as cursor: cursor.execute( "SELECT date_trunc('month', created) AS m, SUM(total) " "FROM shop_order WHERE EXTRACT(year FROM created) = %s GROUP BY m ORDER BY m", [year], ) columns = [col[0] for col in cursor.description] return [dict(zip(columns, row)) for row in cursor.fetchall()] ``` Key facts: - `cursor.execute(sql, params)` follows **PEP 249** with Django's `%s` placeholder on every backend (not SQLite's native `?`). - `fetchone()`/`fetchall()` return **tuples**; Django ships no dict-cursor, so the docs show a small `dictfetchall()` helper built from `cursor.description`, or a `namedtuple` variant. - The cursor can run **writes**: `UPDATE`, `INSERT`, `DELETE`, DDL, and `cursor.callproc()` for stored procedures. - Use it as a context manager so the cursor is closed even on error. ## Side-by-side | | `Manager.raw()` | `connection.cursor()` | |---|---|---| | Returns | `RawQuerySet` of model instances | tuples from `fetchone()`/`fetchall()` | | Statement types | row-returning `SELECT` for one model | anything, including writes | | Laziness | lazy, cached after first evaluation | runs immediately on `execute()` | | Missing columns | deferred fields, extra queries | not applicable | | Primary key | must be selected | not needed | | Prefetching | `prefetch_related()` supported | none | ## Mistakes that show up in review - **Using `raw()` for a report that is not one model.** A `GROUP BY region` summary has no primary key of any model, so `raw()` fails; that belongs in a cursor. - **Selecting too little.** A raw query that fetches only `id` and `name`, followed by a template that prints `email`, quietly issues one query per row. - **Indexing instead of limiting.** `Person.objects.raw(sql)[0]` fetches every row and discards the rest; write `LIMIT 1`. - **Forgetting the cursor is not a model.** Rows from `fetchall()` have no methods, no related managers and no field conversion beyond what the driver does. - **Hard-coding table names.** Take them from `Model._meta.db_table` so a `db_table` change does not break the SQL. ## What neither does for you Both bypass the ORM's **query building**, so neither is portable across database engines, and neither participates in model-level behaviour that lives in Python: a raw `UPDATE` through the cursor sends no `pre_save`/`post_save` signals and runs no `save()` override. Both still run inside Django's transaction management — in autocommit mode each statement commits on its own unless you wrap it in an atomic block. And both take **parameters separately from the SQL string**; interpolating values into the string is how raw SQL becomes an injection hole.

What happens if a raw() query omits a column that the loop then reads?
The instance treats that field as deferred. Under the default fetch mode, reading it triggers a separate `SELECT` for that single instance, so a loop over 500 rows that touches an omitted field issues 500 extra queries. Either select every column the code uses or accept the lazy loads knowingly.
Can you call filter() or order_by() on the result of Manager.raw()?
No. `RawQuerySet` is a finished SQL statement, not a query builder, so it has no `filter()`, `exclude()`, `order_by()` or `select_related()`. Put the conditions and ordering in the SQL itself. It does keep `prefetch_related()`, `using()` and `iterator()`.
How do you get dictionaries instead of tuples from a Django cursor?
Django has no dict cursor. Build dicts from `cursor.description`, which lists column names in order: `cols = [c[0] for c in cursor.description]` then `[dict(zip(cols, row)) for row in cursor.fetchall()]`. A `namedtuple` built from the same names works too.

saying these in an interview costs you the question

Says raw() returns dictionaries or tuples rather than model instances
Believes raw() maps columns to fields by position in the SELECT
Uses raw() for a plain UPDATE or DELETE that returns no rows
Claims the primary key can be left out of a raw() query
Expects filter() to be chainable onto a RawQuerySet
Believes cursor.execute() uses '?' placeholders on SQLite in Django

In Django, when do you use Manager.raw() rather than connection.cursor(), and what does each one give you back?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two ways out of the ORM

How raw() maps rows to a model

How the cursor works

Side-by-side

Mistakes that show up in review

What neither does for you

▾common follow-ups3 questions

▾red flags6 flags

Two ways out of the ORM

How raw() maps rows to a model

How the cursor works

Side-by-side

Mistakes that show up in review

What neither does for you

answer

go deeper

How `raw()` maps rows to a model

How `raw()` maps rows to a model