Querying Specific Fields (Specific Attributes of models) in Django

Posted on Views: Waline: Word count in article: 543 Reading time ≈ 2 mins.
This article introduces that Django, as an ORM framework, can perform database operations by encapsulating APIs without writing SQL. It focuses on the issues of commonly used query methods in Django's Model, such as get, all, and filter, as well as the usage of the values and values_list methods to obtain specified attribute values. The official website link is also mentioned.

Django is an ORM framework, which means developers don’t need to write SQL. Instead, they can perform database operations through some encapsulated APIs, eliminating the need to deal with the details of various databases.

get, all, filter

All along, when querying the database through the Model in Django, the most frequently used methods are get, all, and filter. For example,

1
_ = ExampleModel.objects.all()

The returned result is a QuerySet, and the elements inside are Model objects, and all fields in the corresponding table are returned, similar to the SELECT * FROM ...... query in SQL. When the business requires this, there is no problem. However, when the business doesn’t need so much data, there are two issues:

  1. Querying all fields will consume a large amount of memory. Especially when there is a large amount of data in the database table, this can become a performance bottleneck.
  2. The desired data is not directly obtained, and additional code is needed to filter the data, which is a waste of effort, and the written code may not be good.
    In fact, Django itself provides methods to easily obtain the content of specific fields.

Remarks:
These two points are not absolute, especially in scenarios with extremely large data volume and high concurrency.
The query API of the Model in Django is ultimately converted into SQL for execution. When the API is complex, it means the SQL is complex. This shifts the computational pressure from the server where the code is located to the database server. When the data volume and concurrency (especially concurrency) are huge, the database server surely can’t bear it. So at this time, perhaps only simple queries can be done, and then data filtering and splicing are done by writing code yourself.

values, values_list

When writing a function today, I needed to query the value of a specified attribute of a model. There were similar situations before, but generally, I directly used all or filter to query and then filtered the data myself. However, today, since I knew that the data volume of this table would be very large in the future (about 5,000 - 7,000 new records are generated per minute), if I still used all or filter as before, there would be problems. So I discovered the values and values_list methods.

  • values
    When using the values method to obtain specified attributes, the attribute names need to be passed in.

    1
    2
    3
    _ = ExampleModel.objects.values('id', 'username')
    # The returned result is a QuerySet, and the elements inside are in dict format
    # [{'id':1, 'username': 'Austin'}, {'id':2, 'username':'Sam'}, ...]

    This is equivalent to SELECT id, username FROM ...... in SQL, only returning the values of the corresponding attributes (fields).

  • values_list
    It is very similar to values, except that the elements in the returned QuerySet are in tuple format.

    1
    2
    3
    _ = ExampleModel.objects.values_list('id', 'username')
    # The returned result is a QuerySet, and the elements inside are in dict format
    # [(1, 'Austin'), (2, 'Sam') ...]

  • Getting a String - formatted Return Value
    When only the value of one attribute is needed, a string - formatted return value can be obtained by specifying flat=True, which often better meets the requirements.

    1
    2
    3
    _ = ExampleModel.objects.values_list('username', flat=True)
    # The obtained return value is as follows:
    # <QuerySet['Austin', 'Sam', ... ]>

Official website link: https://docs.djangoproject.com/en/3.1/ref/models/querysets/#django.db.models.query.QuerySet.values