| 1 |
========================== |
| 2 |
Performing raw SQL queries |
| 3 |
========================== |
| 4 |
|
| 5 |
.. currentmodule:: django.db.models |
| 6 |
|
| 7 |
Django gives you two ways of performing raw SQL queries: you can use |
| 8 |
:meth:`Manager.raw()` to `perform raw queries and return model instances`__, or |
| 9 |
you can avoid the model layer entirely and `execute custom SQL directly`__. |
| 10 |
|
| 11 |
__ `performing raw queries`_ |
| 12 |
__ `executing custom SQL directly`_ |
| 13 |
|
| 14 |
.. admonition:: Explore the ORM before using raw SQL! |
| 15 |
|
| 16 |
The Django ORM provides many tools to express queries without writing raw |
| 17 |
SQL. For example: |
| 18 |
|
| 19 |
* The :doc:`QuerySet API </ref/models/querysets>` is extensive. |
| 20 |
* You can :meth:`annotate <.QuerySet.annotate>` and :doc:`aggregate |
| 21 |
</topics/db/aggregation>` using many built-in :doc:`database functions |
| 22 |
</ref/models/database-functions>`. Beyond those, you can create |
| 23 |
:doc:`custom query expressions </ref/models/expressions/>`. |
| 24 |
|
| 25 |
Before using raw SQL, explore :doc:`the ORM </topics/db/index>`. Ask on |
| 26 |
one of :doc:`the support channels </faq/help>` to see if the ORM supports |
| 27 |
your use case. |
| 28 |
|
| 29 |
.. warning:: |
| 30 |
|
| 31 |
You should be very careful whenever you write raw SQL. Every time you use |
| 32 |
it, you should properly escape any parameters that the user can control |
| 33 |
by using ``params`` in order to protect against SQL injection attacks. |
| 34 |
Please read more about :ref:`SQL injection protection |
| 35 |
<sql-injection-protection>`. |
| 36 |
|
| 37 |
.. _executing-raw-queries: |
| 38 |
|
| 39 |
Performing raw queries |
| 40 |
====================== |
| 41 |
|
| 42 |
The ``raw()`` manager method can be used to perform raw SQL queries that |
| 43 |
return model instances: |
| 44 |
|
| 45 |
.. method:: Manager.raw(raw_query, params=(), translations=None) |
| 46 |
|
| 47 |
This method takes a raw SQL query, executes it, and returns a |
| 48 |
``django.db.models.query.RawQuerySet`` instance. This ``RawQuerySet`` instance |
| 49 |
can be iterated over like a normal :class:`~django.db.models.query.QuerySet` to |
| 50 |
provide object instances. |
| 51 |
|
| 52 |
This is best illustrated with an example. Suppose you have the following model:: |
| 53 |
|
| 54 |
class Person(models.Model): |
| 55 |
first_name = models.CharField(...) |
| 56 |
last_name = models.CharField(...) |
| 57 |
birth_date = models.DateField(...) |
| 58 |
|
| 59 |
You could then execute custom SQL like so: |
| 60 |
|
| 61 |
.. code-block:: pycon |
| 62 |
|
| 63 |
>>> for p in Person.objects.raw("SELECT * FROM myapp_person"): |
| 64 |
... print(p) |
| 65 |
... |
| 66 |
John Smith |
| 67 |
Jane Jones |
| 68 |
|
| 69 |
This example isn't very exciting -- it's exactly the same as running |
| 70 |
``Person.objects.all()``. However, ``raw()`` has a bunch of other options that |
| 71 |
make it very powerful. |
| 72 |
|
| 73 |
.. admonition:: Model table names |
| 74 |
|
| 75 |
Where did the name of the ``Person`` table come from in that example? |
| 76 |
|
| 77 |
By default, Django figures out a database table name by joining the |
| 78 |
model's "app label" -- the name you used in ``manage.py startapp`` -- to |
| 79 |
the model's class name, with an underscore between them. In the example |
| 80 |
we've assumed that the ``Person`` model lives in an app named ``myapp``, |
| 81 |
so its table would be ``myapp_person``. |
| 82 |
|
| 83 |
For more details check out the documentation for the |
| 84 |
:attr:`~Options.db_table` option, which also lets you manually set the |
| 85 |
database table name. |
| 86 |
|
| 87 |
.. warning:: |
| 88 |
|
| 89 |
No checking is done on the SQL statement that is passed in to ``.raw()``. |
| 90 |
Django expects that the statement will return a set of rows from the |
| 91 |
database, but does nothing to enforce that. If the query does not |
| 92 |
return rows, a (possibly cryptic) error will result. |
| 93 |
|
| 94 |
.. warning:: |
| 95 |
|
| 96 |
If you are performing queries on MySQL, note that MySQL's silent type coercion |
| 97 |
may cause unexpected results when mixing types. If you query on a string |
| 98 |
type column, but with an integer value, MySQL will coerce the types of all values |
| 99 |
in the table to an integer before performing the comparison. For example, if your |
| 100 |
table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``, |
| 101 |
both rows will match. To prevent this, perform the correct typecasting |
| 102 |
before using the value in a query. |
| 103 |
|
| 104 |
Mapping query fields to model fields |
| 105 |
------------------------------------ |
| 106 |
|
| 107 |
``raw()`` automatically maps fields in the query to fields on the model. |
| 108 |
|
| 109 |
The order of fields in your query doesn't matter. In other words, both |
| 110 |
of the following queries work identically: |
| 111 |
|
| 112 |
.. code-block:: pycon |
| 113 |
|
| 114 |
>>> Person.objects.raw("SELECT id, first_name, last_name, birth_date FROM myapp_person") |
| 115 |
>>> Person.objects.raw("SELECT last_name, birth_date, first_name, id FROM myapp_person") |
| 116 |
|
| 117 |
Matching is done by name. This means that you can use SQL's ``AS`` clauses to |
| 118 |
map fields in the query to model fields. So if you had some other table that |
| 119 |
had ``Person`` data in it, you could easily map it into ``Person`` instances: |
| 120 |
|
| 121 |
.. code-block:: pycon |
| 122 |
|
| 123 |
>>> Person.objects.raw(""" |
| 124 |
... SELECT first AS first_name, |
| 125 |
... last AS last_name, |
| 126 |
... bd AS birth_date, |
| 127 |
... pk AS id, |
| 128 |
... FROM some_other_table |
| 129 |
... """) |
| 130 |
... |
| 131 |
|
| 132 |
As long as the names match, the model instances will be created correctly. |
| 133 |
|
| 134 |
Alternatively, you can map fields in the query to model fields using the |
| 135 |
``translations`` argument to ``raw()``. This is a dictionary mapping names of |
| 136 |
fields in the query to names of fields on the model. For example, the above |
| 137 |
query could also be written: |
| 138 |
|
| 139 |
.. code-block:: pycon |
| 140 |
|
| 141 |
>>> name_map = {"first": "first_name", "last": "last_name", "bd": "birth_date", "pk": "id"} |
| 142 |
>>> Person.objects.raw("SELECT * FROM some_other_table", translations=name_map) |
| 143 |
|
| 144 |
Index lookups |
| 145 |
------------- |
| 146 |
|
| 147 |
``raw()`` supports indexing, so if you need only the first result you can |
| 148 |
write: |
| 149 |
|
| 150 |
.. code-block:: pycon |
| 151 |
|
| 152 |
>>> first_person = Person.objects.raw("SELECT * FROM myapp_person")[0] |
| 153 |
|
| 154 |
However, the indexing and slicing are not performed at the database level. If |
| 155 |
you have a large number of ``Person`` objects in your database, it is more |
| 156 |
efficient to limit the query at the SQL level: |
| 157 |
|
| 158 |
.. code-block:: pycon |
| 159 |
|
| 160 |
>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0] |
| 161 |
|
| 162 |
Deferring model fields |
| 163 |
---------------------- |
| 164 |
|
| 165 |
Fields may also be left out: |
| 166 |
|
| 167 |
.. code-block:: pycon |
| 168 |
|
| 169 |
>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person") |
| 170 |
|
| 171 |
The ``Person`` objects returned by this query will be deferred model instances |
| 172 |
(see :meth:`~django.db.models.query.QuerySet.defer()`). This means that the |
| 173 |
fields that are omitted from the query will be loaded on demand. For example: |
| 174 |
|
| 175 |
.. code-block:: pycon |
| 176 |
|
| 177 |
>>> for p in Person.objects.raw("SELECT id, first_name FROM myapp_person"): |
| 178 |
... print( |
| 179 |
... p.first_name, # This will be retrieved by the original query |
| 180 |
... p.last_name, # This will be retrieved on demand |
| 181 |
... ) |
| 182 |
... |
| 183 |
John Smith |
| 184 |
Jane Jones |
| 185 |
|
| 186 |
From outward appearances, this looks like the query has retrieved both |
| 187 |
the first name and last name. However, this example actually issued 3 |
| 188 |
queries. Only the first names were retrieved by the ``raw()`` query -- the |
| 189 |
last names were both retrieved on demand when they were printed. |
| 190 |
|
| 191 |
There is only one field that you can't leave out - the primary key |
| 192 |
field. Django uses the primary key to identify model instances, so it |
| 193 |
must always be included in a raw query. A |
| 194 |
:class:`~django.core.exceptions.FieldDoesNotExist` exception will be raised if |
| 195 |
you forget to include the primary key. |
| 196 |
|
| 197 |
Adding annotations |
| 198 |
------------------ |
| 199 |
|
| 200 |
You can also execute queries containing fields that aren't defined on the |
| 201 |
model. For example, we could use `PostgreSQL's age() function`__ to get a list |
| 202 |
of people with their ages calculated by the database: |
| 203 |
|
| 204 |
.. code-block:: pycon |
| 205 |
|
| 206 |
>>> people = Person.objects.raw("SELECT *, age(birth_date) AS age FROM myapp_person") |
| 207 |
>>> for p in people: |
| 208 |
... print("%s is %s." % (p.first_name, p.age)) |
| 209 |
... |
| 210 |
John is 37. |
| 211 |
Jane is 42. |
| 212 |
... |
| 213 |
|
| 214 |
You can often avoid using raw SQL to compute annotations by instead using a |
| 215 |
:ref:`Func() expression <func-expressions>`. |
| 216 |
|
| 217 |
__ https://www.postgresql.org/docs/current/functions-datetime.html |
| 218 |
|
| 219 |
Passing parameters into ``raw()`` |
| 220 |
--------------------------------- |
| 221 |
|
| 222 |
If you need to perform parameterized queries, you can use the ``params`` |
| 223 |
argument to ``raw()``: |
| 224 |
|
| 225 |
.. code-block:: pycon |
| 226 |
|
| 227 |
>>> lname = "Doe" |
| 228 |
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname]) |
| 229 |
|
| 230 |
``params`` is a list or dictionary of parameters. You'll use ``%s`` |
| 231 |
placeholders in the query string for a list, or ``%(key)s`` |
| 232 |
placeholders for a dictionary (where ``key`` is replaced by a |
| 233 |
dictionary key), regardless of your database engine. Such placeholders will be |
| 234 |
replaced with parameters from the ``params`` argument. |
| 235 |
|
| 236 |
.. note:: |
| 237 |
|
| 238 |
Dictionary params are not supported with the SQLite backend; with |
| 239 |
this backend, you must pass parameters as a list. |
| 240 |
|
| 241 |
.. warning:: |
| 242 |
|
| 243 |
**Do not use string formatting on raw queries or quote placeholders in your |
| 244 |
SQL strings!** |
| 245 |
|
| 246 |
It's tempting to write the above query as: |
| 247 |
|
| 248 |
.. code-block:: pycon |
| 249 |
|
| 250 |
>>> query = "SELECT * FROM myapp_person WHERE last_name = %s" % lname |
| 251 |
>>> Person.objects.raw(query) |
| 252 |
|
| 253 |
You might also think you should write your query like this (with quotes |
| 254 |
around ``%s``): |
| 255 |
|
| 256 |
.. code-block:: pycon |
| 257 |
|
| 258 |
>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'" |
| 259 |
|
| 260 |
**Don't make either of these mistakes.** |
| 261 |
|
| 262 |
As discussed in :ref:`sql-injection-protection`, using the ``params`` |
| 263 |
argument and leaving the placeholders unquoted protects you from `SQL |
| 264 |
injection attacks`__, a common exploit where attackers inject arbitrary |
| 265 |
SQL into your database. If you use string interpolation or quote the |
| 266 |
placeholder, you're at risk for SQL injection. |
| 267 |
|
| 268 |
__ https://en.wikipedia.org/wiki/SQL_injection |
| 269 |
|
| 270 |
.. _executing-custom-sql: |
| 271 |
|
| 272 |
Executing custom SQL directly |
| 273 |
============================= |
| 274 |
|
| 275 |
Sometimes even :meth:`Manager.raw` isn't quite enough: you might need to |
| 276 |
perform queries that don't map cleanly to models, or directly execute |
| 277 |
``UPDATE``, ``INSERT``, or ``DELETE`` queries. |
| 278 |
|
| 279 |
In these cases, you can always access the database directly, routing around |
| 280 |
the model layer entirely. |
| 281 |
|
| 282 |
The object ``django.db.connection`` represents the default database |
| 283 |
connection. To use the database connection, call ``connection.cursor()`` to |
| 284 |
get a cursor object. Then, call ``cursor.execute(sql, [params])`` to execute |
| 285 |
the SQL and ``cursor.fetchone()`` or ``cursor.fetchall()`` to return the |
| 286 |
resulting rows. |
| 287 |
|
| 288 |
For example:: |
| 289 |
|
| 290 |
from django.db import connection |
| 291 |
|
| 292 |
|
| 293 |
def my_custom_sql(self): |
| 294 |
with connection.cursor() as cursor: |
| 295 |
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz]) |
| 296 |
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz]) |
| 297 |
row = cursor.fetchone() |
| 298 |
|
| 299 |
return row |
| 300 |
|
| 301 |
To protect against SQL injection, you must not include quotes around the ``%s`` |
| 302 |
placeholders in the SQL string. |
| 303 |
|
| 304 |
Note that if you want to include literal percent signs in the query, you have to |
| 305 |
double them in the case you are passing parameters:: |
| 306 |
|
| 307 |
cursor.execute("SELECT foo FROM bar WHERE baz = '30%'") |
| 308 |
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id]) |
| 309 |
|
| 310 |
If you are using :doc:`more than one database </topics/db/multi-db>`, you can |
| 311 |
use ``django.db.connections`` to obtain the connection (and cursor) for a |
| 312 |
specific database. ``django.db.connections`` is a dictionary-like |
| 313 |
object that allows you to retrieve a specific connection using its |
| 314 |
alias:: |
| 315 |
|
| 316 |
from django.db import connections |
| 317 |
|
| 318 |
with connections["my_db_alias"].cursor() as cursor: |
| 319 |
# Your code here |
| 320 |
... |
| 321 |
|
| 322 |
By default, the Python DB API will return results without their field names, |
| 323 |
which means you end up with a ``list`` of values, rather than a ``dict``. At a |
| 324 |
small performance and memory cost, you can return results as a ``dict`` by |
| 325 |
using something like this:: |
| 326 |
|
| 327 |
def dictfetchall(cursor): |
| 328 |
""" |
| 329 |
Return all rows from a cursor as a dict. |
| 330 |
Assume the column names are unique. |
| 331 |
""" |
| 332 |
columns = [col[0] for col in cursor.description] |
| 333 |
return [dict(zip(columns, row)) for row in cursor.fetchall()] |
| 334 |
|
| 335 |
Another option is to use :func:`collections.namedtuple` from the Python |
| 336 |
standard library. A ``namedtuple`` is a tuple-like object that has fields |
| 337 |
accessible by attribute lookup; it's also indexable and iterable. Results are |
| 338 |
immutable and accessible by field names or indices, which might be useful:: |
| 339 |
|
| 340 |
from collections import namedtuple |
| 341 |
|
| 342 |
|
| 343 |
def namedtuplefetchall(cursor): |
| 344 |
""" |
| 345 |
Return all rows from a cursor as a namedtuple. |
| 346 |
Assume the column names are unique. |
| 347 |
""" |
| 348 |
desc = cursor.description |
| 349 |
nt_result = namedtuple("Result", [col[0] for col in desc]) |
| 350 |
return [nt_result(*row) for row in cursor.fetchall()] |
| 351 |
|
| 352 |
The ``dictfetchall()`` and ``namedtuplefetchall()`` examples assume unique |
| 353 |
column names, since a cursor cannot distinguish columns from different tables. |
| 354 |
|
| 355 |
Here is an example of the difference between the three: |
| 356 |
|
| 357 |
.. code-block:: pycon |
| 358 |
|
| 359 |
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2") |
| 360 |
>>> cursor.fetchall() |
| 361 |
((54360982, None), (54360880, None)) |
| 362 |
|
| 363 |
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2") |
| 364 |
>>> dictfetchall(cursor) |
| 365 |
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}] |
| 366 |
|
| 367 |
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2") |
| 368 |
>>> results = namedtuplefetchall(cursor) |
| 369 |
>>> results |
| 370 |
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)] |
| 371 |
>>> results[0].id |
| 372 |
54360982 |
| 373 |
>>> results[0][0] |
| 374 |
54360982 |
| 375 |
|
| 376 |
Connections and cursors |
| 377 |
----------------------- |
| 378 |
|
| 379 |
``connection`` and ``cursor`` mostly implement the standard Python DB-API |
| 380 |
described in :pep:`249` — except when it comes to :doc:`transaction handling |
| 381 |
</topics/db/transactions>`. |
| 382 |
|
| 383 |
If you're not familiar with the Python DB-API, note that the SQL statement in |
| 384 |
``cursor.execute()`` uses placeholders, ``"%s"``, rather than adding |
| 385 |
parameters directly within the SQL. If you use this technique, the underlying |
| 386 |
database library will automatically escape your parameters as necessary. |
| 387 |
|
| 388 |
Also note that Django expects the ``"%s"`` placeholder, *not* the ``"?"`` |
| 389 |
placeholder, which is used by the SQLite Python bindings. This is for the sake |
| 390 |
of consistency and sanity. |
| 391 |
|
| 392 |
Using a cursor as a context manager:: |
| 393 |
|
| 394 |
with connection.cursor() as c: |
| 395 |
c.execute(...) |
| 396 |
|
| 397 |
is equivalent to:: |
| 398 |
|
| 399 |
c = connection.cursor() |
| 400 |
try: |
| 401 |
c.execute(...) |
| 402 |
finally: |
| 403 |
c.close() |
| 404 |
|
| 405 |
Calling stored procedures |
| 406 |
~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 407 |
|
| 408 |
.. method:: CursorWrapper.callproc(procname, params=None, kparams=None) |
| 409 |
|
| 410 |
Calls a database stored procedure with the given name. A sequence |
| 411 |
(``params``) or dictionary (``kparams``) of input parameters may be |
| 412 |
provided. Most databases don't support ``kparams``. Of Django's built-in |
| 413 |
backends, only Oracle supports it. |
| 414 |
|
| 415 |
For example, given this stored procedure in an Oracle database: |
| 416 |
|
| 417 |
.. code-block:: sql |
| 418 |
|
| 419 |
CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS |
| 420 |
p_i INTEGER; |
| 421 |
p_text NVARCHAR2(10); |
| 422 |
BEGIN |
| 423 |
p_i := v_i; |
| 424 |
p_text := v_text; |
| 425 |
... |
| 426 |
END; |
| 427 |
|
| 428 |
This will call it:: |
| 429 |
|
| 430 |
with connection.cursor() as cursor: |
| 431 |
cursor.callproc("test_procedure", [1, "test"]) |