loading...

Django ORM if you already know SQL

amitness profile image Amit Chaudhary Originally published at amitness.com Updated on ・4 min read

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

pic
Editor guide
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