5

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)

enter image description here

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.

Aashish Gahlawat
  • 339
  • 1
  • 4
  • 21

2 Answers2

4

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.

icaine
  • 177
  • 5
  • Thanks for pointing out that it only works in debug mode. Where did you find this information? – viam0Zah Feb 07 '22 at 17:40
  • 1
    @viam0Zah https://docs.djangoproject.com/en/4.0/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running – icaine Feb 08 '22 at 15:57
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.

Willem Van Onsem
  • 397,926
  • 29
  • 362
  • 485
  • so is there a way to see the first one (Lazy)? because I was trying to solve [this](https://stackoverflow.com/questions/51532218/group-by-2-fields-combination-and-then-order-by-the-sum-of-each-group-multiple) problem, and was thinking to add expiry date to values, and see if it actually produce right result. – Aashish Gahlawat Jul 26 '18 at 11:00
  • 1
    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. – Willem Van Onsem Jul 26 '18 at 11:01