Commonly used SQL queries using Django ORM

ORM stands for Object Relation Mapper. Django ORM is a powerful and elegant way to interact with the database. The Django ORM is an abstraction layer that allows us to play with the database. In the end, Django ORM will convert all operations into SQL statements. In this piece, We will learn ORM of some common SQL queries.

Sample Models

Let’s define the sample model User.

from django.db import models# Create your models here.

class User(models.Model):

first_name = models.CharField(max_length=100)

last_name = models.CharField(max_length=100)

age = models.IntegerField()

city = models.CharField(max_length=255)

I’ve created a few objects of the User table. Let’s start with commonly used Django ORMs.

Data in the User tableid | first_name | last_name | age | city

1 | sample | 1 | 20 | city1

2 | sample | 2 | 25 | city2

3 | sample | 3 | 30 | city3

Common Django ORMsFilter NULL values

‘__isnull’ is used to filter the null values in the Django ORM. It accepts True or False.

>>> User.objects.filter(age__isnull=True).values('id','age')

<QuerySet []>>>> User.objects.filter(age__isnull=False).values('id','age')

exists()

The exists() method is used to check the result of the query. Returns True if the queryset contains any results, and False if not.

>>> User.objects.filter(age__isnull=True).values('id','age').exists()

False>>> User.objects.filter(age__isnull=False).values('id','age').exists()

True

SQL ‘LIKE’ with Django ORM>>> User.objects.filter(city__contains='city').values('id','city')

In the above query, __contains is used to search ‘city’ substring in the city column. Also, we can check the exact match using __exact.

>>> User.objects.filter(city__exact='city').values('id','city')

‘__startswith’ to check the start of the string.

‘__endswith’ to check the end of the string.

>>> User.objects.filter(city__startswith='city').values('id','city')

<QuerySet [{'id': 1, 'city': 'city1'}, {'id': 2, 'city': 'city2'}, {'id': 3, 'city': 'city3'}]>>>> User.objects.filter(city__startswith='ity').values('id','city')

<QuerySet []>>>> User.objects.filter(city__endswith='2').values('id','city')

Relational operators

gt -Greater than.

gte -Greater than or equal to.

lt -Less than.

lte -Less than or equal to.

>>> User.objects.filter(age__gt=20).values('id','age')

<QuerySet [{'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>>>> User.objects.filter(age__gte=20).values('id','age')

<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>>>> User.objects.filter(age__lt=25).values('id','age')

<QuerySet [{'id': 1, 'age': 20}]>>>> User.objects.filter(age__lte=25).values('id','age')

Select a few columns of the table

In Django ORM values() method is used to select a few column values of the table.

>>> User.objects.values('id')

<QuerySet [{'id': 1}, {'id': 2}, {'id': 3}]>>>> User.objects.values('id','first_name','last_name')<QuerySet [{'id': 1, 'first_name': 'sample', 'last_name': '1'}, {'id': 2, 'first_name': 'sample', 'last_name': '2'}, {'id': 3, 'first_name': 'sample', 'last_name': '3'}]>

SQL ‘IN’ with Django ORM

‘__in’ is used to filter on multiple values.

>>> User.objects.filter(id__in=[1,2])

exclude()

Excludes objects from the queryset which match with the lookup parameters.

>>> User.objects.exclude(id=1)

Rename objects like ‘As’ in the SQL

The extra() method is used to rename columns in the ORM.

>>> User.objects.extra(select={'FirstName':'first_name','LastName':'last_name'}).values('FirstName','LastName')<QuerySet [{'FirstName': 'sample', 'LastName': '1'}, {'FirstName': 'sample', 'LastName': '2'}, {'FirstName': 'sample', 'LastName': '3'}]>

In this ORM, I’ve renamed first_name to FirstName and last_name to LastName.

Group By and Order By

The aggregate() function is used to perform aggregation operations like sum, average, min, max, etc.

>>> User.objects.aggregate(Sum('age'))

{'age__sum': 75}>>> User.objects.aggregate(Avg('age'))

{'age__avg': 25.0}>>> User.objects.aggregate(Max('age'))

{'age__max': 30}>>> User.objects.aggregate(Min('age'))

{'age__min': 20}

The aggregate() function works on the whole dataset only. Use annotate() instead of aggregate() if you want an average age group by city.

>>> User.objects.values('city').annotate(Sum('age'))

Also, we can sort a queryset using order_by().

>>> User.objects.values('city').annotate(total_age=Sum('age')).order_by('-total_age')

We can rename the default name of the aggregation function output. Here, I’ve changed name total_age and used order_by on total_age. ‘-total_age’ is used for descending order.

Usually, in the database, we use the ‘HAVING’ clause with the group by queries. In the Django, we can use filter() function.

>>> User.objects.values('city').annotate(total_age=Sum('age')).filter(total_age__gt=20).order_by('-total_age')

In the output, city1 with a total_age 20 does not exist because of the filter.

Complex filters with Q objects

Q objects are used for AND, OR and NOT operations. Q objects provide complete control over the where clause of the query.

AND

‘&’ is used for AND operation between 2 Q expressions.

>>> User.objects.filter(Q(city='city1') & Q(age=25)).values('id','city','age')

<QuerySet []>>>> User.objects.filter(Q(city='city1') & Q(age=20)).values('id','city','age')

This ORM will find all users with city=city1 and age= 25.

‘|’ is used for OR operation between 2 Q expressions.

>>> User.objects.filter(Q(city='city1') | Q(age=25)).values('id','city','age')

This ORM will find all users having city=city1 or age= 25.

NOT

‘~’ is used for NOT operation with Q expression.

>>> User.objects.filter(Q(city='city1') & ~Q(age=20)).values('id','city','age')

<QuerySet []>>>> User.objects.filter(Q(city='city2') & ~Q(age=20)).values('id','city','age')

The query will find all users with city=city2 and age is other than 20.

F() Expressions

In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.

>>> User.objects.annotate(new_age=F('age')*2).values('id','age','new_age')

We can also use F() expression in the filter.

>>> User.objects.filter(id__lt=F('age')).values('id','age')

<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>>>> User.objects.filter(id__gt=F('age')).values('id','age')

Update values

Performs an SQL update query for the specified fields, and returns the number of rows matched (which may not be equal to the number of rows updated if some rows already have the new value).

For example, multiple age column value with 2.

>>> User.objects.update(age=F('age')*2)

>>> User.objects.values('id','age')

We can update a single object also.

>>> user=User.objects.get(id=1)

>>> user.age =100

>>> user.save()

Delete object

Performs an SQL delete query on all rows in the queryset and returns the number of objects deleted and a dictionary with the number of deletions per object type.

Delete a single object

>>> user=User.objects.get(id=1)

>>> user.delete()

(1, {'django_orms.User': 1})

Delete multiple objects

>>> User.objects.all().delete()

(2, {'django_orms.User': 2})

By default, Django’s ForeignKey emulates the SQL constraint ON DELETE CASCADE — in other words, any objects with foreign keys pointing at the objects to be deleted will be deleted along with them. We can set the on_delete behavior in the models.

Well, this is it! I hope you liked it.

Thanks for reading this article. If you like it, click on ???? to rate it out of 50 and also share it with your friends. It means a lot to me.

Would you like to check out my other articles?

How to create custom migrations in Django

Recently, I stuck in a problem related to a database schema. What is the problem? Let’s understand the problem first.

medium.com

Using Python Pandas With Excel

Performing basic Excel operations with Python libraries

medium.com

428