Django Query Set Evaluation Timing http://djangoproject.jp/doc/ja/1.0/ref/models/querysets.html
We will verify while actually issuing SQL.
environment: Python 2.7.10 Django 1.9.7 MySQL 5.6.30
data: Create a simple book management class appropriately
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)
View issuing SQL:
settings.py
LOGGING = {
'version': 1,
'handlers': {
'console': {
'level': 'DEBUG',
'class': 'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'level': 'DEBUG',
'handlers': ['console'],
},
}
}
First, let's get it.
>>> 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,)
When I got it with get, SQL was issued suddenly. this is,
>>> type(query)
<class 'book.models.Book'>
Because it's retrieved by an object, not a queryset.
If you change this to filter
>>> query = Book.objects.filter(id=1)
>>> type(query)
<class 'django.db.models.query.QuerySet'>
It is retrieved by the query set and no SQL is issued at this point. Follow this document to perform each operation.
** Iteration **
>>> 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
slice
>>> 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>]
As documented, each operation evaluated the query set and issued SQL.
A query set that has been evaluated once is cached, and no SQL is issued even if the operation is evaluated again.
>>> 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
Note that it is the query set that is cached here, not the SQL.
>>> #Query set evaluation results are cached
>>> for i in xrange(1, 5):
... list(query)
...
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
>>> #SQL results themselves are not cached
>>> 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,)
Also, conditions added to the query set are not cached. (Because the example below is equivalent to 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>]
In this case, it will be cached as follows.
>>> 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>]
Since the issued SQL changes even in the following order, it seems that each will be evaluated individually.
>>> 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>]
This is because when query [: 1] is set first, Limit 1 is added to the query of Book.objects.filter (id = 1). However, if you do query [: 1] after list (query), the cache will work because you will get a slice of list (query).