Avoid making multiple Django database calls

Question

I would like to query my Django database once and return a Queryset (assigned the name 'codes'), which I can iterate over to obtain each instance's 'internal_code' attribute without making further calls to the database. Here is my code, but I am unsure as to whether I am querying the database each time I use 'get' on the Queryset, and how I can avoid this:

codes = models.RecordType.objects.all()

permanent = sorted([
    (
        codes.get(
            external_code=category.value
        ).internal_code,
        self.labels.get(category, category.name)
    )
    for category in enums.Category
])

I am not a Djangonaut, but to me it is that you make a single query and use the returned queryset — JSRB, Jun 19 '20 at 13:45
use `RecordType.objects.values_list('internal_code', flat=True)` to get only the `internal_code` column returned back in list style, then u can `zip` with categories to obtain list of tuples, which is more readable imo. — minglyu, Jun 19 '20 at 13:48

Antonio Jesús · Answer 1 · 2020-06-19T15:20:08.507

Using the __in operator to filter all the RecordType with industry_code present in the Category enum:

codes = (
    RecordType.objects
    .filter(industry_code__in=[c.value for c in Category])
    .only('industry_code', 'internal_code')
)
category_labels = {c.value: self.labels.get(c, c.name) for c in Category}

permanent = sorted([
    (code.internal_code, category_labels[code.industry_code])
    for code in codes
])

Using .only in case RecordType has a lot of fields that you don't need.

but I am unsure as to whether I am querying the database each time I use 'get' on the Queryset

Yes. You can chain .filter(), .exclude(), and other methods that return a queryset without running it; with .get() the queryset is evaluated immediately (and every time), returning an instance.

score 0 · Answer 2 · answered Jun 19 '20 at 13:45

0

If you want to force an evaluation of the QuerySet you can do this:

codes = list(models.RecordType.objects.all())

answered Jun 19 '20 at 13:45

Adrian Klaver

15,886
2
17
28

daphtdazz · Answer 3 · 2020-06-19T14:39:18.693

What you wrote is roughly the way to do it, but you shouldn't call get() on it all the time. Each time you do it you are making a separate db query.

Django fetches lazily from the database, when you indicate that you want the results. E.g.

# first enable some logging to see the database queries
import logging.config
logging.config.dictConfig({
    'version': 1,
    'formatters': {
        'simple': {'format': '%(levelname)s %(message)s'},
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'simple',
        },
    },
    'root': {
        'level': 'WARNING',
        'handlers': ['console'],
    },
    'loggers': {
        'django.db.backends': {'level': 'DEBUG',},
    },
})


# No query made
codes = RecordType.objects.filter(
    industry_code__in=[cat.value for cat in enums.Category]
)

# show the query that will be made (roughly)
print(str(codes.query))

# force the queryset to be executed and return all the results.
# the queryset is executed when you iterate over it.
codes_by_industry_code = {
    code.industry_code: code
    for code in codes
}

# now you can set up your list:
permanent = [
    (
        codes_by_industry_code[category.value].internal_code,
        self.labels.get(category, category.name)
    )
    for category in enums.Category
]

Now, if you want to be sure that the queries only happen in the right place, you should use a testing tool such as described in this question to test your code. That is the only way to be sure going forward that your code is doing what it should be in terms of db queries.

Avoid making multiple Django database calls

3 Answers3