408

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.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
exupero
  • 9,136
  • 8
  • 47
  • 63
  • 10
    This isn't a duplicate. the linked question is quite a different topic. – craigds Mar 23 '17 at 20:45
  • 2
    @Wooble this isn't a duplicate. It's in the same area as the other question but this question relates to a specific query, not ALL queries. – Akrikos Aug 22 '19 at 18:08

5 Answers5

637

You print the queryset's query attribute.

>>> queryset = MyModel.objects.all()
>>> print(queryset.query)
SELECT "myapp_mymodel"."id", ... FROM "myapp_mymodel"
Tom
  • 22,301
  • 5
  • 63
  • 96
jpwatts
  • 8,297
  • 1
  • 22
  • 8
  • I found [this](https://docs.djangoproject.com/en/dev/ref/contrib/gis/tutorial/#automatic-spatial-transformations) which mentions it implicitly but nothing that explicitly documents the above – hanleyhansen Sep 24 '13 at 22:41
  • 47
    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: https://code.djangoproject.com/ticket/17741 – gregoltsov Jul 07 '14 at 14:50
  • 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. – Python Team Nov 17 '14 at 07:10
  • 2
    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. – Ariel Jan 05 '21 at 12:43
  • 1
    If you do anything other than `print`, you may have to use `str(queryset.query)`, because `type(queryset.query) == Query`. – Lutz Prechelt Aug 04 '21 at 14:35
  • 4
    To get the valid SQL from Postgres, use this: https://stackoverflow.com/questions/8112554/printing-django-queryset-sql-with – fjsj Nov 24 '21 at 15:00
  • If you want to pretty print, you may use sqlparse package, or look at more detailed blogpost: https://roman.pt/posts/pretty-print-django-sql/ – 404pio Nov 19 '22 at 18:31
73

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.

siddharth Tyagi
  • 3
  • 1
  • 1
  • 2
Mike Axiak
  • 11,827
  • 2
  • 33
  • 49
  • 3
    This doesn't always output valid SQL, see the other answers – aehlke Jul 21 '14 at 21:12
  • 2
    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. – danius Jan 10 '16 at 07:48
68

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__()
Community
  • 1
  • 1
Hakan B.
  • 2,319
  • 23
  • 29
  • 32
    It probably didn't work because you just typed `queryset.query` instead of `print queryset.query`, which calls `__str__()` – hughes May 28 '13 at 17:49
  • 16
    @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)`. – Chad May 30 '13 at 23:31
  • 1
    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. – Rafay Jun 25 '14 at 05:15
  • 21
    str(queryset.query) would be more pythonic. – dbn Jul 03 '14 at 19:34
  • I had this problem on `manage.py shell` (__str__() did the job) – aldux Sep 24 '14 at 21:49
  • If you just do it in the debugger (`pdb`) you also have to use the `__str__` method, even `p` is supposed to print. – Torsten Engelbrecht Dec 03 '14 at 08:12
  • Something similar happens if you try to use [pprint](https://docs.python.org/3/library/pprint.html). In this case it should be: `pprint(str(queryset.query))` – djvg Oct 28 '22 at 07:03
12

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/

Guillaume Esquevin
  • 2,992
  • 20
  • 25
12

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

Tomasz Zieliński
  • 16,136
  • 7
  • 59
  • 83