@kyanny's blog

My thoughts, my life. Views/opinions are my own.

Django: SQL クエリ実行回数を数える

Django Debug Toolbar が出す SQL 実行回数を Django shell や単体のスクリプトからも使いたい。

python - Get SQL query count during a Django shell session - Stack Overflow に叡智が詰まっている。

  • django.db.connection.queries に発行された SQL クエリの数が入っている
  • Django アプリケーションが複数のコネクションを扱う場合は django.db.connections.all() と組み合わせる
  • connection.queries のカウントをリセットするには django.db. reset_queries を使う

https://github.com/kyanny/django-select_related-prefetch_related-Prefetch/tree/master/mysite を例に試すとこういう感じ。

gist.github.com

python manage.py shell
In [1]: with open('query_count.py') as f:
   ...:     exec(f.read())
   ...:

In [2]: from polls.models import Choice, Question

In [3]: for c in Choice.objects.all():
   ...:         c.question
   ...:
(0.002) SELECT "polls_choice"."id", "polls_choice"."question_id", "polls_choice"."choice_text", "polls_choice"."votes" FROM "polls_choice"; args=()
(0.001) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 1 LIMIT 21; args=(1,)
(0.001) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 2 LIMIT 21; args=(2,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 2 LIMIT 21; args=(2,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 2 LIMIT 21; args=(2,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 2 LIMIT 21; args=(2,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 3 LIMIT 21; args=(3,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 3 LIMIT 21; args=(3,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 3 LIMIT 21; args=(3,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 3 LIMIT 21; args=(3,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 4 LIMIT 21; args=(4,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 4 LIMIT 21; args=(4,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 4 LIMIT 21; args=(4,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 4 LIMIT 21; args=(4,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 5 LIMIT 21; args=(5,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 5 LIMIT 21; args=(5,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 5 LIMIT 21; args=(5,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 5 LIMIT 21; args=(5,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 6 LIMIT 21; args=(6,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 6 LIMIT 21; args=(6,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 6 LIMIT 21; args=(6,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 6 LIMIT 21; args=(6,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 7 LIMIT 21; args=(7,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 7 LIMIT 21; args=(7,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 7 LIMIT 21; args=(7,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 7 LIMIT 21; args=(7,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 8 LIMIT 21; args=(8,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 8 LIMIT 21; args=(8,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 8 LIMIT 21; args=(8,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 8 LIMIT 21; args=(8,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 9 LIMIT 21; args=(9,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 9 LIMIT 21; args=(9,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 9 LIMIT 21; args=(9,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 9 LIMIT 21; args=(9,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 10 LIMIT 21; args=(10,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 10 LIMIT 21; args=(10,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 10 LIMIT 21; args=(10,)
(0.000) SELECT "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_question" WHERE "polls_question"."id" = 10 LIMIT 21; args=(10,)

In [4]: print(query_coun
query_count     query_count.py

In [4]: print(query_count())
41

In [5]: for c in Choice.objects.select_related('question').all():
   ...:         c.question
   ...:
(0.001) SELECT "polls_choice"."id", "polls_choice"."question_id", "polls_choice"."choice_text", "polls_choice"."votes", "polls_question"."id", "polls_question"."question_text", "polls_question"."pub_date" FROM "polls_choice" INNER JOIN "polls_question" ON ("polls_choice"."question_id" = "polls_question"."id"); args=()

In [6]: print(query_count())
1