TEXT 28
Sql.txt Guest on 29th April 2021 06:43:22 AM
  1. ==========================
  2. Performing raw SQL queries
  3. ==========================
  4.  
  5. .. currentmodule:: django.db.models
  6.  
  7. When the :doc:`model query APIs </topics/db/queries>` don't go far enough, you
  8. can fall back to writing raw SQL. Django gives you two ways of performing raw
  9. SQL queries: you can use :meth:`Manager.raw()` to `perform raw queries and
  10. return model instances`__, or you can avoid the model layer entirely and
  11. `execute custom SQL directly`__.
  12.  
  13. __ `performing raw queries`_
  14. __ `executing custom SQL directly`_
  15.  
  16. .. warning::
  17.  
  18.     You should be very careful whenever you write raw SQL. Every time you use
  19.     it, you should properly escape any parameters that the user can control
  20.     by using ``params`` in order to protect against SQL injection attacks.
  21.     Please read more about :ref:`SQL injection protection
  22.     <sql-injection-protection>`.
  23.  
  24. .. _executing-raw-queries:
  25.  
  26. Performing raw queries
  27. ======================
  28.  
  29. The ``raw()`` manager method can be used to perform raw SQL queries that
  30. return model instances:
  31.  
  32. .. method:: Manager.raw(raw_query, params=None, translations=None)
  33.  
  34. This method takes a raw SQL query, executes it, and returns a
  35. ``django.db.models.query.RawQuerySet`` instance. This ``RawQuerySet`` instance
  36. can be iterated over just like a normal
  37. :class:`~django.db.models.query.QuerySet` to provide object instances.
  38.  
  39. This is best illustrated with an example. Suppose you have the following model::
  40.  
  41.     class Person(models.Model):
  42.         first_name = models.CharField(...)
  43.         last_name = models.CharField(...)
  44.         birth_date = models.DateField(...)
  45.  
  46. You could then execute custom SQL like so::
  47.  
  48.     >>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
  49.     ...     print(p)
  50.     John Smith
  51.     Jane Jones
  52.  
  53. Of course, this example isn't very exciting -- it's exactly the same as
  54. running ``Person.objects.all()``. However, ``raw()`` has a bunch of other
  55. options that make it very powerful.
  56.  
  57. .. admonition:: Model table names
  58.  
  59.     Where did the name of the ``Person`` table come from in that example?
  60.  
  61.     By default, Django figures out a database table name by joining the
  62.     model's "app label" -- the name you used in ``manage.py startapp`` -- to
  63.     the model's class name, with an underscore between them. In the example
  64.     we've assumed that the ``Person`` model lives in an app named ``myapp``,
  65.     so its table would be ``myapp_person``.
  66.  
  67.     For more details check out the documentation for the
  68.     :attr:`~Options.db_table` option, which also lets you manually set the
  69.     database table name.
  70.  
  71. .. warning::
  72.  
  73.     No checking is done on the SQL statement that is passed in to ``.raw()``.
  74.     Django expects that the statement will return a set of rows from the
  75.     database, but does nothing to enforce that. If the query does not
  76.     return rows, a (possibly cryptic) error will result.
  77.  
  78. .. warning::
  79.  
  80.     If you are performing queries on MySQL, note that MySQL's silent type coercion
  81.     may cause unexpected results when mixing types. If you query on a string
  82.     type column, but with an integer value, MySQL will coerce the types of all values
  83.     in the table to an integer before performing the comparison. For example, if your
  84.     table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``,
  85.     both rows will match. To prevent this, perform the correct typecasting
  86.     before using the value in a query.
  87.  
  88. .. warning::
  89.  
  90.     While a ``RawQuerySet`` instance can be iterated over like a normal
  91.     :class:`~django.db.models.query.QuerySet`, ``RawQuerySet`` doesn't
  92.     implement all methods you can use with ``QuerySet``. For example,
  93.     ``__bool__()`` and ``__len__()`` are not defined in ``RawQuerySet``, and
  94.     thus all ``RawQuerySet`` instances are considered ``True``. The reason
  95.     these methods are not implemented in ``RawQuerySet`` is that implementing
  96.     them without internal caching would be a performance drawback and adding
  97.     such caching would be backward incompatible.
  98.  
  99. Mapping query fields to model fields
  100. ------------------------------------
  101.  
  102. ``raw()`` automatically maps fields in the query to fields on the model.
  103.  
  104. The order of fields in your query doesn't matter. In other words, both
  105. of the following queries work identically::
  106.  
  107.     >>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
  108.     ...
  109.     >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
  110.     ...
  111.  
  112. Matching is done by name. This means that you can use SQL's ``AS`` clauses to
  113. map fields in the query to model fields. So if you had some other table that
  114. had ``Person`` data in it, you could easily map it into ``Person`` instances::
  115.  
  116.     >>> Person.objects.raw('''SELECT first AS first_name,
  117.     ...                              last AS last_name,
  118.     ...                              bd AS birth_date,
  119.     ...                              pk AS id,
  120.     ...                       FROM some_other_table''')
  121.  
  122. As long as the names match, the model instances will be created correctly.
  123.  
  124. Alternatively, you can map fields in the query to model fields using the
  125. ``translations`` argument to ``raw()``. This is a dictionary mapping names of
  126. fields in the query to names of fields on the model. For example, the above
  127. query could also be written::
  128.  
  129.     >>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
  130.     >>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
  131.  
  132. Index lookups
  133. -------------
  134.  
  135. ``raw()`` supports indexing, so if you need only the first result you can
  136. write::
  137.  
  138.     >>> first_person = Person.objects.raw('SELECT * FROM myapp_person')[0]
  139.  
  140. However, the indexing and slicing are not performed at the database level. If
  141. you have a large number of ``Person`` objects in your database, it is more
  142. efficient to limit the query at the SQL level::
  143.  
  144.     >>> first_person = Person.objects.raw('SELECT * FROM myapp_person LIMIT 1')[0]
  145.  
  146. Deferring model fields
  147. ----------------------
  148.  
  149. Fields may also be left out::
  150.  
  151.     >>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person')
  152.  
  153. The ``Person`` objects returned by this query will be deferred model instances
  154. (see :meth:`~django.db.models.query.QuerySet.defer()`). This means that the
  155. fields that are omitted from the query will be loaded on demand. For example::
  156.  
  157.     >>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
  158.     ...     print(p.first_name, # This will be retrieved by the original query
  159.     ...           p.last_name) # This will be retrieved on demand
  160.     ...
  161.     John Smith
  162.     Jane Jones
  163.  
  164. From outward appearances, this looks like the query has retrieved both
  165. the first name and last name. However, this example actually issued 3
  166. queries. Only the first names were retrieved by the raw() query -- the
  167. last names were both retrieved on demand when they were printed.
  168.  
  169. There is only one field that you can't leave out - the primary key
  170. field. Django uses the primary key to identify model instances, so it
  171. must always be included in a raw query. An ``InvalidQuery`` exception
  172. will be raised if you forget to include the primary key.
  173.  
  174. Adding annotations
  175. ------------------
  176.  
  177. You can also execute queries containing fields that aren't defined on the
  178. model. For example, we could use `PostgreSQL's age() function`__ to get a list
  179. of people with their ages calculated by the database::
  180.  
  181.     >>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')
  182.     >>> for p in people:
  183.     ...     print("%s is %s." % (p.first_name, p.age))
  184.     John is 37.
  185.     Jane is 42.
  186.     ...
  187.  
  188. __ http://www.postgresql.org/docs/current/static/functions-datetime.html
  189.  
  190. Passing parameters into ``raw()``
  191. ---------------------------------
  192.  
  193. If you need to perform parameterized queries, you can use the ``params``
  194. argument to ``raw()``::
  195.  
  196.     >>> lname = 'Doe'
  197.     >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
  198.  
  199. ``params`` is a list or dictionary of parameters. You'll use ``%s``
  200. placeholders in the query string for a list, or ``%(key)s``
  201. placeholders for a dictionary (where ``key`` is replaced by a
  202. dictionary key, of course), regardless of your database engine.  Such
  203. placeholders will be replaced with parameters from the ``params``
  204. argument.
  205.  
  206. .. note::
  207.  
  208.    Dictionary params are not supported with the SQLite backend; with
  209.    this backend, you must pass parameters as a list.
  210.  
  211. .. warning::
  212.  
  213.     **Do not use string formatting on raw queries!**
  214.  
  215.     It's tempting to write the above query as::
  216.  
  217.         >>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
  218.         >>> Person.objects.raw(query)
  219.  
  220.     **Don't.**
  221.  
  222.     Using the ``params`` argument completely protects you from `SQL injection
  223.     attacks`__, a common exploit where attackers inject arbitrary SQL into
  224.     your database. If you use string interpolation, sooner or later you'll
  225.     fall victim to SQL injection. As long as you remember to always use the
  226.     ``params`` argument you'll be protected.
  227.  
  228. __ http://en.wikipedia.org/wiki/SQL_injection
  229.  
  230. .. _executing-custom-sql:
  231.  
  232. Executing custom SQL directly
  233. =============================
  234.  
  235. Sometimes even :meth:`Manager.raw` isn't quite enough: you might need to
  236. perform queries that don't map cleanly to models, or directly execute
  237. ``UPDATE``, ``INSERT``, or ``DELETE`` queries.
  238.  
  239. In these cases, you can always access the database directly, routing around
  240. the model layer entirely.
  241.  
  242. The object ``django.db.connection`` represents the default database
  243. connection. To use the database connection, call ``connection.cursor()`` to
  244. get a cursor object. Then, call ``cursor.execute(sql, [params])`` to execute
  245. the SQL and ``cursor.fetchone()`` or ``cursor.fetchall()`` to return the
  246. resulting rows.
  247.  
  248. For example::
  249.  
  250.     from django.db import connection
  251.  
  252.     def my_custom_sql(self):
  253.         cursor = connection.cursor()
  254.  
  255.         cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
  256.  
  257.         cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
  258.         row = cursor.fetchone()
  259.  
  260.         return row
  261.  
  262. Note that if you want to include literal percent signs in the query, you have to
  263. double them in the case you are passing parameters::
  264.  
  265.      cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
  266.      cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])
  267.  
  268. If you are using :doc:`more than one database </topics/db/multi-db>`, you can
  269. use ``django.db.connections`` to obtain the connection (and cursor) for a
  270. specific database. ``django.db.connections`` is a dictionary-like
  271. object that allows you to retrieve a specific connection using its
  272. alias::
  273.  
  274.     from django.db import connections
  275.     cursor = connections['my_db_alias'].cursor()
  276.     # Your code here...
  277.  
  278. By default, the Python DB API will return results without their field
  279. names, which means you end up with a ``list`` of values, rather than a
  280. ``dict``. At a small performance cost, you can return results as a
  281. ``dict`` by using something like this::
  282.  
  283.     def dictfetchall(cursor):
  284.         "Returns all rows from a cursor as a dict"
  285.         desc = cursor.description
  286.         return [
  287.             dict(zip([col[0] for col in desc], row))
  288.             for row in cursor.fetchall()
  289.         ]
  290.  
  291. Here is an example of the difference between the two::
  292.  
  293.     >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
  294.     >>> cursor.fetchall()
  295.     ((54360982L, None), (54360880L, None))
  296.  
  297.     >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
  298.     >>> dictfetchall(cursor)
  299.     [{'parent_id': None, 'id': 54360982L}, {'parent_id': None, 'id': 54360880L}]
  300.  
  301. Connections and cursors
  302. -----------------------
  303.  
  304. ``connection`` and ``cursor`` mostly implement the standard Python DB-API
  305. described in :pep:`249` — except when it comes to :doc:`transaction handling
  306. </topics/db/transactions>`.
  307.  
  308. If you're not familiar with the Python DB-API, note that the SQL statement in
  309. ``cursor.execute()`` uses placeholders, ``"%s"``, rather than adding
  310. parameters directly within the SQL. If you use this technique, the underlying
  311. database library will automatically escape your parameters as necessary.
  312.  
  313. Also note that Django expects the ``"%s"`` placeholder, *not* the ``"?"``
  314. placeholder, which is used by the SQLite Python bindings. This is for the sake
  315. of consistency and sanity.
  316.  
  317. .. versionchanged:: 1.7
  318.  
  319. :pep:`249` does not state whether a cursor should be usable as a context
  320. manager. Prior to Python 2.7, a cursor was usable as a context manager due
  321. an unexpected behavior in magic method lookups (`Python ticket #9220`_).
  322. Django 1.7 explicitly added support to allow using a cursor as context
  323. manager.
  324.  
  325. .. _`Python ticket #9220`: https://bugs.python.org/issue9220
  326.  
  327. Using a cursor as a context manager::
  328.  
  329.     with connection.cursor() as c:
  330.         c.execute(...)
  331.  
  332. is equivalent to::
  333.  
  334.     c = connection.cursor()
  335.     try:
  336.         c.execute(...)
  337.     finally:
  338.         c.cl

Paste-bin is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.