ORM in Django

ORM refers to object-relational mapping. It lets you interact with the database using Python, like you do it with SQL. It is primarily designed for relational databases like PostgreSQL, MySQL, Oracle, SQLite, etc.

WHY DO WE EVEN NEED ORM?

• Using ORM is a choice, but without it , in order to perform the database operations, it would be necessary to have the understanding of SQL, which in turn would affect the development speed.
• Suppose there’s a requirement to migrate the database from MySQL to PostgreSQL then all the queries would have to be rewritten to follow the syntax of PostgreSQL.
• It would be difficult to parse the results returned by the SQL query, as the results would be database objects and not Python objects.
• With the help of ORM, we can perform all the database operations by writing only python code, no need of knowing SQL
• Even if database migration happens, it would not affect how the queries are written or executed.

HOW DOES ORM WORK?

The above diagram gives a clear representation of the flow of a query from our application to the database and back to our application.

If you have connected your Django application to a PostgreSQL database, you might remember installing a package named psycopg2. Psycopg2 is the adaptor or driver that forms a connection between the database and the application. A query written in Python is given to Django’s ORM, which then converts this query into an abstract representation, and then it is given to the adaptor or driver, which converts the Python query into a raw SQL query. This query then gets executed, and the results of the query are returned by the adaptor or driver to the ORM.

In Django, we define models that map to the tables in the database.

WRITING ORM QUERIES:

For people who are familiar with SQL, it would be easy for them to relate to the ORM queries. Even if you are not familiar with SQL, it is fine, as the purpose of ORM itself is to remove the complexity of knowing SQL. Django utilizes the QuerySet API to retrieve the records. A query set is basically a list of objects of the model. For example, consider that there is an emp table in the database that stores the records of the employees. This table is represented by the Emp model in our application. Let’s write some queries to understand things in a better manner, I’ll also be attaching the output of the code wherever it is necessary.

1. Fetching all the data from the

• Example of SQL query: select * from emp;

Emp.objects.all()

Output: <QuerySet [<Emp: Emp object (1)>, <Emp: Emp object (2)>]>

2. Retrieving only specific fields andunpacking the data in the query set

# .values() fetches all the fields of the model for each object 
# and displays in a dictionary format 

Emp.objects.values()

Output: <QuerySet [
  {'id': 1, 'name': 'John , 'salary': Decimal('50000.00'),'age': 30}, 
  {'id': 2, 'name': 'Jane Smith','salary': decimal (60000.00'), 'age': 28}
]>

• Example of SQL query: select name, age from emp;

# fetches only the name and age fields for each object 
Emp.objects.values('name','age')

Output: <QuerySet [{'name': 'John Doe', 'age': 30}, 
{'name': 'Jane Smith', 'age': 28}]>

• .values() makes the data present in the query set more readable

3. Displaying only selected fields as a list of tuples

• values_list() returns a list of tuples, wherein each tuple represents a record. Specific fields can also be provided to values_list().

Emp.objects.all().values_list("name","age")

Output: <QuerySet [('John Doe', 30), ('Jane Smith', 28)]>

4. Fetch values of a particular field in a list

Emp.objects.all().values_list("name",flat=True)

Output: <QuerySet ['John Doe', 'Jane Smith']>

• flat = True would only work when only one field is specified in the values_list. In the above example, it puts all the values for the name field in a list

5. Fetching distinct values

• Example of SQL query: select distinct age, name from emp;

Emp.objects.distinct('age').values()

# query will return unique combinations of name and age from the table 
Emp.objects.distinct("age","name").values()

6. Filtering records

• In SQL, records are filtered based on conditions using the where clause
• Example of SQL query: select * from emp where id=1;

a. get()

• .get() is used when it is certain that only one record is going to be returned from the query
• If no record or multiple records are returned, then it raises an exception

# fetch employee having id = 1
e = Emp.objects.get(id=1)
print(e.name)

Output: 'John Doe'

b. filter()

• .filter() is used when more than one record is going to be returned
• It doesn’t raise an exception when no objects are returned rather, it just returns an empty query set

# fetch all employees having age of 28
Emp.objects.filter(age=28).values()

Output: <QuerySet [
{'id': 2, 'name': 'Jane Smith','salary': Decimal('60000.00'), 'age': 28, 'date_of_birth': datetime.date(1995, 2, 20)}, 
{'id': 10, 'name': 'Ava Martinez','salary': decimal (60000.00'), 'age': 28, 'date_of_birth': datetime.date(1995, 4, 8)}]>

# fetch only the first object of the query set 
Emp.objects.filter(age=28).first()

# fetch the last object of the query set
Emp.objects.filter(age=28).last()

7. Arithmetic operators

• Example of SQL query: select *, age + 2 as increased_age from emp;

# increase the age of the employees by 2 

Emp.objects.annotate(increased_age=F('age')+2).values()

Output: <QuerySet [
{ 
  'id': 1, 'name': 'John Doe', 'salary': Decimal('50000.00'), 'age': 30, 
  'date_of_birth': datetime.date(1993, 5, 15), 'increased_age': 32
},
{
  'id': 2, 'name': 'Jane Smith', 'salary': Decimal('60000.00'), 'age': 28, 
  'date_of_birth': datetime.date(1995, 2, 20), 'increased_age': 30
}
]>

• Let’s break down the things written in the query above, increased_age is basically a custom field
• annotate will apply or add this custom field to each object returned in the query
• To increase the age by 2, we would need to reference the existing age field and that can be done using the F() object
• In a similar manner, other arithmetic operations can also be performed

8. Logical operators

• Q() object in django is used for writing complex queries by adding multiple conditions
• Example of SQL query:
  AND: select * from emp where name = ‘Ava Martinez’ and age = 28;
  OR: select * from emp where name = ‘Ava Martinez’ or age = 28;
  NOT: select * from emp where age <> 28;

# 1. AND (2 ways)
Emp.objects.filter(age=28,name="Ava Martinez")
Emp.objects.filter(Q(age=28) & Q(name="Ava Martinez"))

# 2. OR 
Emp.objects.filter(Q(age=28) | Q(name="Ava Martinez"))

# 3. NOT
Emp.objects.filter(~Q(age=28))

9. Comparison operators

• Example of SQL query: select * from emp where age > 30;

# GREATER THAN 
Emp.objects.filter(age__gt=30)

# GREATER THAN EQUAL TO
Emp.objects.filter(age__gte=30)

# LESS THAN 
Emp.objects.filter(age__lt=30)

# LESS THAN EQUAL TO 
Emp.objects.filter(age__lte=30)

10. Range searching

• Example of SQL query:
  BETWEEN: select * from emp where age between 1 and 3;
  NOT BETWEEN: select * from emp where age not between 1 and 3;

# filter records that have an ID in the range of 1 to 3
Emp.objects.filter(id__range=(1,3))

# filter records that do not have an ID in the range of 1 to 3 
Emp.objects.filter(~Q(id__range=(1,3)))

11. Containment operators

• Example of SQL query: select * from emp where age in (25,28,30);

# IN OPERATOR
# fetch objects that have age value 25, 28, or 30
Emp.objects.filter(age__in=[25,28,30])

# NOT IN OPERATOR 
# fetch objects that do not have age value 25, 28, 30
Emp.objects.exclude(age__in=[25,28,30])

12. Pattern matching operators and filtering by null values

• pattern matching queries are written in SQL by using the ‘like’ and ‘not like’ operator and also the wild card characters _ and %
• _ represents a single character
• % represents more than one character
• Example of SQL query: select * from emp where name like ‘%doe%’;
• query above will return rows where name contains ‘doe’

# STARTS WITH CASE SENSITIVE
Emp.objects.filter(name__startswith="J")

# STARTS WITH CASE INSENSITIVE
# i ignores casing
Emp.objects.filter(name__istartswith="j")

# ENDS WITH CASE SENSITIVE
Emp.objects.filter(name__endswith="e")

# CONTAINS 
# checks for 'doe' in name irrespective of the case
Emp.objects.filter(name__icontains='doe')

# IS NULL
Emp.objects.filter(age__isnull=True)

# IS NOT NULL
Emp.objects.filter(age__isnull=False)

13. Aggregations

• Aggregations are used to perform calculations on a set of data. It basically groups all the values of a particular column or field and returns a single result
• Examples where aggregations are helpful is when total count of employees, max, min, or average of the salary needs to be calculated
• Example of SQL query: select min (salary) from emp;

# SUM
Emp.objects.aggregate(sum_sal = Sum('salary'))
Output:'sum_sal': 695000.0}

# COUNT
# COUNT ignores null values 
Emp.objects.aggregate(total_row = Count('salary'))
Output: {'total_row': 11}

# AVG
Emp.objects.aggregate(avg_sal = Avg('salary'))
Output: {'avg_sal': 63181.818181818184}

# MIN
Emp.objects.aggregate(min_sal = Min('salary'))
Output: {'min_sal': 45000.0}

# MAX
Emp.objects.aggregate(max_sal = Max('salary'))
Output:  {'max_sal': 80000.0}

14. GROUP BY

• In ORM, we can perform the SQL group by operation by specifying the field on which the group operation is to be performed in the .values()
• Example of SQL query:
  select department, count (department) as no_of_employees from emp group by department;

# Group all employees by department and get the count of the people 
# present in the department 
Emp.objects.values("department").annotate(no_of_employees=Count('department'))

Output: <QuerySet [
{'department': 30, 'count': 3}, 
{'department': 10, 'count': 4}, 
{'department': 20, 'count': 4}]>

15. ORDER BY

• Example of SQL query: select * from emp order by age asc/desc;

# ASCENDING
Emp.objects.order_by("age").values("age")
Output: <QuerySet [{'age': 26}, {'age': 27}, {'age': 28}]>

# DESCENDING
Emp.objects.order_by("-age").values("age")
Output: <QuerySet [{'age': None}, {'age': 40}, {'age': 35}, {'age': 32}]>

# ORDER BY ON MORE THAN ONE COLUMN
# objects will be ordered by age if same age is found 
# then further ordering will happen by ID.
Emp.objects.order_by("-age","id").values("age","id")
Output: <QuerySet [
{'age': 29, 'id': 6}, 
{'age': 28, 'id': 2}, 
{'age': 28, 'id': 10}]>

16. JOINS

ACCESSING PARENT PROPERTIES TO FILTER CHILD TABLE

• Consider a parent-child relationship between the car table (child) and the manufacturer table (parent).
• Manufacturer table fields: id (primary key), name
  Car table fields: id, name, manufacturer (foreign key)
• Let’s look at SQL and ORM query for fetching data from car table where car name is ‘toyota’ and the manufacturer’s name is ‘vanshita’
• SQL:
  select * from car c join manufacturer m on c.manufacturer_id = m.id where m.name = ‘vanshita’ ;

Car.objects.filter(manufacturer__name='vanshita')

• in the above example, we can filter items from child table by accessing the parent properties

ACCESSING CHILD PROPERTIES TO FILTER THE PARENT TABLE

• Here, we’ll just do the reverse of what we did previously

# fetch all the manufacturer objects that have name 'vanshita' and the car
Manufacturer.objects.filter(name=’vanshita’, car__name=’toyota’)

• in the above example, we first mention the child table name in lowercase and then the field name

OPTIMISE JOINS USING SELECT RELATED

• Generally, when we retrieve data from the child table, we can access the parent field data as well, as there is a relationship between the two tables

car_qs = Car.objects.all()
for car in car_qs:
  print(car.manufacturer.name)

• There is an optimization problem with the approach given above. Suppose the query set has 10 objects, so for printing the manufacturer name corresponding to a car, a separate query will be executed each time on the manufacturer table to fetch the manufacturer name corresponding to the car. This increases the time complexity
• select_related performs the same thing in an optimized manner. It performs a join on both tables and fetches related data at once

# performs a join 
car_qs = Car.objects.select_related(‘manufacturer’).all()

for car in car_qs:
  print(car.manufacturer.name)

That’s all I’ve got for this topic, there’s surely a lot more to explore in Django ORM. Throughout this blog, we touched on some of the basics of ORM and how we can write Python queries to interact with the database. As you build your Django projects, remember that ORM is a trusted companion in the world of databases and data management.

Happy coding!

In Plain English

Thank you for being a part of our community! Before you go:

• Be sure to clap and follow the writer! 👏
• You can find even more content at PlainEnglish.io 🚀
• Sign up for our free weekly newsletter. 🗞️
• Follow us on Twitter(X), LinkedIn, YouTube, and Discord.