Getting the SQL from a Django QuerySet

Question

How do I get the SQL that Django will use on the database from a QuerySet object? I'm trying to debug some strange behavior, but I'm not sure what queries are going to the database.

Solution 1

You print the queryset's query attribute.

>>> queryset = MyModel.objects.all()
>>> print(queryset.query)
SELECT "myapp_mymodel"."id", ... FROM "myapp_mymodel"

Solution 2

Easy:

print my_queryset.query

For example:

from django.contrib.auth.models import User
print User.objects.filter(last_name__icontains = 'ax').query

It should also be mentioned that if you have DEBUG = True, then all of your queries are logged, and you can get them by accessing connection.queries:

from django.db import connections
connections['default'].queries

The django debug toolbar project uses this to present the queries on a page in a neat manner.

Solution 3

The accepted answer did not work for me when using Django 1.4.4. Instead of the raw query, a reference to the Query object was returned: <django.db.models.sql.query.Query object at 0x10a4acd90>.

The following returned the query:

>>> queryset = MyModel.objects.all()
>>> queryset.query.__str__()

Solution 4

This middleware will output every SQL query to your console, with color highlighting and execution time, it's been invaluable for me in optimizing some tricky requests

http://djangosnippets.org/snippets/290/

Solution 5

As an alternative to the other answers, django-devserver outputs SQL to the console.

View more solutions

Comments

[hughes]

It probably didn't work because you just typed queryset.query instead of print queryset.query, which calls __str__()

[Chad]

@hughes is right. If you don't want to print it and want it as a string, instead of calling __str__() to get it as a string you should do str(queryset.query).

[hanleyhansen]

I found this which mentions it implicitly but nothing that explicitly documents the above

[Rafay]

I am using ipdb to debug and it prints a reference to the query object when I do p queryset. p queryset.__str__() produces the desired result so this is a better answer.

[dbn]

str(queryset.query) would be more pythonic.

[gregoltsov]

Note that the output of query is not valid SQL, because "Django never actually interpolates the parameters: it sends the query and the parameters separately to the database adapter, which performs the appropriate operations." Source: code.djangoproject.com/ticket/17741

[Asela Liyanage]

This doesn't always output valid SQL, see the other answers

[aldux]

I had this problem on manage.py shell (__str__() did the job)

[Python Team]

where i have to write query... MyModel is a class which is in models.py file... My doubt is where i have to write sql query for retrieve value from the table.

[Torsten Engelbrecht]

If you just do it in the debugger (pdb) you also have to use the __str__ method, even p is supposed to print.

[danius]

There is no way to get actual SQL without executing the query first, final SQL is generated by the surrounding RDBMS driver, not Django. The answer is correct as it's the most you can get with Django QuerySet.

[Ariel]

Is it possible to see the SQL query before the it is actually executed? I'm getting a database error and want to see what SQL it's trying to execute, but when I run this I simply get a database error and cannot see the full SQL.

[Lutz Prechelt]

If you do anything other than print, you may have to use str(queryset.query), because type(queryset.query) == Query.

[fjsj]

To get the valid SQL from Postgres, use this: stackoverflow.com/questions/8112554/…