Intro

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.

More about that you can find here.

Getting Started

Note that all code examples below are written and tested with the Django 3.0.1 and PostgreSQL 12 versions. And most of them will work on lower on higher versions of Django and for other relational database engines without any issues.

As a main model(table) for the examples, we chose Django's built-in User model. If you are unfamiliar with it or want to have deeper understanding of it, please check the official docs.

There are many different fields(columns) in the User model. But we are going to use only some of them. The table below partially represents the User model and the data examples that we can store there.

Of course, this isn't the actual table for the User model, there are many other missing fields/columns, we just listed only ones that we are going to use.

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"'

This technique will be used in further examples.

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

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:

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.

Bonus Tip!

If you install and plug django_extensions package to your project, you can output SQL queries in shell without manually printing them. There is a different shell command with special option(argument) for that:

$ 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

Conclusion

The QuerySet API is very big and there are a lot of other tools that we will cover in different posts. So keep subscribed to not miss them.