Why is "connection.queries" returning an empty list in Django?

Question

from django.db import connection, reset_queries

Prints: []

reset_queries()
p = XModel.objects.filter(id=id) \
.values('name') \
.annotate(quantity=Count('p_id'))\
.order_by('-quantity') \
.distinct()[:int(count)]
print(connection.queries)

While this prints:

reset_queries()
tc = ZModel.objects\
.filter(id=id, stock__gt=0) \
.aggregate(Sum('price'))
print(connection.queries)

I have changed fields names to keep things simple. (Fields are of parent tables i.e. __ to multiple level)

I was trying to print MySQL queries that Django makes and came across connection.queries, I was wondering why doesn't it prints empty with first, while with second it works fine. Although I am getting the result I expect it to. Probably the query is executed. Also am executing only one at a time.

That is logical: queries are performed lazy: only when you need them. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 26, 2018 at 10:49
sorry couldn't get, can you please briefly explain? @WillemVanOnsem — Aashish Gahlawat
– Aashish Gahlawat, Commented Jul 26, 2018 at 10:51

icaine · Accepted Answer · 2022-02-08 16:11:00Z

15

As the accepted answer says you must consume the queryset first since it's lazy (e.g. list(qs)).

Another reason can be that you must be in DEBUG mode (see FAQ):
connection.queries is only available if Django DEBUG setting is True.

edited Feb 8, 2022 at 16:11

answered Jul 4, 2021 at 9:32

icaine

4744 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

viam0Zah Over a year ago

Thanks for pointing out that it only works in debug mode. Where did you find this information?

icaine Over a year ago

@viam0Zah docs.djangoproject.com/en/4.0/faq/models/…

willeM_ Van Onsem · Accepted Answer · 2018-07-26 10:53:42Z

3

Because QuerySets in Django are lazy: as long as you do not consume the result, the QuerySet is not evaluated: no querying is done, until you want to obtain non-QuerySet objects like lists, dictionaries, Model objects, etc.

We can however not doe this for all ORM calls: for example Model.objects.get(..) has as type a Model object, we can not postpone that fetch (well of course we could wrap it in a function, and call it later, but then the "type" is a function, not a Model instance).

The same with a .aggregate(..) since then the result is a dictionary, that maps the keys to the corresponding result of the aggregation.

But your first query does not need to be evaluated. By writing a slicing, you only have added a LIMIT statement at the end of the query, but no need to evaluate it immediately: the type of this is still a QuerySet.

If you would however call list(qs) on a QuerySet (qs), then this means the QuerySet has to be evaluated, and Django will make the query.

The laziness of QuerySets also makes these chainings possible. Imagine that you write:

Model.objects.filter(foo=42).filter(bar=1425)

If the QuerySet of Model.objects.filter(foo=42) would be evaluated immediately, then this could result in a huge amount of Model instances, but by postponing this, we now filter on bar=1425 as well (we constructed a new QuerySet that takes both .filter(..)s into account). This can result in a query that can be evaluated more efficiently, and for example, can result in less data that has to be transferred from the database to the Django server.

answered Jul 26, 2018 at 10:53

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

2 Comments

Aashish Gahlawat Over a year ago

so is there a way to see the first one (Lazy)? because I was trying to solve this problem, and was thinking to add expiry date to values, and see if it actually produce right result.

willeM_ Van Onsem Over a year ago

You can obtain the query out of a queryset, with str(qs.query) so if you want to inspect the query, that might be a solution. Then you do not have to reset the queries as well. Or you can use list(qs) to force evaluation, like written in the answer.

Super Kai - Kazuya Ito · Accepted Answer · 2022-12-23 22:34:11Z

The documentation says QuerySets are lazy as shown below:

QuerySets are lazy – the act of creating a QuerySet doesn’t involve any database activity. You can stack filters together all day long, and Django won’t actually run the query until the QuerySet is evaluated. Take a look at this example:

>>> q = Entry.objects.filter(headline__startswith="What")
>>> q = q.filter(pub_date__lte=datetime.date.today())
>>> q = q.exclude(body_text__icontains="food")
>>> print(q)

Though this looks like three database hits, in fact it hits the database only once, at the last line (print(q)). In general, the results of a QuerySet aren’t fetched from the database until you “ask” for them. When you do, the QuerySet is evaluated by accessing the database. For more details on exactly when evaluation takes place, see When QuerySets are evaluated.

Collectives™ on Stack Overflow

Why is "connection.queries" returning an empty list in Django?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related