Performing Raw SQL Queries in Django: RawSQL, raw(), Field Mapping, Parameters, and Safety Considerations

1. Introduction

Django provides three ways to execute raw SQL:

• Using RawSQL inside ORM expressions
• Using Manager.raw() to return model instances
• Executing SQL directly without the model layer

Before using raw SQL, always check whether the ORM can express your query. Django’s QuerySet API, annotations, aggregates, and custom expressions cover most use cases.

2. Raw SQL Fragments

Sometimes you need to embed SQL directly inside ORM queries—for example, inside annotate() or filter(). Use RawSQL for arbitrary SQL fragments or Func for database functions.

3. Executing Raw Queries with raw()

The raw() method on a Manager executes SQL and returns a RawQuerySet that yields model instances.

Example:

for p in Person.objects.raw("SELECT * FROM myapp_person"):
    print(p)

This behaves similarly to Person.objects.all(), but raw() supports more advanced SQL.

Table name resolution

Django constructs table names using the app label and model name unless db_table is overridden.

Warnings:

• Django does not validate your SQL.
• If your SQL does not return rows, you may get cryptic errors.
• MySQL may silently coerce types, causing unexpected matches.

4. Mapping Query Fields to Model Fields

raw() maps SQL columns to model fields by name. Order does not matter.

Example:

SELECT id, first_name, last_name FROM myapp_person

You can also use AS to rename fields:

SELECT first AS first_name, last AS last_name, pk AS id FROM some_table

Or use the translations argument:

name_map = {"first": "first_name", "last": "last_name"}
Person.objects.raw("SELECT * FROM some_table", translations=name_map)

5. Indexing RawQuerySet

You can index a RawQuerySet:

first_person = Person.objects.raw("SELECT * FROM myapp_person")[0]

But indexing happens in Python, not SQL. For efficiency, limit the SQL query:

SELECT * FROM myapp_person LIMIT 1

6. Deferred Fields

You may omit fields from the SELECT clause:

Person.objects.raw("SELECT id, first_name FROM myapp_person")

Missing fields are loaded lazily when accessed.

Important: The primary key must always be included, or Django raises FieldDoesNotExist.

7. Passing Parameters Safely

Use the params argument to avoid SQL injection:

lname = "Doe"
Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])

For dictionaries:

WHERE last_name = %(lname)s

Note: SQLite does not support dictionary parameters.

Security Warning

Never use Python string formatting to build SQL:

"WHERE last_name = %s" % lname  # unsafe

And never quote placeholders:

"WHERE last_name = '%s'"  # unsafe

Always rely on params to prevent SQL injection.

Conclusion

Raw SQL is a powerful tool in Django, but it must be used sparingly and safely. RawSQL is ideal for small SQL fragments, raw() is useful when you need model instances from custom SQL, and direct SQL execution gives full control. Always use parameterized queries and avoid string formatting to protect against SQL injection.

1. Introduction

Sometimes Manager.raw() is not enough—especially when you need to run SQL that doesn’t map to a model, or when executing UPDATE, INSERT, or DELETE statements. In such cases, Django allows you to bypass the ORM and interact directly with the database.

The main entry point is django.db.connection, which represents the default database connection.

2. Executing SQL with a Cursor

To execute custom SQL, obtain a cursor using connection.cursor() and call cursor.execute():

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()
    return row

Important: Never put quotes around %s placeholders. Django and the DB-API handle quoting and escaping automatically.

Using literal percent signs

If your SQL contains literal % characters, double them when using parameters:

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

3. Working with Multiple Databases

If your project uses multiple databases, use django.db.connections to access a specific connection:

from django.db import connections

with connections["my_db_alias"].cursor() as cursor:
    ...

4. Returning Results as Dictionaries

By default, DB-API cursors return rows as tuples. To return rows as dictionaries, you can define a helper:

def dictfetchall(cursor):
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

This is useful when you want column names included in the result.

5. Returning Results as Named Tuples

Another option is to use collections.namedtuple():

from collections import namedtuple

def namedtuplefetchall(cursor):
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

Named tuples allow attribute access (row.id) as well as index access (row[0]).

Example comparison:

cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
cursor.fetchall()
# → ((54360982, None), (54360880, None))

dictfetchall(cursor)
# → [{'id': 54360982, 'parent_id': None}, ...]

namedtuplefetchall(cursor)
# → [Result(id=54360982, parent_id=None), ...]

6. Connections and Cursors

Django’s connection and cursor follow the Python DB-API (PEP 249), except for transaction handling. SQL parameters always use %s placeholders, even for SQLite.

Using a cursor as a context manager

with connection.cursor() as c:
    c.execute(...)

This is equivalent to manually opening and closing the cursor.

7. Calling Stored Procedures

You can call stored procedures using cursor.callproc():

with connection.cursor() as cursor:
    cursor.callproc("test_procedure", [1, "test"])

Only Oracle supports keyword parameters (kparams) among Django’s built-in backends.

Conclusion

Executing custom SQL directly gives you full control over the database when the ORM is not sufficient. Django’s cursor interface allows safe parameterized queries, structured result handling, and support for multiple databases and stored procedures. Always use placeholders and parameter lists to avoid SQL injection vulnerabilities.