Django QuerySet Example with Equivalent SQL Code

Django Query Set 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.

film-language
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.

SQL CodeSELECT title FROM film;
Django QuerySetFilm.objects.all().values('title')

ii. Get multiple columns from the table.

SQL CodeSELECT title, release_year FROM film;
Django QuerySetFilm.objects.all().values('title', 'release_year')

iii. Get all columns from the table.

SQL CodeSELECT * FROM film;
Django QuerySetFilm.objects.all()

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

SQL CodeSELECT * FROM film ORDER BY title ASC;
Django QuerySetFilm.objects.all().order_by('title')

ii. Sort rows by one column in DESCENDING order

SQL CodeSELECT * FROM film ORDER BY title DESC;
Django QuerySetFilm.objects.all().order_by('-title')

iii. Sort rows by multiple columns in ASCENDING order

SQL CodeSELECT * FROM film ORDER BY title, release_year;
Django QuerySetFilm.objects.all().order_by('title', 'release_year')

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

SQL CodeSELECT * FROM film ORDER BY title ASC, release_year DESC;
Django QuerySetFilm.objects.all().order_by('title', '-release_year')

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.

SQL CodeSELECT DISTINCT rating FROM film;
Django QuerySetFilm.objects.values('rating').distinct()

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

SQL CodeSELECT DISTINCT rating, title FROM film;
Django QuerySetFilm.objects.values('rating', 'title').distinct()

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

SQL CodeSELECT * FROM film WHERE title='Aladdin Calendar';
Django QuerySetFilm.objects.filter(title='Aladdin Calendar')

ii. Using the WHERE clause with the AND operator

SQL CodeSELECT * FROM film WHERE (title='Aladdin Calendar' AND rating='NC-17');
Django QuerySetFilm.objects.filter(Q(title='Aladdin Calendar') & Q(rating='NC-17'))

iii. Using WHERE clause with the OR operator

SQL CodeSELECT * FROM film WHERE (title='Aladdin Calendar' OR rating='NC-17');
Django QuerySetFilm.objects.filter(Q(title='Aladdin Calendar') | Q(rating='NC-17'))

iv. Using WHERE clause with the IN operator

SQL CodeSELECT * FROM film WHERE rating IN ('NC-17', 'R', 'PG');
Django QuerySetFilm.objects.filter(rating__in=['NC-17', 'R', 'PG'])

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

SQL CodeSELECT * FROM film WHERE title LIKE 'Ala%';
Django QuerySetFilm.objects.filter(title__startswith='Ala')

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

SQL CodeSELECT * FROM film WHERE title LIKE '%ape';
Django QuerySetFilm.objects.filter(title__endswith='ape')

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

SQL CodeSELECT * FROM film WHERE title LIKE '%sag%';
Django QuerySetFilm.objects.filter(title__contains='sag')

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

SQL CodeSELECT * FROM film WHERE length BETWEEN 100 AND 110;
Django QuerySetFilm.objects.filter(length__range=[100, 110])

viii. Using the WHERE clause with the NOT EQUAL operator

SQL CodeSELECT * FROM film WHERE rating <> 'PG';
Django QuerySetFilm.objects.filter(~Q(rating='PG'))

ix. Using the WHERE clause with the LESS THAN operator

SQL CodeSELECT * FROM film WHERE length < 100;
Django QuerySetFilm.objects.filter(length__lt=100)

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

SQL CodeSELECT * FROM film WHERE length <= 100;
Django QuerySetFilm.objects.filter(length__lte=100)

xi. Using the WHERE clause with the GREATER THAN operator

SQL CodeSELECT * FROM film WHERE length > 100;
Django QuerySetFilm.objects.filter(length__gt=100)

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

SQL CodeSELECT * FROM film WHERE length >= 100;
Django QuerySetFilm.objects.filter(length__gte=100)

xiii. Using the WHERE clause with the IS NULL operator

SQL CodeSELECT * FROM film WHERE title IS NULL;
Django QuerySetFilm.objects.filter(title__isnull=True)

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.

SQL CodeSELECT * FROM film LIMIT 10;
Django QuerySetFilm.objects.all()[:10]

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

SQL CodeSELECT * FROM film INNER JOIN language ON film.language_id=language.language_id;
Django QuerySetFilm.objects.select_related('language')

ii. Select specific columns from both tables

SQL CodeSELECT film.title, language.name FROM film INNER JOIN language ON film.language_id=language.language_id;
Django QuerySetFilm.objects.select_related('language').only('title', 'language__name')

7. INSERT – Insert a single row into a table

Given code inserts a new row in the Language table.

SQL CodeINSERT INTO language (name, last_update) values('Hindi', '2022-12-03');
Django QuerySetLanguage.create(name='Hindi', last_update='2022-12-03')

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.

Django QuerySetlang_obj.name = 'Hindi' lang_obj.save()

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.

Django QuerySetFilm.objects.get(language_id=id).delete()

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top