PageRenderTime 16ms CodeModel.GetById 2ms app.highlight 5ms RepoModel.GetById 2ms app.codeStats 0ms

/docs/topics/db/sql.txt

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