Django ORM and SQL: Making QuerySet 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.

Next Find the sum of the digits in the number 100! (Python) »

Previous « Create an E-commerce Shop with Django (Part 4)

Published by

Serhii Kupriienko

Tags: djangodjango ormexpressionsModelsprogrammingpythonqueryquerysetsqlstatements

4 роки ago

Як стати Software Engineer: моя історія

Для кого ця стаття? Для таких як я сам, хто в часи пандемії та суттєвої…

3 роки ago

Programming

Useful Git commands

Git is a free and open source distributed version control system designed to handle everything from small…

4 роки ago

Programming

Useful ASCII tables

ASCII Tables ASCII abbreviated from American Standard Code for Information Interchange, is a character encoding standard for electronic communication.…

4 роки ago

Data Science

Conda cheatsheet

Conda Managing Conda and Anaconda, Environments, Python, Configuration, Packages. Removing Packages or Environments Читати далі…

4 роки ago

Python

TOP-10 questions about Python

Читати далі TOP-10 questions about Python

4 роки ago

Django

TOP-10 questions about Django

Читати далі TOP-10 questions about Django

4 роки ago

This website uses cookies.

Django ORM and SQL: Making QuerySet Expressions

SELECT Statement

Fetch all rows

Fetch specific columns

Fetch distinct rows

Fetch specific number of rows

LIMIT AND OFFSET keywords

WHERE Clause

Filter by single column

Filter by comparison operators

BETWEEN Clause & TIME

LIKE operator

Field lookups

IN operator

AND, OR and NOT Operators

NULL Values

ORDER BY Keyword

Ascending Order

Descending Order

INSERT INTO Statement

UPDATE Statement

Update single row

Update multiple rows

DELETE Statement

Delete all rows

Delete specific rows

Aggregation

MIN Function

MAX Function

AVG Function

SUM Function

COUNT Function

GROUP BY Statement

Count of Person by gender

HAVING Clause

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

JOINS

Consider a foreign key relationship between books and publisher.

Fetch publisher name for a book

Fetch books which have specific publisher

Regex

Related Post

Recent Posts

Як стати Software Engineer: моя історія

Useful Git commands

Useful ASCII tables

Conda cheatsheet

TOP-10 questions about Python

TOP-10 questions about Django

`Regex`