Simeon Franklin

Blog :: Hidden Django QuerySet Features I

14 February 2011

I've been paying attention to the generated SQL the Django ORM provides lately. I've had to a bit of performance tuning on some apps I wrote so I started out with the django-debug-toolbar app which will show you all the queries run on a given page and their runtime. This is an indispensable tool in my daily toolkit but I wanted to play with creating queries in my console. How do I see the SQL that is generated?

Somewhere (probably just through introspection) I found the semi-private _as_sql() method.

>>> User.objects.filter(is_staff=True)._as_sql()
('SELECT U0."id" FROM "auth_user" U0 WHERE U0."is_staff" = %s ', (True,))

Notice that this doesn't return all the fields ("select id from..."). This method also won't work on value QuerySets and shouldn't be depended on as it is not a part of the public interface of the QuerySet class. It does helpfully returns a two part tuple of the query string with placeholders and a tuple containing the query parameters. This is useful if you want to tweak the parameters on the fly and paste them into a db console. A better method to get the sql actually being executed is to access the .query member of a QuerySet:

>>> users = User.objects.filter(is_staff=True)
>>> users.query
<django.db.models.sql.query.BaseQuery object at 0x9df63ac>
>>> str(users.query)
'SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name", 
"auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", 
"auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", 
"auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."is_staff" = True '

Finally you can see all the queries that have run by looking at the connection object.

>>> from django.db import connection
>>> print connection.queries
[{'sql': 'SELECT ....',
  'time': '0.009'},]

I redacted the actual query but this returns rows of dicts with the sql that was run and how long it took to run.

blog comments powered by Disqus