Django ORM and SQL: Making QuerySet Expressions

Python Django ORM like SQL: Making Query Expressions
Python Django ORM like SQL: Making Query Expressions

Making QuerySet & Query Expressions with Django ORM like SQL, or Django ORM == SQL.

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

In this post, we 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.

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.

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:

SELECT *
FROM Person;

Django QuerySet:

persons = Person.objects.all()

for person in persons:
print(person.name)
print(person.gender)
print(person.age)

Fetch specific columns

SQL:

SELECT name, age
FROM Person;

Django QuerySet:

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

Fetch distinct rows

SQL:

SELECT DISTINCT name, age
FROM Person;

Django QuerySet:

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

Fetch specific number of rows

SQL:

SELECT *
FROM Person
LIMIT 10;

Django QuerySet:

Person.objects.all()[:10]

LIMIT AND OFFSET keywords

SQL:

SELECT *
FROM Person
OFFSET 5
LIMIT 5;

Django:

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

WHERE Clause

Filter by single column

SQL:

SELECT *
FROM Person
WHERE id = 1;

Django:

Person.objects.filter(id=1)

Filter by comparison operators

SQL:

WHERE age > 18; 
WHERE age >= 18; 
WHERE age < 18; 
WHERE age <= 18; 
WHERE age ! =  18;
SELECT ... WHERE id > 4;

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)
Entry.objects.filter(id__gt=4)

BETWEEN Clause & TIME

SQL:

SELECT *
FROM Person
WHERE age BETWEEN 10 AND 20;

SELECT ... WHERE pub_date BETWEEN '2005-01-01' and '2005-03-31';

SELECT ... WHERE pub_date BETWEEN '2005-01-01' AND '2005-12-31'; SELECT ... WHERE pub_date >= '2005-01-01';

SELECT ... WHERE EXTRACT('month' FROM pub_date) = '12'; 
SELECT ... WHERE EXTRACT('month' FROM pub_date) >= '6';

SELECT ... WHERE EXTRACT('day' FROM pub_date) = '3'; 
SELECT ... WHERE EXTRACT('day' FROM pub_date) >= '3';

SELECT ... WHERE EXTRACT('hour' FROM timestamp) = '23'; 
SELECT ... WHERE EXTRACT('hour' FROM time) = '5'; 
SELECT ... WHERE EXTRACT('hour' FROM timestamp) >= '12';

SELECT ... WHERE EXTRACT('minute' FROM timestamp) = '29'; 
SELECT ... WHERE EXTRACT('minute' FROM time) = '46'; 
SELECT ... WHERE EXTRACT('minute' FROM timestamp) >= '29';

SELECT ... WHERE EXTRACT('second' FROM timestamp) = '31'; 
SELECT ... WHERE EXTRACT('second' FROM time) = '2'; 
SELECT ... WHERE EXTRACT('second' FROM timestamp) >= '31';

Django:

Person.objects.filter (age_____ range= (10, 20))

import datetime
start_date = datetime.date(2005, 1, 1)
end_date = datetime.date(2005, 3, 31)
Entry.objects.filter(pub_date__range=(start_date, end_date))

Entry.objects.filter(pub_date__year=2005) Entry.objects.filter(pub_date__year__gte=2005)

Entry.objects.filter(pub_date__month=12) Entry.objects.filter(pub_date__month__gte=6)

Entry.objects.filter(pub_date__day=3) Entry.objects.filter(pub_date__day__gte=3)

Event.objects.filter(timestamp__hour=23) Event.objects.filter(time__hour=5) Event.objects.filter(timestamp__hour__gte=12)

Event.objects.filter(timestamp__minute=29) Event.objects.filter(time__minute=46) Event.objects.filter(timestamp__minute__gte=29)

Event.objects.filter(timestamp__second=31) Event.objects.filter(time__second=2) Event.objects.filter(timestamp__second__gte=31)

LIKE operator

SQL:

WHERE   name    like  %A% ;  
WHERE  name like binary  '%A%';
WHERE    name      like 'A%';      
SELECT ... WHERE headline ILIKE 'Lennon%';
WHERE  name like binary  'A%';
SELECT ... WHERE headline LIKE 'Lennon%';
WHERE    name      like       '%A';  
SELECT ... WHERE headline ILIKE '%Lennon'
WHERE  name like binary  '%A';
SELECT ... WHERE headline LIKE '%Lennon';
SELECT ... WHERE headline LIKE '%Lennon%';

Django:

Person  objects.filter(name icontains='A')
Person  objects.filter(name contains='A')
Person  objects.filter(name istartswith='A')
Entry.objects.filter(headline__istartswith='Lennon')
Person  objects.filter(name startswith='A')
Entry.objects.filter(headline__startswith='Lennon')
Person  objects.filter(name iendswith='A')
Entry.objects.filter(headline__iendswith='Lennon')
Person  objects.filter(name endswith='A')
Entry.objects.filter(headline__endswith='Lennon')
Entry.objects.get(headline__contains='Lennon')

Field lookups

SQL:

SELECT ... WHERE name ILIKE 'beatles blog'; 
SELECT ... WHERE name IS NULL;
SELECT ... WHERE id = 14; 
SELECT ... WHERE id IS NULL;

Django:

Blog.objects.get(name__iexact='beatles blog') Blog.objects.get(name__iexact=None)
Entry.objects.get(id__exact=14) 
Entry.objects.get(id__exact=None)

IN operator

SQL:

WHERE id in (1, 2);

SELECT ... WHERE id IN (1, 3, 4);
SELECT ... WHERE headline IN ('a', 'b', 'c');

SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%')

Django:

Person.objects.filter(id__in=[1, 2])

Entry.objects.filter(id__in=[1, 3, 4])
Entry.objects.filter(headline__in='abc')

inner_qs = Blog.objects.filter(name__contains='Cheddar') 
entries = Entry.objects.filter(blog__in=inner_qs)

AND, OR and NOT Operators

SQL:

WHERE gender='male' AND age > 25;
WHERE x=1 AND y=2

Django:

Person.objects.filter(gender='male', age gt=25)
Model.objects.filter(x=1) & Model.objects.filter(y=2)
Model.objects.filter(x=1, y=2)
from django.db.models import Q
Model.objects.filter(Q(x=1) & Q(y=2))

SQL:

WHERE gender='male' OR age > 25;
WHERE x=1 OR y=2

Django:

from django.db.models import Q

Person.objects.filter(Q(gender='male') | Q(age_ gt=25))
Model.objects.filter(x=1) | Model.objects.filter(y=2)

from django.db.models import Q
Model.objects.filter(Q(x=1) | Q(y=2))

SQL:

WHERE NOT gender='male';

WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')

WHERE NOT pub_date > '2005-1-3' 
AND NOT headline = 'Hello'

Django:

Person.objects.exclude(gender='male')

Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello')

Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello')

NULL Values

SQL:

WHERE age is NULL;

SELECT ... WHERE pub_date IS NULL;

WHERE age is NOT NULL;

Django:

Person.objects.filter(age__isnull=True)

Entry.objects.filter(pub_date__isnull=True)

Person.objects.filter(age__isnull=False)

# Alternate approach
Person.objects.filter(age=None)
Person.objects.exclude(age=None)

ORDER BY Keyword

Ascending Order

SQL:

SELECT *
FROM Person
order by age;

Django:

Person.objects.order_by('age')

Descending Order

SQL:

SELECT *
FROM Person
ORDER BY age DESC;

Django:

Person.objects.order_by('-age')

INSERT INTO Statement

SQL:

INSERT INTO Person VALUES ('Jack', '23', 'male');

Django:

Person.objects.create(name='jack', age=23, gender='male)

UPDATE Statement

Update single row

SQL:

UPDATE Person
SET age = 20
WHERE id = 1;

Django:

person = Person.objects.get(id=1)
person.age = person.save()

Update multiple rows

SQL:

UPDATE Person
SET age = age * 1.5;

Django:

from django.db.models import F

Person.objects.update(age=F('age')*1.5)

DELETE Statement

Delete all rows

SQL:

DELETE FROM Person;

Django:

Person.objects.all().delete()

Delete specific rows

SQL:

DELETE FROM Person
WHERE age < 10;

Django:

Person.objects.filter(age__lt=10).delete()

Aggregation

MIN Function

SQL:

SELECT MIN(age)
FROM Person;

Django

from django.db.models import Min

Person.objects.all().aggregate(Min('age'))

# Output
{'age min': 0}

MAX Function

SQL:

SELECT MAX(age)
FROM Person;

Django:

from django.db.models import Max

Person.objects.all().aggregate(Max('age'))

# Outpu
{'age__max': 100}

AVG Function

SQL:

SELECT AVG(age)
FROM Person;

Django:

from django.db.models import Avg

Person.objects.all().aggregate(Avg('age'))

# Output
{'age__avg': 50}

SUM Function

SQL:

SELECT SUM(age)
FROM Person;

Django:

from django.db.models import Sum

Person.objects.all().aggregate(Sum('age'))

# Output
{'age__sum': 5050}

COUNT Function

SQL:

SELECT COUNT(*)
FROM Person;

Django:

# Returns the total number of entries in the database.
Person.objects.count()

# Returns the number of entries whose headline contains 'Lennon'
Entry.objects.filter(headline__contains='Lennon').count()

GROUP BY Statement

Count of Person by gender

SQL:

SELECT gender, COUNT(*) as count 
FROM Person 
GROUP BY gender;

Django:

Person.objects.values('gender').annotate(count=Count('gender'))

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;

Django:

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

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)

Fetch publisher name for a book

SQL:

SELECT name
FROM Book
LEFT JOIN Publisher
ON Book.publisher_id = Publisher.id
WHERE Book.id=1;

Django QuerySet:

book = Book.objects.select_related('publisher').get(id=1)
book.publisher.name

Fetch books which have specific publisher

SQL:

SELECT *
FROM Book
WHERE Book.publisher_id = 1;

Django QuerySet:

publisher = Publisher.objects.prefetch_related('book_set').get(id=1)
books = publisher.book set.all()

Regex

SQL:

SELECT ... WHERE title REGEXP BINARY '^(An?|The) +'; -- MySQL
SELECT ... WHERE REGEXP_LIKE(title, '^(An?|The) +', 'c'); -- Oracle
SELECT ... WHERE title ~ '^(An?|The) +'; -- PostgreSQL
SELECT ... WHERE title REGEXP '^(An?|The) +'; -- SQLite

SELECT ... WHERE title REGEXP '^(an?|the) +'; -- MySQL 
SELECT ... WHERE REGEXP_LIKE(title, '^(an?|the) +', 'i'); -- Oracle 
SELECT ... WHERE title ~* '^(an?|the) +'; -- PostgreSQL 
SELECT ... WHERE title REGEXP '(?i)^(an?|the) +'; -- SQLite

Django:

Entry.objects.get(title__regex=r'^(An?|The) +')

Entry.objects.get(title__iregex=r'^(an?|the) +')

A more detailed explanation about Django QuerySet API see here.

Maybe you can realize all querysets in your code like this E-commerce Shop with Django.

Автор Serhii Kupriienko

I have a multifaceted experience, accomplished several successful projects, including national-scale, modernized a museum, engaged in online commerce, wholesale and retail sales, was an entrepreneur, publisher, and editor. I headed the development department. I gave lectures and training. I organized events and press conferences. I was the secretary of the collegial boards. And I also did the planning and reporting in an organization. My Ph.D. thesis was “The social and economic system of the Inca Empire Tawantinsuyu“.

KUPRIENKO