Tools to ease executing raw SQL with Django ORM

Question

I often need to execute custom sql queries in django, and manually converting query results into objects every time is kinda painful. I wonder how fellow Slackers deal with this. Maybe someone had written some kind of a library to help dealing with custom SQL in Django?

Answer 1

Not exactly sure what you're looking for, but you can always add a method onto a model to execute custom SQL per the docs:

def my_custom_sql(self):
  from django.db import connection
  cursor = connection.cursor()
  cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
  row = cursor.fetchone()
  return row

For something more generic, create an abstract base model that defines a function like that with an "sql" parameter.

Answer 2

Since the issue is "manually converting query results into objects," the simplest solution is often to see if your custom SQL can fit into an ORM .extra() call rather than being a pure-SQL query. Often it can, and then you let the ORM do all the work of building up objects as usual.

Answer 3

The newest development version (future 1.2) has .raw() method to help you with that:

Person.objects.raw('SELECT * FROM myapp_person')

More information can be found under http://docs.djangoproject.com/en/dev/topics/db/sql/.