Djangoのクエリセット評価タイミング実験 #Python

Djangoのクエリセット評価タイミング
http://djangoproject.jp/doc/ja/1.0/ref/models/querysets.html

実際にSQL出しながら検証していきます。

準備

環境:
Python 2.7.10
Django 1.9.7
MySQL 5.6.30

データ:
単純な書籍管理クラスを適当に作成

mysql> select * from book_book;
+----+------+-------------+-----+----------------------------+----------------------------+
| id | name | description | tag | created_at                 | updated_at                 |
+----+------+-------------+-----+----------------------------+----------------------------+
|  1 | A    | A's book    | A   | 2016-06-22 01:50:25.895551 | 2016-06-22 01:50:25.895986 |
|  2 | B    | B's book    | B   | 2016-06-22 01:50:36.743094 | 2016-06-22 01:50:36.743139 |
|  3 | C    | C's book    | C   | 2016-06-22 01:50:46.279098 | 2016-06-22 01:50:46.279141 |
|  4 | D    | D's book    | D   | 2016-06-22 01:50:56.831194 | 2016-06-22 01:50:56.831237 |
|  5 | E    | E's book    | E   | 2016-06-22 01:51:06.663500 | 2016-06-22 01:51:06.663562 |
+----+------+-------------+-----+----------------------------+----------------------------+
5 rows in set (0.00 sec)

発行SQLの表示:

settings.py

LOGGING = {
    'version': 1,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },

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

実践

まずはgetしてみます。

>>> query = Book.objects.get(id=1)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)

getで取得したらいきなりSQLが発行されました。
これは、

>>> type(query)
<class 'book.models.Book'>

クエリセットではなくオブジェクトで取得されるからですね

これをfilterに変えると

>>> query = Book.objects.filter(id=1)
>>> type(query)
<class 'django.db.models.query.QuerySet'>

クエリセットで取得され、この時点ではSQLは発行させません。
これにドキュメントに従って各操作をしていきます。

イテレーション

>>> for book in query:
...   print book
... 
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
Book object

スライス

>>> query[:1]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 1; args=(1,)
[<Book: Book object>]

repr

>>> repr(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 21; args=(1,)
'[<Book: Book object>]'

len

>>> len(query)
(0.004) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
1

list

>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]

ドキュメント通り各操作でクエリセットが評価されSQL発行されました。

キャッシュ

1回評価されたクエリセットはキャッシュされ、再度評価される操作をしてもSQL発行されません。

>>> query = Book.objects.filter(id=1)
>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
>>> list(query)
[<Book: Book object>]
>>> list(query)
[<Book: Book object>]
>>> query[:1]
[<Book: Book object>]
>>> len(query)
1

ここでキャッシュされるのはクエリセットであってSQLではないので注意が必要です。

>>> # クエリセット評価結果はキャッシュされる
>>> for i in xrange(1, 5):
...   list(query)
... 
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]

>>> # SQLの結果自体はキャッシュされない
>>> for i in xrange(1, 5):
...   list(Book.objects.filter(id=1))
... 
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.002) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.002) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)

また、クエリセットに条件追加したものはキャッシュされません。
(下例はBook.objects.filter(id=1).order_by('id')と等価なので)

>>> query.order_by('id')
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC LIMIT 21; args=(1,)
[<Book: Book object>]
>>> query.order_by('id')
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC LIMIT 21; args=(1,)
[<Book: Book object>]

この場合は次のようにすればキャッシュされます。

>>> query2 = query.order_by('id')
>>> list(query2)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC; args=(1,)
[<Book: Book object>]
>>> list(query2)
[<Book: Book object>]

次のような順番でも発行されるSQLが変わるためそれぞれ個別評価になるようです。

>>> query = Book.objects.filter(id=1)
>>> query[:1]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 1; args=(1,)
[<Book: Book object>]
>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]

query[:1]を最初にするとBook.objects.filter(id=1)のクエリに対してLimit 1を付加するためです。
ただし、list(query)の後にquery[:1]をするとlist(query)のスライスを取得するためキャッシュが効く模様。