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 ./manage.py 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
[{'sql': 
'SELECT "main_producttag"."id", 
    "main_producttag"."name", 
    "main_producttag"."slug", 
    "main_producttag"."description", 
    "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
>>>print(connection.queries[-1:])

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

Tek Shinobi
Author: Tek Shinobi

Hiya Ninjas, I am the ninja who invented the fire, wheel, science, technology and everything intelligent this humanity has ever experienced since its evolution from monkeys and germs. Actually, above is a partial list. I also was involved with the Big Bang that created this universe. What was it like before the Big Bang is so secret that if I tell you, I will have to turn you into a hobbit and force you to be my gardener. Okay. Sayonanra, Namaste Tek Shinobi




No Comments


You can leave the first : )



Leave a Reply

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