django-queryset-examples
django-queryset-examples
django-queryset-examples
Django QuerySet Examples
In this article, you will find the most commonly used Django QuerySet examples with SQL codes.
A QuerySet describes a collection of objects/rows from your database. At some point, QuerySets are being "converted" to SQL queries. And one of the important facts about them is they are lazy. What does it mean? They can be constructed, filtered, sliced, and passed around without actually hitting the database. And only in the listed below cases, they are being evaluated:
Iteration: A QuerySet is iterable, and it executes a database query the first time we iterate over it.
Pickling/Caching: Evaulated if we pickle or cache a QuerySet.
repr(), len(), list(): A QuerySet is being evaluated when we call repr(), len(), list() on it.
Slicing: A QuerySet is being sliced with the step parameter will hit the database.
Contents
- How to get the SQL query from the QuerySet?
- How to do AND operation?
- How to do OR operation?
- How to do a not equal filtering?
- How to do IN filtering?
- How to do IS NULL or IS NOT NULL queries?
- How to do SQL “LIKE” equivalent queries?
- How to do comparison(>, >=, <, <=) operations?
- How to do BETWEEN operation?
- How to LIMIT the number of results in queryset?
- How to ORDER the results in queryset?
- How to get a single object from queryset?
How to get the SQL query from the QuerySet
?
Sometimes, we need to get the raw SQL query that is being generated and executed behind the scenes when we evaluate the QuerySet. This could help us with the debug process and not only. The string representation of the query attribute of the QuerySet object is the SQL query that we are talking about. See the example below:
In: str(User.objects.all().query)
Out: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"'
How to do AND operation?
As an AND operation example let's limit(filter()) users whose first_name is equal to John and also make sure that they are active - is_active is true.
qs1 = User.objects.filter(first_name="John", is_active=True)
the same thing we can do by chaining multiple filters:
qs2 = User.objects.filter(first_name="John").filter(is_active=True)
alternatively, we can apply & operator on two or more QuerySets:
qs3 = User.objects.filter(first_name="John") & User.objects.filter(is_active=True)
and finally, we can use the tool Q from django.db.models.
qs4 = User.objects.filter(Q(first_name="John") & Q(is_active=True))
It's worth saying that all QuerySet objects(qs1, qs2, qs3, qs4) will generate the same SQL query in the end. The comparison below proves that:
In: str(qs1.query) == str(qs2.query) == str(qs3.query) == str(qs3.query)
Out: True
In SQL terms, they are evaluated to
SELECT "auth_user"."id",
...
"auth_user"."date_joined"
FROM "auth_user"
WHERE ("auth_user"."first_name" = John AND "auth_user"."is_active" = True)
SQL query, all without exceptions.
How to do OR operation?
Very basic example of OR operation will be filtering users whose first_name is equal to John or Jane. We can achieve this with the help of Q:
qs1 = User.objects.filter(Q(first_name="John") | Q(first_name="Jane"))
or applying | (pipe) operator on two or more querysets :
qs2 = User.objects.filter(first_name="John") | User.objects.filter(first_name="Jane")
It's true that qs1 and qs2 have identical SQL queries:
In: str(qs1.query) == str(qs2.query)
Out: True
and both produce:
SELECT "auth_user"."id",
...
"auth_user"."date_joined"
FROM "auth_user"
WHERE ("auth_user"."first_name" = John OR "auth_user"."first_name" = Jane)
SQL query.
How to do a not equal filtering?
As an example, we will select all the users except ones whose
first_name is equal to John:
qs1 = User.objects.filter(~Q(first_name="John"))
another way of doing the same query is using the exclude() method:
qs2 = User.objects.exclude(first_name="John")
As you may guess, SQL queries are identical in this case too:
In: str(qs1.query) == str(qs2.query)
Out: True
and the produced SQL query is:
SELECT "auth_user"."id",
...
"auth_user"."date_joined"
FROM "auth_user"
WHERE NOT ("auth_user"."first_name" = John)
How to do IN filtering?
A typical example could be filtering users by the given list of IDs. Below is an example with the in expression being used:
qs = User.objects.filter(pk__in=[1, 4, 7])
SELECT "auth_user"."id",
...
"auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" IN (1, 4, 7)
In some cases it might be benefical to use in_bulk() method.
in_bulk(id_list=None, field_name='pk') takes a list of field values (id_list) and the field_name for those values, and returns a dictionary mapping each value to an instance of the object with the given field value. If id_list isn’t provided, all objects in the queryset are returned.
IMPORTANT! field_name must be a unique field, and it defaults to the primary key.
Example:
In: User.objects.in_bulk([1, 4, 7])
Out: {1: < User: John >, 4: < User: Jane >, 7: < User: Jemery >}
How to do IS NULL or IS NOT NULL queries?
As an example, we will select only those users whose first_name is not specified(in other words it is NULL):
qs = User.objects.filter(first_name__isnull=True)
SELECT ...
FROM "auth_user"
WHERE "auth_user"."first_name" IS NULL
the opposite QuerySet:
qs = User.objects.filter(first_name__isnull=False)
SELECT ...
FROM "auth_user"
WHERE "auth_user"."first_name" IS NOT NULL
So, isnull takes either True or False, which correspond to SQL queries of IS NULL and IS NOT NULL, respectively.
How to do SQL “LIKE” equivalent queries?
The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
We will cover different cases one by one.
The first case will be selecting users whose first_name starts with Jo string:
qs = User.objects.filter(first_name__startswith="Jo")
SQL equivalent:
SELECT ...
FROM "auth_user"
WHERE "auth_user"."first_name"::text LIKE Jo%
now let's select those users whose last_name ends with yan string:
qs = User.objects.filter(last_name__endswith="yan")
SQL equivalent:
SELECT ...
FROM "auth_user"
WHERE "auth_user"."last_name"::text LIKE %yan
To demonstrate the last example for the % wildcard, we will select users whose last_name contains oh string:
qs = User.objects.filter(last_name__contains="oh")
SELECT ...
FROM "auth_user"
WHERE "auth_user"."last_name"::text LIKE %oh%
Unfortunatelly, there is no built-in tool for _(underscore wildcard). But, there is an alternative solution with the use of the regex lookup tool.
As an example, we will filter users whose last_name matches D_e patterns, which means that it should start with the letter D then follow with any single character and end with the letter e:
qs = User.objects.filter(last_name__regex=r"^D.e$")
SELECT ...
FROM "auth_user"
WHERE "auth_user"."last_name"::text ~ ^D.e$
As you can see, it's being converted to a regular expression syntax that is defined in the database backend.
All the tools in this section also support case insensitive lookups. Check startswith, istartswith, endswith, iendswith, contains, icontains, regex, iregex in the official docs for the details.
How to do comparison(>, >=, <, <=) operations?
Getting all users, whose id is greater than(>) 3 integer:
qs = User.objects.filter(id__gt=3)
SELECT ...
FROM "auth_user"
WHERE "auth_user"."id" > 3
Getting all users, whose id is less than(<) 4 integer:
qs = User.objects.filter(id__lt=4)
SELECT ...
FROM "auth_user"
WHERE "auth_user"."id" < 4
As you may guess, gt and lt are short aliases for greater than and less than, respectively.
And for doing greater than or equal or less than or equal lookups we just have to add e - equal at the end of the alias. So, gte will be converted to >= and lte will be converted to <=. More on this here.
How to do BETWEEN operation?
To implement BETWEEN operation we are going to use another Django's tool called range. And we are going to filter users whose date_joined(datetime) value is in a specific date time range. Before building the QuerySet let's prepare two timezone aware datetime objects:
import pytz
from django.utils import timezone
start = timezone.make_aware(timezone.datetime(year=2019, month=1, day=1), pytz.utc)
end = timezone.make_aware(timezone.datetime(year=2019, month=12, day=31), pytz.utc)
the QuerySet:
qs = User.objects.filter(date_joined__range=[start, end])
SQL equivalent:
SELECT ...
FROM "auth_user"
WHERE "auth_user"."date_joined" BETWEEN 2019-01-01 00:00:00+00:00 AND 2019-12-31 00:00:00+00:00
FROM "auth_user"
WHERE "auth_user"."date_joined" BETWEEN 2019-01-01 00:00:00+00:00 AND 2019-12-31 00:00:00+00:00
FYI, you can use range anywhere you can use BETWEEN in SQL queries - for dates, numbers, and even characters. Note that the end of the range is inclusive.
How to LIMIT the number of results in queryset?
To implement SQL’s LIMIT and OFFSET clauses we should use a subset of Python’s array-slicing syntax on a QuerySet object. The example below returns the first 10 users(LIMIT 10):
qs = User.objects.all()[:10]
SQL equivalent:
SELECT ...
FROM "auth_user"
LIMIT 10
tenth through twentieth users(LIMIT 10 OFFSET 10):
qs = User.objects.all()[10:20]
SELECT ...
FROM "auth_user"
LIMIT 10 OFFSET 10
At first glance you might think that this type of LIMIT OFFSET operations force to evaluate the QuerySet. But in general, slicing returns a new QuerySet object - it doesn’t evaluate the query. As we've noted at the beginning of this article, slices evaluate a QuerySet only if we use them with the step parameter. Both examples above are not evaluated - new QuerySets are being returned instead. An example of slicing with the step could be returning a list of every second object of the first 10 users:
list_of_every_second = User.objects.all()[:10:2]
SELECT ...
FROM "auth_user"
LIMIT 10
QuerySet for this operation is the same as for LIMIT 10 one above, but immediate evaluation and additional post-processing happened in this case in order to return every second object from the list of 10 objects.
To retrieve a single user rather than a list, use an index instead of a slice. For example, this returns the first User in the database:
In: User.objects.all()[0]
Out: < User: John >
SQL equivalent:
SELECT ...
FROM "auth_user"
LIMIT 1
Note, that this approach will raise IndexError if there are no rows in the database.
It's important to note that negative indexing (i.e. User.objects.all()[-1]) is not supported on QuerySets.
The implemenaion of slicing/indexing is written inside of getitem method that you can find in QuerySet class located in django.db.models.query module.
How to ORDER results with QuerySets?
To order a QuerySet you need to use the order_by() method. Additionally, you should pass the field(s) that are going to be used in sorting to that method:
qs = User.objects.order_by('date_joined')
SQL equivalent:
SELECT ...
FROM "auth_user"
ORDER BY "auth_user"."date_joined" ASC
Query will return the list of users ordered by date_joined field ascending. We can pass as many fields as we need, also, adding the -(dash) at the beginning of the field will order results in descending order. Example:
qs = User.objects.order_by('date_joined', '-first_name')
SQL equivalent:
SELECT ...
FROM "auth_user"
ORDER BY "auth_user"."date_joined" ASC,
"auth_user"."first_name" DESC
reverse() method will reverse the existing order of QuerySet. Calling reverse() second time will restore the ordering back to the normal direction:
qs = User.objects.order_by('date_joined', '-first_name').reverse()
SELECT ...
FROM "auth_user"
ORDER BY "auth_user"."date_joined" DESC,
"auth_user"."first_name" ASC
In [1]: qs1 = User.objects.order_by('date_joined')
In [2]: qs2 = User.objects.order_by('date_joined').reverse().reverse()
In [3]: str(qs1.query) == str(qs2.query)
Out [3]: True
To order randomly, use '?' like below:
qs = User.objects.order_by('?')
SELECT ...
FROM "auth_user"
ORDER BY RANDOM() ASC
How to get a single object from QuerySet?
The first approach is to use indexing as we used in the LIMIT section above:
How to get a single object from QuerySet?
The first approach is to use indexing as we used in the LIMIT section above:
In: User.objects.all()[0]
Out: < User: John >
Similiar QuerySet we can construct using the get() method. get() method returns the object matching the given lookup parameters, and raises DoesNotExist exception if there is no match. It raises MultipleObjectsReturned exception if more than one object was found. Example:
In: User.objects.get(pk=1)
Out: < User: John >
Also, If you expect a QuerySet to return a single row, you can use get() without any arguments.
There are 4 other handy methods that exist to help us when we want to get a single row/object with some conditions.
Indexing and get() approaches are "alarmic" in some cases and we might want to get the object if it exists, or None if it does not exist without catching any exceptions. And the first() method is very beneficial for that case:
user = User.objects.order_by('date_joined', '-first_name').first()
the equivalent example using indexing:
try:
user = User.objects.order_by('date_joined', '-first_name')[0]
except IndexError:
user = None
The second method is the last(), which works like first(), but returns the last object in the QuerySet:
user = User.objects.order_by('first_name').last()
user = User.objects.order_by('first_name').reverse().first()
SQLs for both queries will be the same.
It's worth to mention 2 other methods. latest(), that returns the latest object in the table based on the given field(s) and earliest() that works the same way but the opposite direction.
user = User.objects.latest('date_joined', '-first_name')
SELECT ...
FROM "auth_user"
ORDER BY "auth_user"."date_joined" DESC,
"auth_user"."first_name" ASC
Like get(), earliest() and latest() raise DoesNotExist if there is no object with the given parameters. As mentioned in the official docs, earliest() and latest() exist purely for convenience and readability.
$ python manage.py shell_plus --print-sql
>>> User.objects.first()
SELECT "auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined"
FROM "auth_user"
ORDER BY "auth_user"."id" ASC
LIMIT 1
Execution time: 0.011151s [Database: default]
Note that DEBUG in project settings must be set to True.
You can also specify SHELL_PLUS_PRINT_SQL configuration option in settings to omit the above command-line option:
# will print SQL queries in shell_plus
SHELL_PLUS_PRINT_SQL = True
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 table
id | first_name | last_name | age | city
1 | sample | 1 | 20 | city1
2 | sample | 2 | 25 | city2
3 | sample | 3 | 30 | city3
Common Django ORMs
Filter 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')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>
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')
<QuerySet [{'id': 1, 'city': 'city1'}, {'id': 2, 'city': 'city2'}, {'id': 3, 'city': 'city3'}]>
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')
<QuerySet []>
‘__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')
<QuerySet [{'id': 2, 'city': 'city2'}]>
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')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}]>
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])
<QuerySet [<User: User object (1)>, <User: User object (2)>]>
exclude()
Excludes objects from the queryset which match with the lookup parameters.
>>> User.objects.exclude(id=1)
<QuerySet [<User: User object (2)>, <User: User object (3)>]>
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'))
<QuerySet [{'city': 'city1', 'age__sum': 20}, {'city': 'city2', 'age__sum': 25}, {'city': 'city3', 'age__sum': 30}]>
Also, we can sort a queryset using order_by().
>>> User.objects.values('city').annotate(total_age=Sum('age')).order_by('-total_age')
<QuerySet [{'city': 'city3', 'total_age': 30}, {'city': 'city2', 'total_age': 25}, {'city': 'city1', 'total_age': 20}]>
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')
<QuerySet [{'city': 'city3', 'total_age': 30}, {'city': 'city2', 'total_age': 25}]>
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')
<QuerySet [{'id': 1, 'city': 'city1', 'age': 20}]>
This ORM will find all users with city=city1 and age= 25.
OR
‘|’ is used for OR operation between 2 Q expressions.
>>> User.objects.filter(Q(city='city1') | Q(age=25)).values('id','city','age')
<QuerySet [{'id': 1, 'city': 'city1', 'age': 20}, {'id': 2, 'city': 'city2', 'age': 25}]>
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')
<QuerySet [{'id': 2, 'city': 'city2', 'age': 25}]>
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')
<QuerySet [{'id': 1, 'age': 20, 'new_age': 40}, {'id': 2, 'age': 25, 'new_age': 50}, {'id': 3, 'age': 30, 'new_age': 60}]>
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)
3
>>> User.objects.values('id','age')
<QuerySet [{'id': 1, 'age': 40}, {'id': 2, 'age': 50}, {'id': 3, 'age': 60}]>
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.
Top comments (0)