When I checked the query generated by Django, it was issued in large numbers

Django:1.10

When you generate a query in Django, you'll end up with a lot of queries if you don't write it carefully.

Query log output

First, write the log settings in manage.py to output the query to the server console.

manage.py


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

Check the query

Let's execute a query to get the list of books published by the selected author that was used last time.

How to generate a query using the IN operator in Django http://qiita.com/nakkun/items/86a94e65fe6785325f54

Source


books = Book.objects.filter(author_id__in = form.cleaned_data['author'])

Execution result


(0.002) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)

The SQL was running as expected. I got the same query using "author_ \ _ id_ \ in" as shown below. (The number of "" is different)

Source


books = Book.objects.filter(author__id__in = form.cleaned_data['author'])

In the case of "author_ \ _ id_ \ _in", I was wondering if they would join, but that was not the case.

A large number of queries! ??

There is no particular problem so far, but if you try to display the author name after that, the execution result will be as follows.

Source


books = Book.objects.filter(author_id__in = form.cleaned_data['author'])
[print(book.author.name) for book in books]

Execution result


(0.003) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 1; args=(1,)
Hiroshi Mori
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani

I have issued a fairly useless query.

So you need to write a source that will issue a joined query like this:

Source


books = Book.objects.select_related().filter(author_id__in = form.cleaned_data['author'])
[print(book.author.name) for book in books]

Execution result


(0.004) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id", "books_author"."id", "books_author"."name" FROM "books_book" INNER JOIN "books_author" ON ("books_book"."author_id" = "books_author"."id") WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

Now you only have to issue the SQL once.

It may be better to develop while checking SQL before you get used to it so that you do not throw a large amount of SQL without knowing it.

Supplement

About 3 other methods.

① How to use prefetch_related

Source


books = Book.objects.filter(author_id__in = form.cleaned_data['author']).prefetch_related('author')
[print(book.author.name) for book in books]

Execution result


(0.001) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

② Starting from the Author model

Source


authors = Author.objects.filter(id__in = form.cleaned_data['author'])
[[print(book.atuhor.name) for book in author.book_set.all()] for author in authors]

Execution result


(0.002) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" = 1; args=(1,)
Hiroshi Mori
(0.000) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" = 3; args=(3,)
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

③ Use prefetch_related starting from the Author model

Source


authors = Author.objects.filter(id__in = form.cleaned_data['author']).prefetch_related("book_set")
[[print(book.atuhor.name) for book in author.book_set.all()] for author in authors]

Execution result


(0.002) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
(0.002) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

Recommended Posts

When I checked the query generated by Django, it was issued in large numbers
I referred to it when I got stuck in the django geodjango tutorial (editing)
What I did when I was angry to put it in with the enable-shared option
If I thought I didn't see the pyc file recently, it was quarantined in pycache by python3.
Where was the thrown place in the exception stack trace? Where it was generated?
Get the query string (query string) in Django
When I tried to run Python, it was skipped to the Microsoft Store
Django ~ Let's display it in the browser ~
In IPython, when I tried to see the value, it was a generator, so I came up with it when I was frustrated.
[Python] Precautions when retrieving data by scraping and putting it in the list
When I investigated whether the COTOHA API could understand comics, it was reasonable.
The _authenticate_with_backend function was obsolete in django auth.autenticate
When I try matplotlib in Python, it says'cairo.Context'
I checked the session retention period of django
I checked the calendar deleted in Qiita Advent Calendar 2016
I checked the reference speed when using python list, dictionary, and set type in.
It became TLE when I confirmed the operation with the print function in the competition pro
The file name saved by pysheng was a hexadecimal number, so I fixed it.
I tried to make the phone ring when it was posted at the IoT post
When I crawl the webapi that appears during rendering, it was played with CORS
I was able to repeat it in Python: lambda
What I learned by participating in the ISUCON10 qualifying
When I tried the AtCoder Beginner Contest, it was a terrible result, so I look back
The story I was addicted to when I specified nil as a function argument in Go