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 Performing raw queries

    17 ======================

    18 

    19 .. versionadded:: 1.2

    20 

    21 The ``raw()`` manager method can be used to perform raw SQL queries that

    22 return model instances:

    23 

    24 .. method:: Manager.raw(raw_query, params=None, translations=None)

    25 

    26 This 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

    29 over just like an normal QuerySet to provide object instances.

    30 

    31 This 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 

    38 You 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 

    45 .. admonition:: Model table names

    46 

    47     Where'd the name of the ``Person`` table come from in that example?

    48 

    49     By default, Django figures out a database table name by joining the

    50     model's "app label" -- the name you used in ``manage.py startapp`` -- to

    51     the model's class name, with an underscore between them. In the example

    52     we've assumed that the ``Person`` model lives in an app named ``myapp``,

    53     so its table would be ``myapp_person``.

    54 

    55     For more details check out the documentation for the

    56     :attr:`~Options.db_table` option, which also lets you manually set the

    57     database table name.

    58 

    59 Of course, this example isn't very exciting -- it's exactly the same as

    60 running ``Person.objects.all()``. However, ``raw()`` has a bunch of other

    61 options that make it very powerful.

    62 

    63 Mapping query fields to model fields

    64 ------------------------------------

    65 

    66 ``raw()`` automatically maps fields in the query to fields on the model.

    67 

    68 The order of fields in your query doesn't matter. In other words, both

    69 of the following queries work identically::

    70 

    71     >>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')

    72     ...

    73     >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')

    74     ...

    75 

    76 Matching is done by name. This means that you can use SQL's ``AS`` clauses to

    77 map fields in the query to model fields. So if you had some other table that

    78 had ``Person`` data in it, you could easily map it into ``Person`` instances::

    79 

    80     >>> Person.objects.raw('''SELECT first AS first_name,

    81     ...                              last AS last_name,

    82     ...                              bd AS birth_date,

    83     ...                              pk as id,

    84     ...                       FROM some_other_table''')

    85 

    86 As long as the names match, the model instances will be created correctly.

    87 

    88 Alternatively, you can map fields in the query to model fields using the

    89 ``translations`` argument to ``raw()``. This is a dictionary mapping names of

    90 fields in the query to names of fields on the model. For example, the above

    91 query could also be written::

    92 

    93     >>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}

    94     >>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)

    95 

    96 Index lookups

    97 -------------

    98 

    99 ``raw()`` supports indexing, so if you need only the first result you can

   100 write::

   101 

   102     >>> first_person = Person.objects.raw('SELECT * from myapp_person')[0]

   103 

   104 However, the indexing and slicing are not performed at the database level. If

   105 you have a big amount of ``Person`` objects in your database, it is more

   106 efficient to limit the query at the SQL level::

   107 

   108     >>> first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0]

   109 

   110 Deferring model fields

   111 ----------------------

   112 

   113 Fields may also be left out::

   114 

   115     >>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person')

   116 

   117 The ``Person`` objects returned by this query will be deferred model instances

   118 (see :meth:`~django.db.models.QuerySet.defer()`). This means that the fields

   119 that are omitted from the query will be loaded on demand. For example::

   120 

   121     >>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):

   122     ...     print p.first_name, # This will be retrieved by the original query

   123     ...     print p.last_name # This will be retrieved on demand

   124     ...

   125     John Smith

   126     Jane Jones

   127 

   128 From outward appearances, this looks like the query has retrieved both

   129 the first name and last name. However, this example actually issued 3

   130 queries. Only the first names were retrieved by the raw() query -- the

   131 last names were both retrieved on demand when they were printed.

   132 

   133 There is only one field that you can't leave out - the primary key

   134 field. Django uses the primary key to identify model instances, so it

   135 must always be included in a raw query. An ``InvalidQuery`` exception

   136 will be raised if you forget to include the primary key.

   137 

   138 Adding annotations

   139 ------------------

   140 

   141 You can also execute queries containing fields that aren't defined on the

   142 model. For example, we could use `PostgreSQL's age() function`__ to get a list

   143 of people with their ages calculated by the database::

   144 

   145     >>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')

   146     >>> for p in people:

   147     ...     print "%s is %s." % (p.first_name, p.age)

   148     John is 37.

   149     Jane is 42.

   150     ...

   151 

   152 __ http://www.postgresql.org/docs/8.4/static/functions-datetime.html

   153 

   154 Passing parameters into ``raw()``

   155 ---------------------------------

   156 

   157 If you need to perform parameterized queries, you can use the ``params``

   158 argument to ``raw()``::

   159 

   160     >>> lname = 'Doe'

   161     >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])

   162 

   163 ``params`` is a list of parameters. You'll use ``%s`` placeholders in the

   164 query string (regardless of your database engine); they'll be replaced with

   165 parameters from the ``params`` list.

   166 

   167 .. warning::

   168 

   169     **Do not use string formatting on raw queries!**

   170 

   171     It's tempting to write the above query as::

   172 

   173         >>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname

   174         >>> Person.objects.raw(query)

   175 

   176     **Don't.**

   177 

   178     Using the ``params`` list completely protects you from `SQL injection

   179     attacks`__, a common exploit where attackers inject arbitrary SQL into

   180     your database. If you use string interpolation, sooner or later you'll

   181     fall victim to SQL injection. As long as you remember to always use the

   182     ``params`` list you'll be protected.

   183 

   184 __ http://en.wikipedia.org/wiki/SQL_injection

   185 

   186 Executing custom SQL directly

   187 =============================

   188 

   189 Sometimes even :meth:`Manager.raw` isn't quite enough: you might need to

   190 perform queries that don't map cleanly to models, or directly execute

   191 ``UPDATE``, ``INSERT``, or ``DELETE`` queries.

   192 

   193 In these cases, you can always access the database directly, routing around

   194 the model layer entirely.

   195 

   196 The object ``django.db.connection`` represents the

   197 default database connection, and ``django.db.transaction`` represents the

   198 default database transaction. To use the database connection, call

   199 ``connection.cursor()`` to get a cursor object. Then, call

   200 ``cursor.execute(sql, [params])`` to execute the SQL and ``cursor.fetchone()``

   201 or ``cursor.fetchall()`` to return the resulting rows. After performing a data

   202 changing operation, you should then call

   203 ``transaction.commit_unless_managed()`` to ensure your changes are committed

   204 to the database. If your query is purely a data retrieval operation, no commit

   205 is required. For example::

   206 

   207     def my_custom_sql():

   208         from django.db import connection, transaction

   209         cursor = connection.cursor()

   210 

   211         # Data modifying operation - commit required

   212         cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])

   213         transaction.commit_unless_managed()

   214 

   215         # Data retrieval operation - no commit required

   216         cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])

   217         row = cursor.fetchone()

   218 

   219         return row

   220 

   221 If you are using more than one database you can use

   222 ``django.db.connections`` to obtain the connection (and cursor) for a

   223 specific database. ``django.db.connections`` is a dictionary-like

   224 object that allows you to retrieve a specific connection using it's

   225 alias::

   226 

   227     from django.db import connections

   228     cursor = connections['my_db_alias'].cursor()

   229 

   230 .. _transactions-and-raw-sql:

   231 

   232 Transactions and raw SQL

   233 ------------------------

   234 If you are using transaction decorators (such as ``commit_on_success``) to

   235 wrap your views and provide transaction control, you don't have to make a

   236 manual call to ``transaction.commit_unless_managed()`` -- you can manually

   237 commit if you want to, but you aren't required to, since the decorator will

   238 commit for you. However, if you don't manually commit your changes, you will

   239 need to manually mark the transaction as dirty, using

   240 ``transaction.set_dirty()``::

   241 

   242     @commit_on_success

   243     def my_custom_sql_view(request, value):

   244         from django.db import connection, transaction

   245         cursor = connection.cursor()

   246 

   247         # Data modifying operation

   248         cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [value])

   249 

   250         # Since we modified data, mark the transaction as dirty

   251         transaction.set_dirty()

   252 

   253         # Data retrieval operation. This doesn't dirty the transaction,

   254         # so no call to set_dirty() is required.

   255         cursor.execute("SELECT foo FROM bar WHERE baz = %s", [value])

   256         row = cursor.fetchone()

   257 

   258         return render_to_response('template.html', {'row': row})

   259 

   260 The call to ``set_dirty()`` is made automatically when you use the Django ORM

   261 to make data modifying database calls. However, when you use raw SQL, Django

   262 has no way of knowing if your SQL modifies data or not. The manual call to

   263 ``set_dirty()`` ensures that Django knows that there are modifications that

   264 must be committed.

   265 

   266 Connections and cursors

   267 -----------------------

   268 

   269 ``connection`` and ``cursor`` mostly implement the standard `Python DB-API`_

   270 (except when it comes to :doc:`transaction handling </topics/db/transactions>`).

   271 If you're not familiar with the Python DB-API, note that the SQL statement in

   272 ``cursor.execute()`` uses placeholders, ``"%s"``, rather than adding parameters

   273 directly within the SQL. If you use this technique, the underlying database

   274 library will automatically add quotes and escaping to your parameter(s) as

   275 necessary. (Also note that Django expects the ``"%s"`` placeholder, *not* the

   276 ``"?"`` placeholder, which is used by the SQLite Python bindings. This is for

   277 the sake of consistency and sanity.)

   278 

   279 .. _Python DB-API: http://www.python.org/dev/peps/pep-0249/