Empty Thoughts

Did you ever need to display the SQL that Django is generating, within your shell? I was playing around with a problem, which I’ll write more about later, and I needed this exact functionality to help me learn how various options in the Django ORM were being translated into SQL.

I did not come up with this technique but found it while browsing around for an answer. All credit goes to DaveA.

Put this in a utilities module and just import it when needed.

def show_sql(q):
    cols, sql, args = q._get_sql_clause()
    return "SELECT %s %s" % (', '.join(cols), sql % tuple(args))

...and now for an example:

>>> from utils import show_sql
>>> from blog.models import Section
>>> q = Section.objects.all()
>>> show_sql(q)
'SELECT "blog_section"."id", "blog_section"."name", "blog_section"."slug", 
"blog_section"."position"  FROM "blog_section" 
ORDER BY "blog_section"."position" ASC'
>>> q = Section.objects.filter(name__istartswith='djan')
>>> q
[<Section: Django>]
>>> show_sql(q)
u'SELECT "blog_section"."id", "blog_section"."name", "blog_section"."slug", 
"blog_section"."position"  
FROM "blog_section" 
WHERE ("blog_section"."name" LIKE djan% ESCAPE \'\\\') 
ORDER BY "blog_section"."position" ASC'

It’s been a great learning tool.

Update

As Jonathan Buchanan points out in the comments you can also use the method described in the Django FAQ:

>>> from django.db import connection
>>> connection.queries
{'time': '0.000', 'sql': u'SELECT "blog_section"."id","blog_section"."name","blog_section"."slug","blog_section"."position" FROM "blog_section" WHERE id NOT IN (SELECT DISTINCT section_id FROM blog_post) ORDER BY "blog_section"."position" ASC'}

To get the just the last query, slice the list:

>>> connection.queries[-1]

This method is also used in the SQL Log Middleware snippet that adds the SQL queries to the bottom of your templates.