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.
To implement the above entity, we would model it as a table in SQL.
1 2 3 4 5 6 |
CREATE TABLE Person ( id int, name varchar(50), age int NOT NULL, gender varchar(10), ); |
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.
1 2 3 4 |
class Person(models.Model): name = models.CharField(max_length=50, blank=True) age = models.IntegerField() gender = models.CharField(max_length=10, blank=True) |
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:
1 2 |
SELECT * FROM Person; |
Django:
1 2 3 4 5 |
persons = Person.objects.all() for person in persons: print(person.name) print(person.gender) print(person.age) |
Fetch specific columns
SQL:
1 2 |
SELECT name, age FROM Person; |
Django:
1 |
Person.objects.only('name', 'age') |
etch distinct rows
SQL:
1 2 |
SELECT DISTINCT name, age FROM Person; |
Django:
1 |
Person.objects.values('name', 'age').distinct() |
Fetch specific number of rows
SQL:
1 2 3 |
SELECT * FROM Person LIMIT 10; |
Django:
1 |
Person.objects.all()[:10] |
LIMIT AND OFFSET keywords
SQL:
1 2 3 4 |
SELECT * FROM Person OFFSET 5 LIMIT 5; |
Django:
1 |
Person.objects.all()[5:10] |
WHERE Clause
Filter by single column
SQL:
1 2 3 |
SELECT * FROM Person WHERE id = 1; |
Django:
1 |
Person.objects.filter(id=1) |
Filter by comparison operators
SQL:
1 2 3 4 5 |
WHERE age > 18; WHERE age >= 18; WHERE age < 18; WHERE age <= 18; WHERE age != 18; |
Django:
1 2 3 4 5 |
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:
1 2 3 |
SELECT * FROM Person WHERE age BETWEEN 10 AND 20; |
Django:
1 |
Person.objects.filter(age__range=(10, 20)) |
LIKE operator
SQL:
1 2 3 4 5 6 |
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'; |
Django:
1 2 3 4 5 6 |
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') |
IN operator
SQL:
1 |
WHERE id in (1, 2); |
Django:
1 |
Person.objects.filter(id__in=[1, 2]) |
AND, OR and NOT Operators
SQL:
1 |
WHERE gender='male' AND age > 25; |
Django:
1 |
Person.objects.filter(gender='male', age__gt=25) |
SQL:
1 |
WHERE gender='male' OR age > 25; |
Django:
1 2 |
from django.db.models import Q Person.objects.filter(Q(gender='male') | Q(age__gt=25)) |
SQL:
1 |
WHERE NOT gender='male'; |
Django:
1 |
Person.objects.exclude(gender='male') |
1 |
Person.objects.exclude(gender='male') |
https://amitness.com/2018/10/django-orm-for-sql-users/