Django QuerySet Example with Equivalent SQL Code

In this blog, we will learn the most commonly used Django QuerySet examples with their equivalent SQL code.

A QuerySet is a collection of objects/rows fetched from your database. The QuerySet can be constructed, filtered, sliced, and passed around without actually hitting the database. More about QuerySet you can find Django’s official website.

Getting Started

Note: All code examples below are written and tested with the Django 4.1 and PostgreSQL 15 versions. And many of them will work on lower/higher versions of Django and for other relational database engines without any errors.

If you want to learn how to create a Hello World Django project, check out the blog How to Create Hello World Website App in Django

Below is the ER-Model for models we will use to execute queries. There are many fields in these tables but we will only use some of them.

class Film(models.Model):
    film_id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=255)
    description = models.TextField(blank=True, null=True)
    release_year = models.IntegerField(blank=True, null=True)
    language = models.ForeignKey('Language', models.DO_NOTHING)
    rental_duration = models.SmallIntegerField()
    rental_rate = models.DecimalField(max_digits=4, decimal_places=2)
    length = models.SmallIntegerField(blank=True, null=True)
    replacement_cost = models.DecimalField(max_digits=5, decimal_places=2)
    rating = models.TextField(blank=True, null=True) 
    last_update = models.DateTimeField()
    special_features = models.TextField(blank=True, null=True) 
    fulltext = models.TextField()

    class Meta:
        db_table = 'film'


class Language(models.Model):
    language_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=20)
    last_update = models.DateTimeField()

    class Meta:
        db_table = 'language'

Contents

1. SELECT – Query data from a single table
2. ORDER BY – Sort result set returned from a query
3. SELECT DISTINCT – Removes duplicate rows from the result set
4. WHERE – Filter rows based on a specified condition
5. LIMIT – Get a subset of rows generated by a query
6. INNER JOIN – fetch rows from one table that has the corresponding rows in other tables
7. INSERT – Insert a single row into a table
8. UPDATE – update existing data in a table
9. DELETE – delete data in a table

1. SELECT – Query data from a single table

The SELECT is the most common task when you work with the database to query data from tables. Below are some SELECT statements with their equivalent Django QuerySet.

i. Get a single column from the table.

ii. Get multiple columns from the table.

iii. Get all columns from the table.

2. ORDER BY – Sort result set returned from a query

When you query data using the SELECT statement, it returns rows in an unspecified order. To sort the result set rows, you use the ORDER BY clause in the SELECT statement. In Django QuerySet it can be done by the order_by(column_name) function.

i. Sort rows by one column in ASCENDING order

ii. Sort rows by one column in DESCENDING order

iii. Sort rows by multiple columns in ASCENDING order

iv. Sort rows by multiple columns in both ascending and descending order.

3. SELECT DISTINCT – Removes duplicate rows from the result set

In the SELECT statement, the DISTINCT clause removes duplicate rows from a result set. In Django QuerySet distinct() method is used to remove duplicates.

i. Show unique data from a single column from the table.

ii. Show unique data from multiple columns from the table.

4. WHERE – Filter rows based on a specified condition

The SELECT statement returns all rows from one or more columns from a table. To select rows that satisfy a given condition, you have to use a WHERE clause.

i. Using WHERE clause with the equal (=) operator

ii. Using the WHERE clause with the AND operator

iii. Using WHERE clause with the OR operator

iv. Using WHERE clause with the IN operator

v. Using WHERE clause with the LIKE operator to match BEGINNING characters

vi. Using WHERE clause with the LIKE operator to match ENDING characters

vii. Using WHERE clause with the LIKE operator to match characters from ANYWHERE

Note: These commands also support case-insensitive lookups. Check startswith, istartswith, endswith, iendswith, contains, icontains in the official docs for the details.

viii. Using the WHERE clause with the BETWEEN operator

viii. Using the WHERE clause with the NOT EQUAL operator

ix. Using the WHERE clause with the LESS THAN operator

x. Using the WHERE clause with the LESS THAN OR EQUAL operator

xi. Using the WHERE clause with the GREATER THAN operator

xii. Using the WHERE clause with the GREATER THAN OR EQUAL operator

xiii. Using the WHERE clause with the IS NULL operator

5. LIMIT – Get a subset of rows generated by a query

The LIMIT is an optional clause of the SELECT statement that constrains the number of rows returned by the query. In Django, we can use array slicing to limit the number of rows returned.

6. INNER JOIN – fetch rows from one table that has the corresponding rows in other tables

Join is used to combine columns from one or more tables based on the values of the common columns between related tables. The common columns are typically the primary key columns of the first table and the foreign key columns of the second table.

The following statement joins the first table Film with the second table Language by matching the values in the Film.language_id and Language.language_id columns:

i. Select all columns from both tables

ii. Select specific columns from both tables

7. INSERT – Insert a single row into a table

Given code inserts a new row in the Language table.

8. UPDATE – update existing data in a table

Suppose you’ve saved a single row in the lang_obj object. You can update it by assigning a new value to a specific field and then calling save() command.

9. DELETE – delete data from a table

Suppose you’ve fetched a single row in the lang_obj object. You can delete it by calling the delete() command.