Optimizing Queries in Django: Selecting Individual Fields using Only / Abenezer Belachew

How the Django ORM works (briefly)

One aspect of Django I really enjoy is the abstraction it provides when accessing and modifying the database/s. Using Django's ORM (Object-Relational Mapper), we can interact with the database using Python.
The Python code gets converted to SQL code to deal with the relational database/s of the project. Using a driver/adapter, it translates the python code you write into queries for the database you are using. This means you don't have to be that concerned about whether your SQL queries are specific enough for the database you are using. It also allows for a switch of databases (SQLite to Postgres or Postgres to MySQL, etc..) without much hassle.
The adapter is the bridge that helps in facilitating the transport of getting data from the ORM to the database. If you have used postgres as a database for your python projects, you most likely have heard of Psycopg. Psycopg's extensions allow you to access the many features offered by PostgreSQL.
This abstraction speeds up development and makes it easier to migrate or switch to different databases. But it also means we may not be writing the most efficient queries to access data.

Optimizing your query using Only

Consider a model that consists of numerous fields

class Address(models.Model):
    first_name = models.CharField(max_length=256, blank=True)
    last_name = models.CharField(max_length=256, blank=True)
    company_name = models.CharField(max_length=256, blank=True)
    street_address_1 = models.CharField(max_length=256, blank=True)
    street_address_2 = models.CharField(max_length=256, blank=True)
    city = models.CharField(max_length=256, blank=True)
    city_area = models.CharField(max_length=128, blank=True)
    postal_code = models.CharField(max_length=20, blank=True)
    country = models.CharField(max_length=20, blank=True)
    country_area = models.CharField(max_length=128, blank=True)
    phone = models.CharField(max_length=20, blank=True)
    ...

If I wanted to get the cities of all the addresses, I would do something like this:

# views.py
from django.shortcuts import render
from .models import Address  


def cities(request):
    addresses = Address.objects.all()
    return render(request, "cities.html", {"addresses": addresses)

<!-- cities.html -->
<h3>Cities</h3>
{% for address in addresses %}
    <p>- {{ address.city }}</p>
{% endfor %}

or this

# views.py
from django.shortcuts import render
from .models import Address

def cities(request):
		addresses = Address.objects.all()
		cities = [address.city for address in addresses]
		return render(request, "cities.html", {"cities": cities})

<!-- cities.html -->
<h3>Cities</h3>
{% for city in cities %}
    <div>- {{ city }}</div>
{% endfor %}

The second method may look more effective but both methods are executing the same SQL query. To check the query being executed we can add a .query method to the object we are performing the query on.

def cities(request):
    addresses = Address.objects.all()
    cities = [address.city for address in addresses]

    query = addresses.query

    return render(request, "cities.html", 
				{"addresses": addresses, "query": query})

Add this to the end of cities.html to see the SQL code generated:

<h3>SQL Executed</h3>
{{ query }}
</p>

This is the SQL query that is executed:

SELECT "address_address"."id", "address_address"."first_name", "address_address"."last_name", "address_address"."company_name", 
"address_address"."street_address_1", "address_address"."street_address_2", "address_address"."city", "address_address"."city_area",
 "address_address"."postal_code", "address_address"."country", "address_address"."country_area", "address_address"."phone" FROM "address_address"

As you can see, it is getting data that we don't really need. It is getting fields like first_name, last_name, street_address_1, etc... that don't help us in getting the city name of an Address.

Using Only

So how do we select an individual field like `city`?

We use only.

def cities(request):
    cities = Address.objects.only('city')
	query = cities.query

    return render(request, "cities.html", 
				{"cities": cities, "query": query})

With the changes above, this is the new query being executed:

SELECT "address_address"."id", "address_address"."city" FROM "address_address"

It only gets the id (required) and the city field.

We can now update cities.html

<h3>Cities</h3>
{% for city in cities %}
    <div>{{ city.city }}</div>
{% endfor %}
<p>
<h3>SQL Executed</h3>
{{ query }}
</p>

Why are we writing `city.city`?

That is because cities contains the id and the city field as mentioned above. We need to specify what it is exactly we are asking.

Conclusion

We can use only to select specific fields in a django model instead of returning everything. It is important to note that only can have more than one argument. If we wanted to get the city and city_area, we can do this: Address.objects.only('city', 'city_area')

Hope you found this helpful.

Optimizing Queries in Django: Selecting Individual Fields using Only