Django ORM if you already know SQL

If you’re migrating to Django from another MVC framework, it’s likely that you’re already familiar with SQL.

In this article, I’ll demonstrate how to utilize Django’s ORM by drawing parallels to comparable SQL statements. Establishing connections between this new topic and your existing SQL knowledge will expedite your learning process for using the ORM.

Let us consider a simple base model for a person with attributes such as name, age, and gender.

ERD

To implement the above entity, we would model it as a table in SQL.

CREATE TABLE Person (
    id int,
    name varchar(50),
    age int NOT NULL,
    gender varchar(10),
);

In Django, a table is represented as a class that inherits from the base Model class. The Django ORM (Object-Relational Mapping) automatically generates the corresponding table in the database.

class Person(models.Model):
    name = models.CharField(max_length=200, blank=True)
    age = models.IntegerField()
    gender = models.CharField(max_length=15, blank=True)

The most used data types are:

The various queries we can perform are:

SELECT Statement

1. Fetch all rows

SQL

SELECT *
FROM Person;

Django

persons = Person.objects.all()
for person in persons:
    print(person.name)
    print(person.gender)
    print(person.age)

2. Fetch specific columns

SQL

SELECT name, age
FROM Person;

Django

Person.objects.only('name', 'age')

3. Fetch distinct rows

SQL

SELECT DISTINCT name, age
FROM Person;

Django

Person.objects.values('name', 'age').distinct()p

4. Fetch a specific number of rows

SQL

SELECT *
FROM Person
LIMIT 10;

Django

Person.objects.all()[:10]

6. LIMIT AND OFFSET keywords

SQL

SELECT *
FROM Person
OFFSET 5
LIMIT 5;

Django

Person.objects.all()[5:10]

WHERE Clause

1. Filter by a single column

SQL

SELECT *
FROM Person
WHERE id = 1;

Django

Person.objects.filter(id=1)

2. Filter by comparison operators

SQL

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;

Django

Person.objects.filter(age__gt=18)
Person.objects.filter(age__gte=18)
Person.objects.filter(age__lt=18)
Person.objects.filter(age__lte=18)
Person.objects.exclude(age=18)

BETWEEN Clause

SQL

SELECT *
FROM Person 
WHERE age BETWEEN 20 AND 40;

Django

Person.objects.filter(age__range=(20, 40))