DEV Community

Amit Chaudhary
Amit Chaudhary

Posted on • Updated on • Originally published at amitness.com

Django ORM if you already know SQL

If you are migrating to Django from another MVC framework, chances are you already know SQL.

In this post, I will be illustrating how to use Django ORM by drawing analogies to equivalent SQL statements. Connecting a new topic to your existing knowledge will help you learn to use the ORM faster.

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

Person ER Diagram

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),
);
Enter fullscreen mode Exit fullscreen mode

The same table is modeled in Django as a class which inherits from the base Model class. The ORM creates the equivalent table under the hood.

class Person(models.Model):
    name = models.CharField(max_length=50, blank=True)
    age = models.IntegerField()
    gender = models.CharField(max_length=10, blank=True)
Enter fullscreen mode Exit fullscreen mode

The most used data types are:

SQL Django
INT IntegerField()
VARCHAR(n) CharField(max_length=n)
TEXT TextField()
FLOAT(n) FloatField()
DATE DateField()
TIME TimeField()
DATETIME DateTimeField()

The various queries we can use are:

SELECT Statement

Fetch all rows

SQL:

SELECT *
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

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

Fetch specific columns

SQL:

SELECT name, age
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.only('name', 'age')
Enter fullscreen mode Exit fullscreen mode

Fetch distinct rows

SQL:

SELECT DISTINCT name, age
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.values('name', 'age').distinct()
Enter fullscreen mode Exit fullscreen mode

Fetch specific number of rows

SQL:

SELECT *
FROM Person
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.all()[:10]
Enter fullscreen mode Exit fullscreen mode

LIMIT AND OFFSET keywords

SQL:

SELECT *
FROM Person
OFFSET 5
LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.all()[5:10]
Enter fullscreen mode Exit fullscreen mode

WHERE Clause

Filter by single column

SQL:

SELECT *
FROM Person
WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(id=1)
Enter fullscreen mode Exit fullscreen mode

Filter by comparison operators

SQL:

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

BETWEEN Clause

SQL:

SELECT *
FROM Person 
WHERE age BETWEEN 10 AND 20;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(age__range=(10, 20))
Enter fullscreen mode Exit fullscreen mode

LIKE operator

SQL:

WHERE name like '%A%';
WHERE name like binary '%A%';
WHERE name like 'A%';
WHERE name like binary 'A%';
WHERE name like '%A';
WHERE name like binary '%A';
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(name__icontains='A')
Person.objects.filter(name__contains='A')
Person.objects.filter(name__istartswith='A')
Person.objects.filter(name__startswith='A')
Person.objects.filter(name__iendswith='A')
Person.objects.filter(name__endswith='A')
Enter fullscreen mode Exit fullscreen mode

IN operator

SQL:

WHERE id in (1, 2);
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(id__in=[1, 2])
Enter fullscreen mode Exit fullscreen mode

AND, OR and NOT Operators

SQL:

WHERE gender='male' AND age > 25;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(gender='male', age__gt=25)
Enter fullscreen mode Exit fullscreen mode

SQL:

WHERE gender='male' OR age > 25;
Enter fullscreen mode Exit fullscreen mode

Django:

from django.db.models import Q
Person.objects.filter(Q(gender='male') | Q(age__gt=25))
Enter fullscreen mode Exit fullscreen mode

SQL:

WHERE NOT gender='male';
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.exclude(gender='male')
Enter fullscreen mode Exit fullscreen mode

NULL Values

SQL:

WHERE age is NULL;
WHERE age is NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(age__isnull=True)
Person.objects.filter(age__isnull=False)

# Alternate approach
Person.objects.filter(age=None)
Person.objects.exclude(age=None)
Enter fullscreen mode Exit fullscreen mode

ORDER BY Keyword

Ascending Order

SQL:

SELECT *
FROM Person
order by age;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.order_by('age')
Enter fullscreen mode Exit fullscreen mode

Descending Order

SQL:

SELECT *
FROM Person
ORDER BY age DESC;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.order_by('-age')
Enter fullscreen mode Exit fullscreen mode

INSERT INTO Statement

SQL:

INSERT INTO Person
VALUES ('Jack', '23', 'male');
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.create(name='jack', age=23, gender='male)
Enter fullscreen mode Exit fullscreen mode

UPDATE Statement

Update single row

SQL:

UPDATE Person
SET age = 20
WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

Django:

person = Person.objects.get(id=1)
person.age = 20
person.save()
Enter fullscreen mode Exit fullscreen mode

Update multiple rows

SQL:

UPDATE Person
SET age = age * 1.5;
Enter fullscreen mode Exit fullscreen mode

Django:

from django.db.models import F

Person.objects.update(age=F('age')*1.5)
Enter fullscreen mode Exit fullscreen mode

DELETE Statement

Delete all rows

SQL:

DELETE FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.all().delete()
Enter fullscreen mode Exit fullscreen mode

Delete specific rows

SQL:

DELETE FROM Person
WHERE age < 10;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.filter(age__lt=10).delete()
Enter fullscreen mode Exit fullscreen mode

Aggregation

MIN Function

SQL:

SELECT MIN(age)
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

>>> from django.db.models import Min
>>> Person.objects.all().aggregate(Min('age'))
{'age__min': 0}
Enter fullscreen mode Exit fullscreen mode

MAX Function

SQL:

SELECT MAX(age)
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

>>> from django.db.models import Max
>>> Person.objects.all().aggregate(Max('age'))
{'age__max': 100}
Enter fullscreen mode Exit fullscreen mode

AVG Function

SQL:

SELECT AVG(age)
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

>>> from django.db.models import Avg
>>> Person.objects.all().aggregate(Avg('age'))
{'age__avg': 50}
Enter fullscreen mode Exit fullscreen mode

SUM Function

SQL:

SELECT SUM(age)
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

>>> from django.db.models import Sum
>>> Person.objects.all().aggregate(Sum('age'))
{'age__sum': 5050}
Enter fullscreen mode Exit fullscreen mode

COUNT Function

SQL:

SELECT COUNT(*)
FROM Person;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.count()
Enter fullscreen mode Exit fullscreen mode

GROUP BY Statement

Count of Person by gender

SQL:

SELECT gender, COUNT(*) as count
FROM Person
GROUP BY gender;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.values('gender').annotate(count=Count('gender'))
Enter fullscreen mode Exit fullscreen mode

HAVING Clause

Count of Person by gender if number of person is greater than 1

SQL:

SELECT gender, COUNT('gender') as count
FROM Person
GROUP BY gender
HAVING count > 1;
Enter fullscreen mode Exit fullscreen mode

Django:

Person.objects.annotate(count=Count('gender'))
.values('gender', 'count')
.filter(count__gt=1)
Enter fullscreen mode Exit fullscreen mode

JOINS

Consider a foreign key relationship between books and publisher.

class Publisher(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
Enter fullscreen mode Exit fullscreen mode

Fetch publisher name for a book

SQL:

SELECT name
FROM Book
LEFT JOIN Publisher
ON Book.publisher_id = Publisher.id
WHERE Book.id=1;
Enter fullscreen mode Exit fullscreen mode

Django:

book = Book.objects.select_related('publisher').get(id=1)
book.publisher.name
Enter fullscreen mode Exit fullscreen mode

Fetch books which have specific publisher

SQL:

SELECT *
FROM Book
WHERE Book.publisher_id = 1;
Enter fullscreen mode Exit fullscreen mode

Django:

publisher = Publisher.objects.prefetch_related('book_set').get(id=1)
books = publisher.book_set.all()
Enter fullscreen mode Exit fullscreen mode

Connect

If you enjoyed this blog post, feel free to connect with me on Twitter where I share new blog posts every week.

Discussion (3)

Collapse
mattedwards profile image
Matt Edwards

Thank you! That’s a really useful article.

Collapse
andrewbaisden profile image
Andrew Baisden

Good article and it makes sense to me now that i know SQL again :)

Collapse
smyja profile image
Smyja

Really good