How to view the implementation of SQL statements executed by Django ORM

Django ORM encapsulation of database operations is quite perfect , most of the daily database operations can be realized through the ORM . But django will query the process hidden in the background , which may be slightly obscure in the development , and the use of improper way will cause excessive overhead .

So how do you see when django executes what sql statement? The answer is to use Logging.

First directly on the method, in the middle of adding LOGGING options, adjust the logging level to DEBUG can be:

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

Then start the runserver, browse to the page where you need 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 my blog's home page fetches the first ten posts, whose corresponding QuerySet is

(pub_date__lt=())[:10] \
.defer('author', 'category__number') \
.select_related('category') \
.prefetch_related('topics')

Logging not only allows you to view SQL statements, but also to know when django has executed SQL. in some cases, we can use this way to determine whether the backend has repeatedly executed SQL statements, so that we can guide the optimization of database access.

Django uses Python's built-in logging module to perform system logging.

To this article on how to view the implementation of Django ORM execution of SQL statements to this article, more related to the implementation of Django ORM SQL statements content please search my previous posts or continue to browse the following related articles I hope you will support me in the future more!