PageRenderTime 43ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 0ms

Plain Text | 268 lines | 195 code | 73 blank | 0 comment | 0 complexity | a7eaad319c6ec84db7bc4de404a96bce MD5 | raw file
Possible License(s): BSD-3-Clause
  1. ==========================
  2. Performing raw SQL queries
  3. ==========================
  4. .. currentmodule:: django.db.models
  5. When the :doc:`model query APIs </topics/db/queries>` don't go far enough, you
  6. can fall back to writing raw SQL. Django gives you two ways of performing raw
  7. SQL queries: you can use :meth:`Manager.raw()` to `perform raw queries and
  8. return model instances`__, or you can avoid the model layer entirely and
  9. `execute custom SQL directly`__.
  10. __ `performing raw queries`_
  11. __ `executing custom SQL directly`_
  12. Performing raw queries
  13. ======================
  14. .. versionadded:: 1.2
  15. The ``raw()`` manager method can be used to perform raw SQL queries that
  16. return model instances:
  17. .. method:: Manager.raw(raw_query, params=None, translations=None)
  18. This method method takes a raw SQL query, executes it, and returns a
  19. :class:`~django.db.models.query.RawQuerySet` instance. This
  20. :class:`~django.db.models.query.RawQuerySet` instance can be iterated
  21. over just like an normal QuerySet to provide object instances.
  22. This is best illustrated with an example. Suppose you've got the following model::
  23. class Person(models.Model):
  24. first_name = models.CharField(...)
  25. last_name = models.CharField(...)
  26. birth_date = models.DateField(...)
  27. You could then execute custom SQL like so::
  28. >>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
  29. ... print p
  30. John Smith
  31. Jane Jones
  32. Of course, this example isn't very exciting -- it's exactly the same as
  33. running ``Person.objects.all()``. However, ``raw()`` has a bunch of other
  34. options that make it very powerful.
  35. .. admonition:: Model table names
  36. Where'd the name of the ``Person`` table come from in that example?
  37. By default, Django figures out a database table name by joining the
  38. model's "app label" -- the name you used in `` startapp`` -- to
  39. the model's class name, with an underscore between them. In the example
  40. we've assumed that the ``Person`` model lives in an app named ``myapp``,
  41. so its table would be ``myapp_person``.
  42. For more details check out the documentation for the
  43. :attr:`~Options.db_table` option, which also lets you manually set the
  44. database table name.
  45. .. warning::
  46. No checking is done on the SQL statement that is passed in to ``.raw()``.
  47. Django expects that the statement will return a set of rows from the
  48. database, but does nothing to enforce that. If the query does not
  49. return rows, a (possibly cryptic) error will result.
  50. Mapping query fields to model fields
  51. ------------------------------------
  52. ``raw()`` automatically maps fields in the query to fields on the model.
  53. The order of fields in your query doesn't matter. In other words, both
  54. of the following queries work identically::
  55. >>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
  56. ...
  57. >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
  58. ...
  59. Matching is done by name. This means that you can use SQL's ``AS`` clauses to
  60. map fields in the query to model fields. So if you had some other table that
  61. had ``Person`` data in it, you could easily map it into ``Person`` instances::
  62. >>> Person.objects.raw('''SELECT first AS first_name,
  63. ... last AS last_name,
  64. ... bd AS birth_date,
  65. ... pk as id,
  66. ... FROM some_other_table''')
  67. As long as the names match, the model instances will be created correctly.
  68. Alternatively, you can map fields in the query to model fields using the
  69. ``translations`` argument to ``raw()``. This is a dictionary mapping names of
  70. fields in the query to names of fields on the model. For example, the above
  71. query could also be written::
  72. >>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
  73. >>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
  74. Index lookups
  75. -------------
  76. ``raw()`` supports indexing, so if you need only the first result you can
  77. write::
  78. >>> first_person = Person.objects.raw('SELECT * from myapp_person')[0]
  79. However, the indexing and slicing are not performed at the database level. If
  80. you have a big amount of ``Person`` objects in your database, it is more
  81. efficient to limit the query at the SQL level::
  82. >>> first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0]
  83. Deferring model fields
  84. ----------------------
  85. Fields may also be left out::
  86. >>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person')
  87. The ``Person`` objects returned by this query will be deferred model instances
  88. (see :meth:`~django.db.models.QuerySet.defer()`). This means that the fields
  89. that are omitted from the query will be loaded on demand. For example::
  90. >>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
  91. ... print p.first_name, # This will be retrieved by the original query
  92. ... print p.last_name # This will be retrieved on demand
  93. ...
  94. John Smith
  95. Jane Jones
  96. From outward appearances, this looks like the query has retrieved both
  97. the first name and last name. However, this example actually issued 3
  98. queries. Only the first names were retrieved by the raw() query -- the
  99. last names were both retrieved on demand when they were printed.
  100. There is only one field that you can't leave out - the primary key
  101. field. Django uses the primary key to identify model instances, so it
  102. must always be included in a raw query. An ``InvalidQuery`` exception
  103. will be raised if you forget to include the primary key.
  104. Adding annotations
  105. ------------------
  106. You can also execute queries containing fields that aren't defined on the
  107. model. For example, we could use `PostgreSQL's age() function`__ to get a list
  108. of people with their ages calculated by the database::
  109. >>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')
  110. >>> for p in people:
  111. ... print "%s is %s." % (p.first_name, p.age)
  112. John is 37.
  113. Jane is 42.
  114. ...
  115. __
  116. Passing parameters into ``raw()``
  117. ---------------------------------
  118. If you need to perform parameterized queries, you can use the ``params``
  119. argument to ``raw()``::
  120. >>> lname = 'Doe'
  121. >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
  122. ``params`` is a list of parameters. You'll use ``%s`` placeholders in the
  123. query string (regardless of your database engine); they'll be replaced with
  124. parameters from the ``params`` list.
  125. .. warning::
  126. **Do not use string formatting on raw queries!**
  127. It's tempting to write the above query as::
  128. >>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
  129. >>> Person.objects.raw(query)
  130. **Don't.**
  131. Using the ``params`` list completely protects you from `SQL injection
  132. attacks`__, a common exploit where attackers inject arbitrary SQL into
  133. your database. If you use string interpolation, sooner or later you'll
  134. fall victim to SQL injection. As long as you remember to always use the
  135. ``params`` list you'll be protected.
  136. __
  137. Executing custom SQL directly
  138. =============================
  139. Sometimes even :meth:`Manager.raw` isn't quite enough: you might need to
  140. perform queries that don't map cleanly to models, or directly execute
  141. ``UPDATE``, ``INSERT``, or ``DELETE`` queries.
  142. In these cases, you can always access the database directly, routing around
  143. the model layer entirely.
  144. The object ``django.db.connection`` represents the
  145. default database connection, and ``django.db.transaction`` represents the
  146. default database transaction. To use the database connection, call
  147. ``connection.cursor()`` to get a cursor object. Then, call
  148. ``cursor.execute(sql, [params])`` to execute the SQL and ``cursor.fetchone()``
  149. or ``cursor.fetchall()`` to return the resulting rows. After performing a data
  150. changing operation, you should then call
  151. ``transaction.commit_unless_managed()`` to ensure your changes are committed
  152. to the database. If your query is purely a data retrieval operation, no commit
  153. is required. For example::
  154. def my_custom_sql():
  155. from django.db import connection, transaction
  156. cursor = connection.cursor()
  157. # Data modifying operation - commit required
  158. cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
  159. transaction.commit_unless_managed()
  160. # Data retrieval operation - no commit required
  161. cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
  162. row = cursor.fetchone()
  163. return row
  164. If you are using more than one database you can use
  165. ``django.db.connections`` to obtain the connection (and cursor) for a
  166. specific database. ``django.db.connections`` is a dictionary-like
  167. object that allows you to retrieve a specific connection using its
  168. alias::
  169. from django.db import connections
  170. cursor = connections['my_db_alias'].cursor()
  171. # Your code here...
  172. transaction.commit_unless_managed(using='my_db_alias')
  173. .. _transactions-and-raw-sql:
  174. Transactions and raw SQL
  175. ------------------------
  176. When you make a raw SQL call, Django will automatically mark the
  177. current transaction as dirty. You must then ensure that the
  178. transaction containing those calls is closed correctly. See :ref:`the
  179. notes on the requirements of Django's transaction handling
  180. <topics-db-transactions-requirements>` for more details.
  181. .. versionchanged:: 1.3
  182. Prior to Django 1.3, it was necessary to manually mark a transaction
  183. as dirty using ``transaction.set_dirty()`` when using raw SQL calls.
  184. Connections and cursors
  185. -----------------------
  186. ``connection`` and ``cursor`` mostly implement the standard `Python DB-API`_
  187. (except when it comes to :doc:`transaction handling </topics/db/transactions>`).
  188. If you're not familiar with the Python DB-API, note that the SQL statement in
  189. ``cursor.execute()`` uses placeholders, ``"%s"``, rather than adding parameters
  190. directly within the SQL. If you use this technique, the underlying database
  191. library will automatically add quotes and escaping to your parameter(s) as
  192. necessary. (Also note that Django expects the ``"%s"`` placeholder, *not* the
  193. ``"?"`` placeholder, which is used by the SQLite Python bindings. This is for
  194. the sake of consistency and sanity.)
  195. .. _Python DB-API: