How to view the SQL statements executed by Django ORM

created at 07-17-2021 views: 3

Django ORM encapsulates database operations quite well, and most of the daily database operations can be implemented through ORM. But Django hides the query process in the background, which may be slightly obscure during development, and improper use can also cause excessive overhead.

So how to check what SQL statement django executed when? The answer is to use Logging.

First go directly to the method, add the LOGGING option in settings.py, and adjust the logging level to DEBUG:

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'formatters': {
        'simple': {
            'format': '[%(asctime)s] %(message)s'
        },
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'simple'
        },
    },
    'loggers': {
        'django': {
            'handlers': ['console'],
            'level': 'DEBUG',
        },
    },
}

Then start runserver, browse the page that needs to access the database, and you can see the related logs in the shell, as follows:

[2018-04-21 21:09:14,676] (0.002) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`cover`, `blog_article`.`content`, `blog_article`.`pub_date`, `blog_article`.`category_id`, `blog_article`.`views`, `blog_category`.`id`, `blog_category`.`name` FROM `blog_article` INNER JOIN `blog_category` ON (`blog_article`.`category_id` = `blog_category`.`id`) WHERE `blog_article`.`pub_date` < '2018-04-21 13:09:14.601856' ORDER BY `blog_article`.`pub_date` DESC LIMIT 10; args=('2018-04-21 13:09:14.601856',)
[2018-04-21 21:09:14,678] (0.000) SELECT (`blog_article_topics`.`article_id`) AS `_prefetch_related_val_article_id`, `blog_topic`.`id`, `blog_topic`.`name`, `blog_topic`.`number` FROM `blog_topic` INNER JOIN `blog_article_topics` ON (`blog_topic`.`id` = `blog_article_topics`.`topic_id`) WHERE `blog_article_topics`.`article_id` IN (3, 4, 5, 6, 7, 8, 9, 10, 11, 12) ORDER BY `blog_topic`.`number` ASC; args=(3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
[2018-04-21 21:09:14,708] "GET / HTTP/1.1" 200 22325

The log printed above is part of the SQL statement executed when the first ten articles are fetched on the homepage of my blog, and the corresponding QuerySet is

Article.objects.filter(pub_date__lt=timezone.now())[:10] \
.defer('author', 'category__number') \
.select_related('category') \
.prefetch_related('topics')

Logging can not only view SQL statements, but also know when Django executed SQL. In some cases, we can judge in this way whether the SQL statement is executed repeatedly in the background, which is convenient for guiding the optimization of database access.

Django uses Python's built-in logging module to perform system logging. Python discusses the usage of this module in detail in its own documentation. However, if you have never used Python's logging framework (or even if you have one), you can take a look at the Logging Quick Start provided by django.

Please log in to leave a comment.