Feel free to write custom SQL statements in custom model methods and module-level methods. The object django.db.connection represents the current database connection, and django.db.transaction represents the current 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(self):
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 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.
connection and cursor mostly implement the standard Python DB-API (except when it comes to 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.)
A final note: If all you want to do is a custom WHERE clause, you can just use the where, tables and params arguments to the extra clause in the standard queryset API.
Sep 20, 2009