Django ORM Queries: A Practical Guide with Real-World Examples
Introduction
Have you ever thought about how the data can be fetched from the database in a Django application without writing SQL queries each time?
Let’s assume that you want to create a student management system. You have to show all students, get the particular student by ID, update marks for some student, and delete one record. For each of these tasks, you might need to write SQL queries.
But there is an easier way to work with databases in Django application.
The ORM(Object-Relational Mapping) feature of Django enables us to interact with databases using Python instead of writing SQL queries every time.
In this blog, you will learn the most helpful queries for working with Django ORM explained with easy-to-understand examples.
What Is Django ORM?
ORM in Django is an integral part of Django framework which creates relation between Python object and database tables.
Instead of coding SQL queries like:
SELECT * FROM students;
We code:
Student.objects.all()
In both cases, it is about the same idea of fetching all records of students. In this instance, Django ORM will translate the python query into SQL and execute it on the database selected.
SQL code depends on the database used and the particular query running.
Let's Create a Model First
Before implementing the ORM queries, we first require the model. In this case, for illustration, let's make a very simple student management system.
Add the below code to the models.py file of your Django app:
from django.db import models
class Student(models.Model):
name = models.CharField(max_length=100)
age = models.IntegerField()
city = models.CharField(max_length=100)
marks = models.FloatField()
course = models.CharField(max_length=100)
def __str__(self):
return self.name
In this case, Student is the model and Django will use it to define the structure of the student table. Then execute the following command to implement changes to the model:
python manage.py makemigrations
python manage.py migrate
Let's move on to the ORM queries that you will use frequently in real projects.
1. Retrieve All Records
Let’s say you want to display all the students enrolled in your application. students = Student.objects.all() for student in students: print(student.name, student.marks)
The all() function fetches a QuerySet having all the matching records for the particular model. Real-world scenario: Display all the students in a student management dashboard.
2. Retrieve a Single Record Using get()
How about if you would like to search for some specific student based on his/her id?
student = Student.objects.get(id=1)
print(student.name)
print(student.marks)
This method get() will return one record.
Do note that it will raise error Student.DoesNotExist if there is no record found and Student.MultipleObjectsReturned if there are multiple records found.
In case of non-unique field, make sure there is only one record in your query.
3. Filter Records Using filter()
Imagine you want to display only students who live in Pune.
students = Student.objects.filter(city="Pune")
for student in students:
print(student.name)
Filtering will return the QuerySet of those students who have matched the condition.
An empty QuerySet will be returned if there are no results that match the filters.
You can also combine conditions:
Combination of multiple conditions is also possible:
students = Student.objects.filter(
This query finds students from Pune who scored at least 75 marks.
This code returns all the students who come from Pune and have secured a minimum of 75 marks.
4. Exclude Records Using exclude()
However, there are cases when you require all records except the ones which meet some criteria. Consider an example where you require to show all students who don’t belong to the city of Mumbai.
students = Student.objects.exclude(city=”Mumbai”)
It will return all students whose city is other than Mumbai.
Further, you may exclude students who have marks less than 40 as follows:
students = Student.objects.exclude(marks__lt=40)
Examples: Inactive users, canceled orders, obsolete items.
5. Understand Django Field Lookups
Django offers field lookups for making filtering operations more flexible.
Lookup Meaning Example
exact Exact match name__exact="Rahul"
iexact Case-insensitive exact match name__iexact="rahul"
contains Contains the specified text name__contains="Rah"
icontains Case-insensitive contains name__icontains="rah"
gt Greater than marks__gt=75
gte Greater than or equal to marks__gte=75
lt Less than marks__lt=40
lte Less than or equal to marks__lte=40
in Matches any value in a collection city__in=["Pune", "Mumbai"]
range Within a range of values marks__range=(60, 90)
startswithStarts with the specified text name__startswith="A"
isnull Checks whether the field is NULL some_field__isnull=True
For instance:
students = Student.objects.filter(marks__range=(60, 90))
The above line will retrieve those students whose marks lie between 60 and 90.
6. Sort Records Using order_by()
Assume we need to show students sorted in descending order of marks.
students = Student.objects.order_by("-marks")
In this case, "-" indicates that the results should be in descending order.
students = Student.objects.order_by("city", "-marks")
We sort students by their city names in ascending order first, followed by descending order of marks.
Real-world Example: Showing top-performing students or latest orders.
7. Retrieve Only Required Fields Using values()
However, there are cases where all fields of the database record are not required. In some cases, only the names and marks of the students are needed.
students = Student.objects.values("name", "marks")
Here, you will get a QuerySet of dictionaries and not full instances of the model. For instance,
[
{"name":"Rahul","marks":85},
{"name":"Priya","marks":92}
]
These are just examples.
For getting a list of values from a single field, you can make use of values_list().
If you want to fetch a list of values from a specific field, you can do that by using values_list():
names = Student.objects.values_list("name", flat=True)
lists. 8. Count Records Using count()
Now if you wish to find out the number of students registered.
total_students = Student.objects.count()
print(total_students)
Or even count the number of students that have got at least 75 marks.
high_scorers = Student.objects.filter(
marks__gte=75
).count()
The use of the count() function is generally more efficient compared to loading objects and counting them through Python.
9. Finding Maximum and Minimum Value
There are built-in functions in Django ORM such as max, min, avg, and sum. from django.db.models import Max, Min, Avg, Sum
result = Student.objects.aggregate(
max_marks=Max("marks"),
min_marks=Min("marks"),
avg_marks=Avg("marks"),
sum_marks=Sum("marks")
)
print(result)
The output will be a dictionary with the results.
For example, you can use Avg("marks") to find the average marks of all the students. To calculate the average marks of a specific course:
result = Student.objects.filter(
course="Python"
).aggregate(avg_marks=Avg("marks"))
Real world usage: Average salary, total sales, maximum order amount, and performance stats.
10. Create a New Record Using create()
Imagine you have a new student enrolled at your institute.
Now, you can add this record in the database through a single ORM statement:
Student.objects.create(
name='Amit',
age=22,
city='Pune',
marks=88,
course='Python'
)
Django will create and save the new student record for you.
There is also another way to do it:
student = Student(
name='Neha',
age=21,
city='Mumbai',
marks=91,
course='Data Science'
)
student.save()
These two methods are fine. Use create() if you want to create and save in one method call.
11. Update Records Using update()
Let us suppose that there is a need for correcting the marks of a particular student.
Student.objects.filter(id=1).update(marks=95)
You can also update multiple students at one time as follows:
Student.objects.filter(
city="Pune"
).update(course="Advanced Python")
However, please note that the function update will affect all those records that match the filter.
Another thing worth mentioning is that the update() function of the QuerySet directly interacts with the database and not with the individual save() of each model instance.
12. Delete Records Using delete()
If you have to delete a student entry, for instance.
Student.objects.filter(id=1).delete()
It will delete those entries that match your filter and give you information about deletions. It is important to note that you should always check your filter because there is a possibility that you can delete extra entries. For business use, there is an option of soft-deletion.
13. Use get_or_create() to Avoid Duplicate Creation
Now assume that the application is required to create the record for registering a particular student. First of all, the application should ensure that there is no such record already available.
student, created = Student.objects.get_or_create(
name="Rahul",
age=22,
defaults={
"city": "Pune",
"marks": 80,
"course": "Python"
}
)
if created:
print("New student created")
else:
print("Matching student already exists")
In case a record is found, Django will return that record. In case no such record is found, it will create one using the lookup fields as well as the default value fields.
14. Use first() and last()
However, there are cases where all you require is the first or the last object in an ordered QuerySet. For instance:
top_student = Student.objects.order_by("-marks").first()
if top_student:
print(top_student.name, top_student.marks)
Here you get the student who scored the maximum marks, according to the sorting mentioned above. Furthermore,
student = Student.objects.order_by("id").last()
If there are no objects in the database, the functions first() and last() will return None.
15. Limit Records Using Slicing
If you wish to retrieve only the first five students.
students = Student.objects.all()[:5]
For the next five students:
students = Student.objects.all()[5:10]
Consistency can be maintained with sorting before slicing.
For bigger projects, pagination in Django is helpful.
16. Use Q Objects for OR Conditions
Default behavior of the filter() method is that the combination of multiple keyword arguments in it uses AND operator. However, if there are students from Pune or having marks greater than 90, we can use Q object in this way:
from django.db.models import Q
students = Student.objects.filter(
Q(city="Pune") | Q(marks__gt=90)
)
We can use & operator in case of AND condition and ~ for negation.
Example:
students = Student.objects.filter(
Q(city="Pune") & Q(marks__gte=75)
17. Use F Expressions for Database-Side Calculations Let us consider that we wish to add 5 marks to all students.
We don't have to get each object, change its Python value, and save it back individually. We can simply use F.
from django.db.models import F
Student.objects.update(marks=F("marks") + 5)
The operation will be performed at the database side.
However, we have to be careful while updating limited or business fields like marks should not go above 100.
F expressions are used for inventory updates and counter calculations.
18. Group Data Using annotate()
Imagine you are dealing with a database where there is information about students who are taking classes in various subjects.
from django.db.models import Count
course_info = Student.objects.values(
"course"
).annotate(
student_count=Count("id")
).order_by("course")
The results will have one item per course together with the number of students attending it. For instance:
[
{"course": "Python", "student_count": 15},
{"course": "Data Science", "student_count": 10}
]
It’s an example.
annotate() is helpful for grouping items and calculating their values, while aggregate() is for calculating summary values for a QuerySet.
19. Work with Related Models Using select_related()
In practical applications, you will have more than one table in your database that are related.
In this scenario, the student will belong to a department in the database. This connection between both can be established through the ForeignKey relationship in Django.
class Department(models.Model):
name = models.CharField(max_length=100)
class Student(models.Model):
name = models.CharField(max_length=100)
department = models.ForeignKey(
Department,
on_delete=models.CASCADE,
related_name="students"
)
Assuming you want to display the names of all students together with the name of their departments.
students = Student.objects.select_related("department")
for student in students:
print(student.name, student.department.name)
The select_related() function enables the retrieval of related objects in case of the foreign key relation or one-to-one relation through SQL join.
20. Work with Reverse and Many-to-Many Relationships Using prefetch_related()
If one student is permitted to take many courses, and one course can contain many students taking it,
the many-to-many field will be something like the following:
class Course(models.Model):
name = models.CharField(max_length=100)
class Student(models.Model):
name = models.CharField(max_length=100)
courses = models.ManyToManyField(
Course, related_name="students"
)
For fetching students with their courses in an efficient manner:
students = Student.objects.prefetch_related("courses")
for student in students:
print(student.name)
for course in student.courses.all():
print(course.name)
The prefetch_related() function, on the other hand, creates separate queries and later combines them in Python.
This function performs better when used with many-to-many fields and reverse foreign key fields.
The main difference is that select_related() makes SQL joins when dealing with single-valued
relationships.
Best Practices for Django ORM Queries
Creating queries through ORM is easy but creating efficient queries is an important skill for professional Django developers.
The below are some of the tips which should be followed:
1. Do not repeat your queries: Use select_related() and prefetch_related() functions when necessary.
2. Do not make queries for unnecessary things: Use values() and values_list() function where you don't need model instances
3. Make use of calculations on the database side: Use Count, Avg, Sum, and F expressions where applicable.
4. Indexing: Create indexes to fields which are queried often.
5. Do not make queries for all: Use pagination and limit your queries.sets.
6. Check the generated SQL: Use queryset.query to check generated SQL in many QuerySets, or Django Debug Toolbar and database query logging for performance issues.
7. Properly handle errors: Consider missing records and possible duplicates when making use of get().
8. Use transactions when necessary: Use transaction.atomic() where multiple database operations should either all succeed or all fail.
It should be noted that ORM queries are not necessarily efficient queries as they are in Python.
Conclusion
Django ORM facilitates database operation in the form of querying through Python objects, rather than writing SQL statements for every database operation.
Whether it is fetching records or filtering records, updating, aggregating, grouping, or handling relations, ORM queries are an important component of Django programming.
For beginners who are new to Django, it is advisable to practice all(), get(), filter(), exclude(), create(), update(), and delete() first. When you feel more comfortable with the basics, you can move onto practicing with Q objects, F expressions, aggregations, and query optimization.
The only way to learn Django ORM is through a mini project in which you can write queries to achieve real-world requirement and see how Django converts them to SQL.
Practicing regularly, you will soon find database operation one of the easiest aspects of your Django programming skillset.
Author:
Dhanaji Kokare
Related Links:
Resume Tips For Software Developers
Do visit our channel to know more: SevenMentor
Dhanaji Kokare
Expert trainer and consultant at SevenMentor with years of industry experience. Passionate about sharing knowledge and empowering the next generation of tech leaders.