Brendel Consulting logo

Brendel Consulting

composing beautiful software solutions

May 30, 2009

Retrieving full objects with custom SQL in Django

While working with Django, I recently had to retrieve some model objects via custom SQL. Looking for examples on how to do this, I noticed that most tutorials describe how to retrieve specific values via SQL, but not entire model objects. The solution is actually very simple. But since I couldn't find it described anywhere, I thought I share it here.

The usual approach goes something like this:
   from django.db import connection, models
class CustomManager(models.Manager):
...
query = "SELECT id FROM mymodel WHERE ..."
cursor = connection.cursor()
cursor.execute(query)
return [i[0] for i in cursor.fetchall()]
This then returns the IDs of the objects that were selected. If you want to return actual objects, you can modify the last line into this:
    return self.filter(id in [i[0] for i in cursor.fetchall()])
However, the problem with this approach is that we are executing two queries now: The custom SQL query, followed by the self.filter() query.

Here then is a very simple way to get complete objects, with just a single, custom SQL query:
   class CustomManager(models.Manager):
...
query = "SELECT * FROM mymodel WHERE ..."
cursor = connection.cursor()
cursor.execute(query)
return [MyModel(*i) for i in cursor.fetchall()]
Rather than making a list of IDs, which is then used to query for the actual objects, we make a list of the complete objects. We can do that, because we changed the custom SQL: Instead of selecting just the ID column, we are now selecting all columns (SELECT * ...).

With custom SQL queries we get a list for each row. Fortunately, the order of columns in each row reflect the order of arguments to the model's __init__() function. As a result, we can use each row's list representation as positional arguments for __init__().



You should follow me on twitter here.

Labels: , , , ,

 
 

Welcome to the Brendel Consulting Blog

Hello, and welcome to our blog. This is a first message, just to get us started. More is to come...


You should follow me on twitter here.