Viewing Django SQL Queries

Django ORM makes it easy to interact with the database. To understand what's going on behind the scenes or see SQL performance, we can record all the SQL queries that are being executed. In this article, we will see different ways to achieve this.

Using django-debug-toolbar

The Django debug toolbar provides an interface for displaying query debugging information: all executed SQL queries and the time spent on them.

When building REST APIs or microservices that don't use the Django templating engine, this method won't work. In these situations, we must output SQL queries to the console.

Using django-extensions

Django-extensions provides many utilities for productive development. For the runserver_plus and shell_plus commands, it also accepts an optional --print-sql argument that prints all running SQL queries.

./manage.py runserver_plus --print-sql
./manage.py shell_plus --print-sql

Whenever a SQL query is executed, it prints the query and the time spent on it in the console.

In [42]: User.objects.filter(is_staff=True)
Out[42]: SELECT "auth_user"."id",
        "auth_user"."password",
        "auth_user"."last_login",
        "auth_user"."is_superuser",
        "auth_user"."username",
        "auth_user"."first_name",
        "auth_user"."last_name",
        "auth_user"."email",
        "auth_user"."is_staff",
        "auth_user"."is_active",
        "auth_user"."date_joined"
   FROM "auth_user"
  WHERE "auth_user"."is_staff" = true
  LIMIT 21


Execution time: 0.002107s [Database: default]

<QuerySet [<User: anand>, <User: chillar>]>

Using django-querycount

Django-querycount provides middleware (learn about new middleware format in Django 2) to display the number of SQL queries and display duplicate queries on the console.

|------|-----------|----------|----------|----- -----|------------|
| type | database | reads | writes | totals | Duplicates |
|------|-----------|----------|----------|-------- --|------------|
| RESP | default | 3 | 0 | 3 | 1 |
|------|-----------|----------|----------|-------- --|------------|
Total queries: 3 in 1.7738s


Repeated 1 times.
SELECT "django_session"."session_key",
"django_session"."session_data", "django_session"."expire_date" FROM
"django_session" WHERE("django_session"."session_key" =
'dummy_key AND "django_session"."expire_date"
> '2018-05-31T09:38:56.369469+00:00'::timestamptz)

This package provides additional options to customize the output.

Django logging

Instead of using some third party package, we can use the django.db.backends logger to print all SQL queries.

Add django.db.backends to the list of loggers and set the message level and handlers.

'loggers': {
         'django.db.backends': {
             'level': 'DEBUG',
             'handlers': ['console', ],
         },

In the runserver console we see all the SQL queries that are being executed.

 (0.001) Select & quot; django_admin_log & quot;. & quot; Id & quot ;, & quot; django_admin_log & quot;.. & quot; Action_time & quot ;, & quot; jango_admin_log & quot;. & quot; user_id & quot;, & quot; django_admin_log & quot;. & quot; concent_type_id & quot ;, & quango_admin_log files ;."object_id", "django_admin_log"."object_repr", "django_admin_log"."action_flag", "django_admin_log"."change_message", "auth_user"."id", "auth_user" ;."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user" ;."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", "django_content_type" ;."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_admin_log" INNER JOIN "auth_user" ON ("django_admin_log"."user_id" = "auth_user"."id") LEFT OUTER JOIN "django_content_type" ON ("django_admin_log"."content_type_id" = "django_content_type"."id") WHERE "django_admin_log"."user_id" = 4 ORDER BY "django_admin_log"."action_time" DESC LIMIT 10; args=(4,)
[2018/06/03 15:06:59] HTTP GET /admin/ 200 [1.69, 127.0.0.1:47734]

These are several ways to log all SQL queries to the console. We can also write our own custom middleware to better log these requests and get more information.

Also see a tip about Finding performance bottlenecks in a Django project.

Back to Top