Getting SQL query string from Django orm query

I often want to see the actual SQL generated by the orm query. This is important especially if you are running some DML queries like update, select etc and want to ensure that generated sql is exactly what you had in mind.

If the query results in a queryset, its really straight forward.

goto ./ shell and load the model (MyModel here)

>> items = MyModel.objects.all()
>> str(items.query)

That worked because .all() returns a queryset and all querysets have a query property from which you can grab the sql that generated that queryset.

What if you did this instead? item = MyModel.objects.first()
Now the returned object is not a queryset. Its a model object. Here, the above approach of str(item.query) will not work as this object does not have that query property. You can have similar scenarios when returned objects are int like here str(MyModel.objects.count()).

What I use in these scenarios is to grab them from the query log like so:

>>> from django import db
>>> db.connections['dbname'].queries

or this:

>>> from django import db
>>> db.connection.queries
'SELECT "main_producttag"."id", 
    "main_producttag"."active" FROM "main_producttag" ORDER BY "main_producttag"."id" ASC LIMIT 1', 
'time': '0.000'}]

When you have DEBUG = True in your settings, Django puts every SQL statement into corresponding connection.queries list in order of query execution. The time key, shows how long it took to execute statement (in seconds).

If you just want to see the last query or last few queries from the log, do this:

>>>from django.db import connection

The above will also work outside of a shell.

As you can see that this is OK if you are experimenting in shell. But what about request-response scenario? Well, you can use print, logging or Django debug toolbar

No Comments

You can leave the first : )

Leave a Reply

Your email address will not be published. Required fields are marked *