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