Working With Databases Correctly in Django

1 What is ORM

The main function of ORM (Object Relational Mapping) is to associate data with tables in the database in programming. ORM converts different types of data into objects in the database, and at the same time provides methods to interact with the objects. The following is an introduction to the ORM of Django.

Django’s Object-Relational Model or ORM provides a great productivity shortcut: not only generating decent SQL queries for common use cases, but providing model access/update functionality that comes complete with validation and security.

The Django ORM, like any ORM, converts data from different types into objects that we can use pretty consistently across supported databases. Then it provides a set of methods for interacting with those objects.

- Two Scoops of Django

Thanks to ORM, we can access the database without writing complex SQL statements. Handwritten SQL code may also have a major security risk-SQL injection. In order to avoid the above problems, it is recommended to use a mature and secure framework like Django to operate the database.

2 Handling exceptions

Django’s objects.get() method is used to try to get a record that meets the criteria. objects.get() will throw an exception if the database does not contain or contains multiple eligible records. If you don’t want to use objects.filter() to get QuerySet and then use objects.filter().exists() to judge the query result, you can use exceptions defined by Django.

First let’s define a model.

# models.py
from django.db import models

class User(models.Model):
   	sex_choices = ("M", "F", "O")
	
    name = models.CharField(max_length=64)
    sex = models.CharField(max_length=1, choices=SEX_CHOICES)
    age = models.PositiveIntegerField()
    email = models.EmailField()
    friend = models.ManyToManyField('self', symmetrical=False)

2.1 No record in database

In Django, the get() function is used to query a database model and return a single object (record) that meets the query criteria. The get() function will raise an exception if the query condition matches more than one object, or if no object matches the query condition. In order to facilitate the handling of the case where the query object does not exist, you can use ObjectDoesNotExist or DoesNotExist to throw an exception and then handle it.

ObjectDoesNotExist can be used for any model, however DoesNotExist can only be used for a specific model.

# views.py
from django.core.exceptions import ObjectDoesNotExist
from .models import User

def get_user(name):
	try:
        return User.objects.get(name=name)
    except User.DoesNotExists:
        return None
        
def get_object(model, name):
    try:
        return model.objects.get(name=name)
    except ObjectDoesNotExist:
        return None

2.2 Multiple records in database

Django also provides MultipleObjectsReturned exception to deal with this situation.

# views.py
from .models import User

def get_user(name):
	try:
        return User.objects.get(name=name)
    except User.DoesNotExists:
        return None
    # Note here
    except User.MultipleObjectsReturned:
        return User.objects.filter(name=name)

3 Complex query

Django supports negation, addition, subtraction, multiplication, division, modulo arithmetic, and the power operator on query expressions, using Python constants, variables, and even other expressions.

- Django Documentation

In Django, django.db.models.Q is an object used to build complex query conditions. It can be used to create logical connections between multiple conditions, such as AND and OR, allowing for more flexibility in building database queries. The Q object allows you to programmatically build complex query expressions instead of simple filter conditions. Typically, Q objects are used to create complex query conditions in filter() or exclude() functions.

In addition to logical operations, Django provides various expressions for comparison and filter operations. Some common expressions are given below.

Let’s illustrate with an example. Query records for males over the age of 18.

# views.py
from django.db.models import Q
from .models import User

# male age greater than 18
# query is an instance of object Q
query = Q(age__gt=18) & Q(sex="M")

Use the Q object as the parameter of filter() to get a QuerySet, which is a collection of indexes of all eligible objects.

# users is QuerySet, which contains references of returned objects
users = User.objects.filter(query)

for user in users:
    print(user.name)

4 Lazy evaluation

Lazy evaluation is represented by until it is needed and then calculated, and Django’s ORM also provides this feature. When we use a long list of query statements to query records in the database, Django will not execute the query operation until we actually operate the query results, and we don’t have to access the database over and over again every time we modify the query . Lazy evaluation can optimize performance by implicitly aggregating all operations into one to avoid multiple operations. Based on this mechanism, we can also divide the query statement into multiple lines, thereby increasing the readability and maintainability of the code, without worrying that doing so will affect query performance.

Let’s write a more complex example based on the example in 3 Complex query.

# views.py, not ok
from django.db.models import Q
from .models import User

users = User.objects.filter(Q(age__gt=18) & Q(sex="M")).include(name__iexact="bob").exclude(name="Bob Dylan")

for user in users:
    print(user.name)

Writing code like this is too difficult to maintain. For readability it can be written like this:

# views.py, ok
from django.db.models import Q
from .models import User

users = User
		.objects
		.filter(
			Q(age__gt=18) & 
			Q(sex="M"))
		.include(name__iexact="bob")
		.exclude(name="Bob Dylan")
        
for user in users:
    print(user.name)

It’s kind of ugly to write that. And because of the characteristics of lazy calculation, we can write like this:

# views.py, better
from django.db.models import Q
from .models import User

users = User.objects
users = filter(Q(age__gt=18) & Q(sex="M"))
users = include(name__iexact="bob")
users = exclude(name="Bob Dylan")

# Django will touch the database at this moment
for user in users:
    print(user.name)

Django will only execute the query operation when actually calling the object in the database (executing the print() statement). In this way, we can reduce the number of accesses to the database while ensuring the maintainability and readability of the code.

5 Reference

For more details, please refer to the Django documentation.

1. Model Field Reference

2. Query Expressions