diff -r 5ff1fc726848 -r c6bca38c1cbf parts/django/docs/topics/db/sql.txt --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/parts/django/docs/topics/db/sql.txt Sat Jan 08 11:20:57 2011 +0530 @@ -0,0 +1,279 @@ +========================== +Performing raw SQL queries +========================== + +.. currentmodule:: django.db.models + +When the :doc:`model query APIs ` don't go far enough, you +can fall back to writing raw SQL. Django gives you two ways of performing raw +SQL queries: you can use :meth:`Manager.raw()` to `perform raw queries and +return model instances`__, or you can avoid the model layer entirely and +`execute custom SQL directly`__. + +__ `performing raw queries`_ +__ `executing custom SQL directly`_ + +Performing raw queries +====================== + +.. versionadded:: 1.2 + +The ``raw()`` manager method can be used to perform raw SQL queries that +return model instances: + +.. method:: Manager.raw(raw_query, params=None, translations=None) + +This method method takes a raw SQL query, executes it, and returns a +:class:`~django.db.models.query.RawQuerySet` instance. This +:class:`~django.db.models.query.RawQuerySet` instance can be iterated +over just like an normal QuerySet to provide object instances. + +This is best illustrated with an example. Suppose you've got the following model:: + + class Person(models.Model): + first_name = models.CharField(...) + last_name = models.CharField(...) + birth_date = models.DateField(...) + +You could then execute custom SQL like so:: + + >>> for p in Person.objects.raw('SELECT * FROM myapp_person'): + ... print p + John Smith + Jane Jones + +.. admonition:: Model table names + + Where'd the name of the ``Person`` table come from in that example? + + By default, Django figures out a database table name by joining the + model's "app label" -- the name you used in ``manage.py startapp`` -- to + the model's class name, with an underscore between them. In the example + we've assumed that the ``Person`` model lives in an app named ``myapp``, + so its table would be ``myapp_person``. + + For more details check out the documentation for the + :attr:`~Options.db_table` option, which also lets you manually set the + database table name. + +Of course, this example isn't very exciting -- it's exactly the same as +running ``Person.objects.all()``. However, ``raw()`` has a bunch of other +options that make it very powerful. + +Mapping query fields to model fields +------------------------------------ + +``raw()`` automatically maps fields in the query to fields on the model. + +The order of fields in your query doesn't matter. In other words, both +of the following queries work identically:: + + >>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person') + ... + >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person') + ... + +Matching is done by name. This means that you can use SQL's ``AS`` clauses to +map fields in the query to model fields. So if you had some other table that +had ``Person`` data in it, you could easily map it into ``Person`` instances:: + + >>> Person.objects.raw('''SELECT first AS first_name, + ... last AS last_name, + ... bd AS birth_date, + ... pk as id, + ... FROM some_other_table''') + +As long as the names match, the model instances will be created correctly. + +Alternatively, you can map fields in the query to model fields using the +``translations`` argument to ``raw()``. This is a dictionary mapping names of +fields in the query to names of fields on the model. For example, the above +query could also be written:: + + >>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'} + >>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map) + +Index lookups +------------- + +``raw()`` supports indexing, so if you need only the first result you can +write:: + + >>> first_person = Person.objects.raw('SELECT * from myapp_person')[0] + +However, the indexing and slicing are not performed at the database level. If +you have a big amount of ``Person`` objects in your database, it is more +efficient to limit the query at the SQL level:: + + >>> first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0] + +Deferring model fields +---------------------- + +Fields may also be left out:: + + >>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person') + +The ``Person`` objects returned by this query will be deferred model instances +(see :meth:`~django.db.models.QuerySet.defer()`). This means that the fields +that are omitted from the query will be loaded on demand. For example:: + + >>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'): + ... print p.first_name, # This will be retrieved by the original query + ... print p.last_name # This will be retrieved on demand + ... + John Smith + Jane Jones + +From outward appearances, this looks like the query has retrieved both +the first name and last name. However, this example actually issued 3 +queries. Only the first names were retrieved by the raw() query -- the +last names were both retrieved on demand when they were printed. + +There is only one field that you can't leave out - the primary key +field. Django uses the primary key to identify model instances, so it +must always be included in a raw query. An ``InvalidQuery`` exception +will be raised if you forget to include the primary key. + +Adding annotations +------------------ + +You can also execute queries containing fields that aren't defined on the +model. For example, we could use `PostgreSQL's age() function`__ to get a list +of people with their ages calculated by the database:: + + >>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person') + >>> for p in people: + ... print "%s is %s." % (p.first_name, p.age) + John is 37. + Jane is 42. + ... + +__ http://www.postgresql.org/docs/8.4/static/functions-datetime.html + +Passing parameters into ``raw()`` +--------------------------------- + +If you need to perform parameterized queries, you can use the ``params`` +argument to ``raw()``:: + + >>> lname = 'Doe' + >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname]) + +``params`` is a list of parameters. You'll use ``%s`` placeholders in the +query string (regardless of your database engine); they'll be replaced with +parameters from the ``params`` list. + +.. warning:: + + **Do not use string formatting on raw queries!** + + It's tempting to write the above query as:: + + >>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname + >>> Person.objects.raw(query) + + **Don't.** + + Using the ``params`` list completely protects you from `SQL injection + attacks`__, a common exploit where attackers inject arbitrary SQL into + your database. If you use string interpolation, sooner or later you'll + fall victim to SQL injection. As long as you remember to always use the + ``params`` list you'll be protected. + +__ http://en.wikipedia.org/wiki/SQL_injection + +Executing custom SQL directly +============================= + +Sometimes even :meth:`Manager.raw` isn't quite enough: you might need to +perform queries that don't map cleanly to models, or directly execute +``UPDATE``, ``INSERT``, or ``DELETE`` queries. + +In these cases, you can always access the database directly, routing around +the model layer entirely. + +The object ``django.db.connection`` represents the +default database connection, and ``django.db.transaction`` represents the +default database transaction. To use the database connection, call +``connection.cursor()`` to get a cursor object. Then, call +``cursor.execute(sql, [params])`` to execute the SQL and ``cursor.fetchone()`` +or ``cursor.fetchall()`` to return the resulting rows. After performing a data +changing operation, you should then call +``transaction.commit_unless_managed()`` to ensure your changes are committed +to the database. If your query is purely a data retrieval operation, no commit +is required. For example:: + + def my_custom_sql(): + from django.db import connection, transaction + cursor = connection.cursor() + + # Data modifying operation - commit required + cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz]) + transaction.commit_unless_managed() + + # Data retrieval operation - no commit required + cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz]) + row = cursor.fetchone() + + return row + +If you are using more than one database you can use +``django.db.connections`` to obtain the connection (and cursor) for a +specific database. ``django.db.connections`` is a dictionary-like +object that allows you to retrieve a specific connection using it's +alias:: + + from django.db import connections + cursor = connections['my_db_alias'].cursor() + +.. _transactions-and-raw-sql: + +Transactions and raw SQL +------------------------ +If you are using transaction decorators (such as ``commit_on_success``) to +wrap your views and provide transaction control, you don't have to make a +manual call to ``transaction.commit_unless_managed()`` -- you can manually +commit if you want to, but you aren't required to, since the decorator will +commit for you. However, if you don't manually commit your changes, you will +need to manually mark the transaction as dirty, using +``transaction.set_dirty()``:: + + @commit_on_success + def my_custom_sql_view(request, value): + from django.db import connection, transaction + cursor = connection.cursor() + + # Data modifying operation + cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [value]) + + # Since we modified data, mark the transaction as dirty + transaction.set_dirty() + + # Data retrieval operation. This doesn't dirty the transaction, + # so no call to set_dirty() is required. + cursor.execute("SELECT foo FROM bar WHERE baz = %s", [value]) + row = cursor.fetchone() + + return render_to_response('template.html', {'row': row}) + +The call to ``set_dirty()`` is made automatically when you use the Django ORM +to make data modifying database calls. However, when you use raw SQL, Django +has no way of knowing if your SQL modifies data or not. The manual call to +``set_dirty()`` ensures that Django knows that there are modifications that +must be committed. + +Connections and cursors +----------------------- + +``connection`` and ``cursor`` mostly implement the standard `Python DB-API`_ +(except when it comes to :doc:`transaction handling `). +If you're not familiar with the Python DB-API, note that the SQL statement in +``cursor.execute()`` uses placeholders, ``"%s"``, rather than adding parameters +directly within the SQL. If you use this technique, the underlying database +library will automatically add quotes and escaping to your parameter(s) as +necessary. (Also note that Django expects the ``"%s"`` placeholder, *not* the +``"?"`` placeholder, which is used by the SQLite Python bindings. This is for +the sake of consistency and sanity.) + +.. _Python DB-API: http://www.python.org/dev/peps/pep-0249/