Debian Codegraph @

docs/topics/db/sql.txt

raw
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"])