When the model query APIs don’t go far enough, you can fall back to writing raw SQL. Django gives you two ways of performing raw SQL queries: you can use
Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.
raw() manager method can be used to perform raw SQL queries that return model instances:
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:
You could then execute custom SQL like so:
Of course, this example isn’t very exciting – it’s exactly the same as running
raw() has a bunch of other options that make it very powerful.
Where’d the name of the
Person table come from in the above example? By default, Django figures out a database table name by joining the model’s “app label” – the name you used in
manage.py startapp – 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
For more details, check out the documentation for the
db_table option, which also lets you manually set the database table name.
raw() automatically maps fields in the query to fields on the model. The order of fields in your query doesn’t matter. In other words, both of the following queries work identically:
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
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:
raw() supports indexing, so if you need only the first result you can write:
However, the indexing and slicing are not performed at the database level. If you have a large number of
Person objects in your database, it is more efficient to limit the query at the SQL level:
Fields may also be left out:
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:
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:
If you need to perform parameterized queries, you can pass the
params argument to
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
Manager.raw() isn’t quite enough: you might need to perform queries that don’t map cleanly to models, or directly execute
DELETE queries. 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.fetchall() to return the resulting rows. For example:
Note that if you want to include literal percent signs in the query, you have to double them in the case you are passing parameters:
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 cost, you can return results as a
dict by using something like this:
Here is an example of the difference between the two:
cursor mostly implement the standard Python DB-API described in PEP 249, except when it comes to transaction handling. 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. Using a cursor as a context manager:
is equivalent to:
Manager methods is the preferred way to add table-level functionality to your models.
(For row-level functionality – i.e., functions that act on a single instance of a model object – use Model methods, not custom
Manager methods.) A custom
Manager method can return anything you want. It doesn’t have to return a
For example, this custom
Manager offers a method
with_counts(), which returns a list of all
OpinionPoll objects, each with an extra
num_responses attribute that is the result of an aggregate query:
With this example, you’d use
OpinionPoll.objects.with_counts() to return that list of
OpinionPoll objects with
num_responses attributes. Another thing to note about this example is that
Manager methods can access
self.model to get the model class to which they’re attached.
In the next chapter, we’ll show you Django’s generic views framework, which lets you save time in building Web sites that follow common patterns.