You should be very careful whenever you write raw SQL. Every time you use
it, you should properly escape any parameters that the user can control
by using params in order to protect against SQL injection attacks.
Please read more about SQL injection protection.

This method takes a raw SQL query, executes it, and returns a
django.db.models.query.RawQuerySet instance. This RawQuerySet instance
can be iterated over just like a normal
QuerySet to provide object instances.

This is best illustrated with an example. Suppose you have the following model:

Of course, this example isn’t very exciting – it’s exactly the same as
running Person.objects.all(). However, raw() has a bunch of other
options that make it very powerful.

Model table names

Where did the name of the Person table come from in that example?

By default, Django figures out a database table name by joining the
model’s “app label” – the name you used in manage.pystartapp – to
the model’s class name, with an underscore between them. In the example
we’ve assumed that the Person model lives in an app named myapp,
so its table would be myapp_person.

For more details check out the documentation for the
db_table option, which also lets you manually set the
database table name.

Warning

No checking is done on the SQL statement that is passed in to .raw().
Django expects that the statement will return a set of rows from the
database, but does nothing to enforce that. If the query does not
return rows, a (possibly cryptic) error will result.

Warning

If you are performing queries on MySQL, note that MySQL’s silent type coercion
may cause unexpected results when mixing types. If you query on a string
type column, but with an integer value, MySQL will coerce the types of all values
in the table to an integer before performing the comparison. For example, if your
table contains the values 'abc', 'def' and you query for WHEREmycolumn=0,
both rows will match. To prevent this, perform the correct typecasting
before using the value in a query.

Matching is done by name. This means that you can use SQL’s AS clauses to
map fields in the query to model fields. So if you had some other table that
had Person data in it, you could easily map it into Person instances:

>>> Person.objects.raw('''SELECT first AS first_name,... last AS last_name,... bd AS birth_date,... pk AS id,... FROM some_other_table''')

As long as the names match, the model instances will be created correctly.

Alternatively, you can map fields in the query to model fields using the
translations argument to raw(). This is a dictionary mapping names of
fields in the query to names of fields on the model. For example, the above
query could also be written:

The Person objects returned by this query will be deferred model instances
(see defer()). This means that the
fields that are omitted from the query will be loaded on demand. For example:

>>> forpinPerson.objects.raw('SELECT id, first_name FROM myapp_person'):... print(p.first_name,# This will be retrieved by the original query... p.last_name)# This will be retrieved on demand...John SmithJane Jones

From outward appearances, this looks like the query has retrieved both
the first name and last name. However, this example actually issued 3
queries. Only the first names were retrieved by the raw() query – the
last names were both retrieved on demand when they were printed.

There is only one field that you can’t leave out - the primary key
field. Django uses the primary key to identify model instances, so it
must always be included in a raw query. An InvalidQuery exception
will be raised if you forget to include the primary key.

You can also execute queries containing fields that aren’t defined on the
model. For example, we could use PostgreSQL’s age() function to get a list
of people with their ages calculated by the database:

>>> people=Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')>>> forpinpeople:... print("%s is %s."%(p.first_name,p.age))John is 37.Jane is 42....

You can often avoid using raw SQL to compute annotations by instead using a
Func() expression.

params is a list or dictionary of parameters. You’ll use %s
placeholders in the query string for a list, or %(key)s
placeholders for a dictionary (where key is replaced by a
dictionary key, of course), regardless of your database engine. Such
placeholders will be replaced with parameters from the params
argument.

Note

Dictionary params are not supported with the SQLite backend; with
this backend, you must pass parameters as a list.

Warning

Do not use string formatting on raw queries or quote placeholders in your
SQL strings!

You might also think you should write your query like this (with quotes
around %s):

>>> query="SELECT * FROM myapp_person WHERE last_name = '%s'"

Don’t make either of these mistakes.

As discussed in SQL injection protection, using the params
argument and leaving the placeholders unquoted protects you from SQL
injection attacks, a common exploit where attackers inject arbitrary
SQL into your database. If you use string interpolation or quote the
placeholder, you’re at risk for SQL injection.

In these cases, you can always access the database directly, routing around
the model layer entirely.

The object django.db.connection represents the default database
connection. To use the database connection, call connection.cursor() to
get a cursor object. Then, call cursor.execute(sql,[params]) to execute
the SQL and cursor.fetchone() or cursor.fetchall() to return the
resulting rows.

If you are using more than one database, you can
use django.db.connections to obtain the connection (and cursor) for a
specific database. django.db.connections is a dictionary-like
object that allows you to retrieve a specific connection using its
alias:

By default, the Python DB API will return results without their field names,
which means you end up with a list of values, rather than a dict. At a
small performance and memory cost, you can return results as a dict by
using something like this:

defdictfetchall(cursor):"Return all rows from a cursor as a dict"columns=[col[0]forcolincursor.description]return[dict(zip(columns,row))forrowincursor.fetchall()]

Another option is to use collections.namedtuple() from the Python
standard library. A namedtuple is a tuple-like object that has fields
accessible by attribute lookup; it’s also indexable and iterable. Results are
immutable and accessible by field names or indices, which might be useful:

fromcollectionsimportnamedtupledefnamedtuplefetchall(cursor):"Return all rows from a cursor as a namedtuple"desc=cursor.descriptionnt_result=namedtuple('Result',[col[0]forcolindesc])return[nt_result(*row)forrowincursor.fetchall()]

If you’re not familiar with the Python DB-API, note that the SQL statement in
cursor.execute() uses placeholders, "%s", rather than adding
parameters directly within the SQL. If you use this technique, the underlying
database library will automatically escape your parameters as necessary.

Also note that Django expects the "%s" placeholder, not the "?"
placeholder, which is used by the SQLite Python bindings. This is for the sake
of consistency and sanity.

Calls a database stored procedure with the given name. A sequence
(params) or dictionary (kparams) of input parameters may be
provided. Most databases don’t support kparams. Of Django’s built-in
backends, only Oracle supports it.