Introduction

Applications that constitute a database allow users to interact with the application’s data through Object Relational Mappers (ORMs). ORMs are the intermediaries between an application and a database. They provide a set of functionality that enable CRUD database operations. Many server-side programming frameworks like Python’s Django provide ORMs that allow developers to write database queries in the pure server-side language. The ORM then performs a database operation in a language understood by the database type in use. This article discusses the Django ORM, including the functionalities that the ORM provides to facilitate database interactions.

A Brief Overview

When building a backend application using Django, you would typically install Django in a virtual environment. Django installs a SQLite database by default, which comes with the Django ORM to allow database operations. You may have interacted with the Django ORM functionality without noticing. For instance, every time you have used .get() or .filter() methods somewhere in your views.py file, you were using Django ORM methods to query the database.

Django ORM methods can broadly be classified into two types:

• Those that interact with a queryset
• Those that interact with a model object

In simple terms, a queryset is single or several database records. A database record is basically a data row in a database. Therefore, queryset methods act on database records. A model object is an instance, or a definition if you may, of a database record. A model object provides information about a database record. That is, what fields does the database record have? For instance, a database record could have ID, name, URL, image or Foreign Key fields. Django ORM has methods that can manipulate these database record fields.

Django ORM Methods

This section discusses the most commonly used Django ORM methods. The methods are grouped based on related functionality. That is, the methods that perform a similar functions are discussed together. Note that based on the broad classification discussed above, methods within the same category could interact with either a queryset or a model object.

Filtering Methods

Filtering methods return querysets depending on the lookup parameters passed to the methods.

.filter(**kwargs)

.filter() returns a queryset that matches the lookup parameters passed to the method. For instance, assume you are a querying a database field, name, for all database records containing the name bob. In this case you would need to filter your database by bob as a keyword argument. Django ORM provides a magic method, __icontains, that attaches to a database field, which is useful for string and substring matching. You would implement this operation like this:

from models import YourModel
bob_records = YourModel.objects.filter(name__icontains="bob")

Another common example where you would use .filter() is when you have implemented user accounts in your Django application. You may want to restrict each user to their data. In this case, if a user requests some data, you want to show them only the data that belongs to them. You would typically use .filter() method to avail data by the user making the request:

your_data = YourModel.objects.filter(owner=request.user)

Assuming in your data model you have a field called owner that associates data and users

.excludes(**kwargs):

Works the same way as .filter() but returns database records that do not match the lookup parameters.

.get(**kwargs):

This method is among the most commonly used. Like .filter(), the method is used to retrieve the data that matches the lookup parameters passed to it as keyword arguments. However, .get() returns only a single database record while .filter() returns one or more records. .get() raises a MultipleObjectsReturned error or DoesNotExist error exception if multiple objects are found or no object is found, respectively. Below is an example of how you would use .get()

data = YourModel.objects.get(id=1)

This queryset returns a database record with an id 1

.first()

Returns the first object matched by the queryset or None if the queryset is empty.

.last():

Returns the last object matched by the queryset or None if the queryset is empty.
.first() and .last() are usually used with .filter() incase one needs only the first or the last item returned by .filter():

bob_records = YourModel.objects.filter(name__icontains="bob", owner=request.user).first()

Aggregation Methods

Aggregation methods perform arithmetic calculations on querysets. They include:

aggregate(*args, **kwargs):

Performs aggregate calculations (e.g., Count, Sum, Avg, etc.) on the queryset

.count()

returns the number of objects in a queryset

.exists()

Evaluates to true if a queryset contains any results, else false. This method could be combined with .filter() in a real-world use case. Assume you want to add a record to the database, but only if no similar record already exists. Say you want to add a name bob to a database field name:

If not YourModel.objects.filter(name="bob").exists():
  model_instance = YourModel(name="bob")

Ordering Methods

Ordering methods sort a queryset based on declared model fields.
Assume you have this model

class Names(models.Model):
    Name = models.CharField(max_length=200)
    date_added = models.DateTimeField(auto_now_add=True)

Names.objects.order_by('date_added'): returns a queryset based on the date they were added to the database. The first added entry is returned first. Assuming you want to return a queryset based on the most recently added record, you would reverse the order like this:

Names.objects.order_by('-date_added'). # The '-' sign indicates 'reverse'

Alternatively, Django ORM provides a .reverse() method for reversing the order of querysets:

Names.objects.order_by('date_added').reverse()

Alternatively, you could sort the entries of the above model like this:

class Meta:
    Ordering = ['date_added']

In this case, you provide a Meta class inside your model class and define the desired ordering using the attribute ordering

Update and Delete

Methods for updating or deleting objects in the database.
.update(**kwargs): Updates all objects(fields) in the queryset with the given values.

YourModel.objects.get(id=1).update(name="bob")

The above query updates the model field "name" with value "bob"
You can update more than one field with .update()

YourModel.objects.get(id=1).update(name=”bob”, gender=”male”)

.delete(): Deletes all objects in the queryset.

YourModel.objects.get(id=1).delete()

The above query deletes a database record with id 1. Note that while .update() can act on a part of a database record(a field), .delete() acts on the entire record(all fields);.delete() erases the entire record at once from the database.

Other Methods

.values(*fields, **expressions): Returns a queryset that returns dictionaries instead of model instances, with values according to the specified fields and expressions.

.values_list(*fields, flat=False): Similar to values() but returns tuples instead of dictionaries.

.values() and .value_list() are helpful when you want to return specific database fields while excluding others.
Assume you have this model:

from django.db import models
from django.contrib.auth.models import User

# Create your models here.

class PasswordEntry(models.Model):
    """Defines password's data structure"""
    website_url = models.URLField(max_length=200)
    username = models.CharField(max_length=100)
    password = models.CharField(max_length=500)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    owner = models.ForeignKey(User, on_delete=models.CASCADE, default=1)

    class Meta:
        ordering = ['-created_at']
        verbose_name_plural = 'password entries'

    def __str__(self):
        """Returns a string rep of password entry"""
        return f"{self.username}-{self.website_url}-{self.password}"

This is a password entries model that allows a user to store passwords to their various user accounts. If you are building a similar Django application, you may want to implement a functionality that allows users to search for specific passwords. In this application, you may want to display the password entries by website URL or username instead of the actual password, for security reasons

Your search functionality may look something like this. Notice how .values_list() comes in handy when you want to choose the fields to display:

def search_entry(request):
    """Searches for a password entry by site name"""
    query = request.GET.get('q')
    if query:
        entries = PasswordEntry.objects.filter(website_url__icontains=query, owner=request.user).values_list('id', 'website_url')
    else:
        entries = PasswordEntry.objects.filter(owner=request.user).values_list('id', 'website_url')
    context = {'entries': entries}
    return render(request, 'manager/search_entries.html', context)

.values_list() and .values() act on model instances instead of querysets. Attempting to apply the methods on querysets will raise an attribute error.

Conclusion

Object relational Mappers (ORMs) are a useful technology. They allow backend developers to query databases without having to write raw statements in a standard database query language like Structured Query Language (SQL). ORMs, therefore, reduce the chances of database injection attacks. The attacks can occur if users access the database directly using languages like SQL. Moreover, developers do not have to necessarily understand a query language; ORMs conduct most of the heavy lifting.

Django provides its own ORM, the Django ORM. The Django ORM avails different types of methods that enable developers to perform CRUD operations on the database. The methods perform operations such as filtering, aggregation, ordering, updating and deleting. Several errors are bound to rise if the methods are used inappropriately. This article mentions several errors such as AttributeError, MultipleObjectsReturnedError and DoesNotExistError. Nevertheless, the Django ORM is a powerful tool for executing database transactions. Try it out, and happy querying!