In this article ,We are going to learn how you will
use django ORM(Object Relation Mapper) to SQL or SQL to django ORM. Basically django
ORM used to SQL queries in Python
language. ORM is build for developers,which is very usefull and
easy to understand and because a developer is free from type biggest queries in
SQL queries.
At last We will describe
relation between django ORM AND SQL.
So let’s learn about Django ORM first,then we will
see convert in SQL queries.
DJANGO ORM
ORM stands
for Object Relation Mapper. Django ORM is a
easy and well known way to interact with the database. The Django ORM is an abstraction layer that allows us
to program our django projects with the database.
At the end, We will see the Django
ORM will convert all operations into SQL
statements. In this piece, We will learn ORM
of some common SQL queries.
Example of Models :-
class
example(models.Model):
first_name =
models.CharField(max_length=100)
last_name =
models.CharField(max_length=100)
age =
models.IntegerField()
text = models.CharField(max_length=255)
Iam created some objects of the User table use din
django model. Let’s start some commonly used Django ORMs,which use din the
biggest projects.
Data in the User table
1 | pappu | 1 | 50
| new text
2 | rocky | 2 | 25
| world say hello
3 | well | 3 | 35 | new one
Commonly used Django ORMs
Filter NULL values
<QuerySet []>
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25},
{'id': 3, 'age': 30}]>
exists()
The exists() method is used to
check the result of the query. Returns True
if the queryset contains any results, and False
if not.
False
True
SQL ‘LIKE’ with Django ORM
<QuerySet [{'id': 1, 'text': 'new text'}, {'id': 2,
'text': 'world say hello'}, {'id': 3, 'text': 'new one'}]>
In the above query, __contains is
used to search ‘text’ substring in the city column. Also, we can check the
exact match using __exact.
<QuerySet []>
<QuerySet [{'id': 1, 'text': 'new text'}, {'id': 2,
'text': 'world say hello'}, {'id': 3, 'text': 'new one'}]>
<QuerySet []>
<QuerySet [{'id': 2, 'text': 'world say hello'}]>
Relational operators
Basically we used some
relational operators mostly in the django projects.
<QuerySet
[{'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>
<QuerySet [{'id':
1, 'age': 50}, {'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>
<QuerySet
[{'id': 1, 'age': 50}]>
<QuerySet [{'id': 1, 'age': 50}, {'id': 2, 'age':
25}]>
Select some columns of the table
<QuerySet [{'id': 1}, {'id': 2}, {'id': 3}]>
<QuerySet [{'id': 1, 'first_name': 'pappu',
'last_name': '1'}, {'id': 2, 'first_name': 'rocky', 'last_name': '2'}, {'id':
3, 'first_name': 'well', 'last_name': '3'}]>
SQL ‘IN’ with Django ORM
<QuerySet [<User: User object (1)>, <User:
User object (2)>]>
exclude()
<QuerySet [<User: User object (2)>, <User:
User object (3)>]>
Rename objects ‘As’ in the
SQL
>>>example.objects.extra(select={'FirstName':'first_name','LastName':'last_name'}).values('FirstName','LastName')
<QuerySet [{'FirstName': 'pappu', 'LastName': '1'},
{'FirstName': 'rocky', 'LastName': '2'}, {'FirstName': 'well', 'LastName':
'3'}]>
Group By and Order By
The aggregate() function is used to
perform aggregation operations like sum, average, min, max, etc.
{'age__sum': 75}
{'age__avg': 25.0}
{'age__max': 50}
{'age__min': 20}
The aggregate() function works on
the whole dataset only. Use annotate()
instead of aggregate() if you want an average
age group by city.
<QuerySet [{'text': 'new text', 'age__sum': 50},
{'city': 'city2', 'world say hello': 25}, {'city': 'new one', 'age__sum':
35}]>
<QuerySet [{'text': 'new one', 'total_age': 35},
{'city': 'world say hello', 'total_age': 25}, {'city': 'new text', 'total_age':
50}]>
We coul rename the default name of
the aggregation function. Here, I have changed name total_age and used order_by
on total_age. ‘-total_age’ is used for descending order.
Usually, in the database, we use
the ‘HAVING’ clause with the group by queries. In the Django, we can use
filter() function.
>>>example.objects.values('text').annotate(total_age=Sum('age')).filter(total_age__gt=20).order_by('-total_age')
<QuerySet [{'text': 'new one', 'total_age': 35},
{'text': 'world say hello', 'total_age': 25}]>
Complex filters with Q
objects
Q objects are used for AND, OR and NOT
operations. Q objects provide complete control over the where clause of the
query.
<QuerySet []>
<QuerySet [{'id': 1, 'text': 'new text', 'age':
50}]>
<QuerySet [{'id': 1, 'text': 'new text', 'age': 50},
{'id': 2, 'text': 'world say hello', 'age': 25}]>
<QuerySet []>
<QuerySet [{'id': 2, 'text': 'world say hello', 'age':
25}]>
F() Expressions
In the Django
QuerySet API, F() expressions are used
to refer to model field values directly in the database.
<QuerySet [{'id': 1, 'age': 50, 'new_age': 100},
{'id': 2, 'age': 25, 'new_age': 50}, {'id': 3, 'age': 35, 'new_age': 70}]>
<QuerySet
[{'id': 1, 'age': 50}, {'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>
<QuerySet []>
Update values used in
django ORM
Performs an SQL update query for all the your fields, and returns the
number of rows matched (which may not be equal to the number of rows updated if
some rows already have the new value).
3
>>>example.objects.values('id','age')
<QuerySet [{'id': 1, 'age': 100}, {'id': 2, 'age':
50}, {'id': 3, 'age': 70}]>
>>>value.age
=100
>>>value.save()
Delete object
Performs an SQL delete query on all the rows in the queryset and returns the number of objects deleted and a
dictionary with the number of deletions per object type.
>>>value.delete()
(1, {'django_orms.example': 1})
(2, {'django_orms.example': 2})
By default, Django’s ForeignKey emulates the SQL
constraint ON
DELETE CASCADE— in other words, any objects with foreign keys
pointing at the objects to be deleted will be deleted along with them. We can
set the on_delete behavior in the models.
RELATION BETWEEN DJANGO ORM AND SQL
Here we told you about relation between SQL and Django ORM , we have to know how these things different and help us to understand them both and make easy to understand us and use in our django projects.
If you are migrating to Django from another MVC framework, chances are you already know SQL.
CREATE TABLE
Example (
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 in the
database or you could say django model.
class
example(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()
|
SELECT
Statement
Fetch all rows
SQL:
SQL:
SELECT *
FROM Person;
Django:
example =
example.objects.all()
for i in example:
print(i.name)
print(i.gender)
print(i.age)
Fetch specific columns
SQL:
SQL:
SELECT name, age
FROM Person;
Django:
example.objects.only('name', 'age')
Fetch distinct rows
SQL:
SQL:
SELECT DISTINCT
name, age
FROM Person;
Django:
example.objects.values('name', 'age').distinct()
Fetch specific number of
rows
SQL:
SQL:
SELECT *
FROM Person
LIMIT 10;
Django:
example.objects.all()[:10]
LIMIT AND OFFSET keywords
SQL:
SQL:
SELECT *
FROM Person
OFFSET 5
LIMIT 5;
Django:
example.objects.all()[5:10]
WHERE Clause
Filter by single column
SQL:
SQL:
SELECT *
FROM Person
WHERE id = 1;
Django:
example.objects.filter(id=1)
Filter by comparison
operators
SQL:
SQL:
WHERE age > 10;
WHERE age >= 10;
WHERE age < 10;
WHERE age <= 10;
WHERE age != 10;
Django:
example.objects.filter(age__gt=8)
example.objects.filter(age__gte=8)
example.objects.filter(age__lt=8)
example.objects.filter(age__lte=8)
example.objects.exclude(age=8)
BETWEEN Clause
SQL:
SQL:
SELECT *
FROM Person
WHERE age BETWEEN 10 AND 20;
Django:
example.objects.filter(age__range=(10, 20))
LIKE operator
SQL:
SQL:
WHERE name like
'%P%RAM%';
WHERE name like
binary '%P%RAM%';
WHERE name like
'%P%RAM%';
WHERE name like
binary '%P%RAM%';
WHERE name like
'%P%RAM%';
WHERE name like binary '%P%RAM%';
Django:
example.objects.filter(name__icontains='RAM')
example.objects.filter(name__contains='RAM')
example.objects.filter(name__istartswith='RAM')
example.objects.filter(name__startswith='RAM')
example.objects.filter(name__iendswith='RAM')
example.objects.filter(name__endswith='RAM')
IN operator
SQL:
SQL:
WHERE id in (1, 2);
Django:
example.objects.filter(id__in=[1, 2])
AND, OR
and NOT Operators
SQL:
WHERE gender='male' AND age > 25;
Django:
example.objects.filter(gender='male', age__gt=25)
SQL:
WHERE gender='male' OR age > 25;
Django:
from
django.db.models import Q
example.objects.filter(Q(gender='male') | Q(age__gt=25))
SQL:
WHERE NOT gender='male';
Django:
example.objects.exclude(gender='male')
NULL Values
SQL:
WHERE age is NULL;
WHERE age is NOT NULL;
Django:
example.objects.filter(age__isnull=True)
example.objects.filter(age__isnull=False)
--------------------OR---------------------
example.objects.filter(age=None)
example.objects.exclude(age=None)
ORDER
BY Keyword
Ascending Order
SQL:
SQL:
SELECT *
FROM example
order by age;
Django:
example.objects.order_by('age')
Descending Order
SQL:
SQL:
SELECT *
FROM example
ORDER BY age DESC;
Django:
example.objects.order_by('-age')
INSERT
INTO Statement
SQL:
INSERT INTO example
VALUES ('PAPPU', '150', 'male');
Django:
example.objects.create(name='PAPPU', age=150,
gender='male)
UPDATE Statement
Update single row
SQL:
SQL:
UPDATE example
SET age = 25
WHERE id = 1;
Django:
example= example.objects.get(id=1)
example.age = 25
example.save()
Update multiple rows
SQL:
SQL:
UPDATE example
SET age = age * 2;
Django:
from
django.db.models import F
example.objects.update(age=F('age')*2)
DELETE Statement
Delete all rows
SQL:
SQL:
DELETE FROM example;
Django:
example.objects.all().delete()
Delete specific rows
SQL:
SQL:
DELETE FROM example
WHERE age <20;
Django:
example.objects.filter(age__lt=10).delete()
Aggregation
MIN Function
SQL:
SQL:
SELECT MIN(age)
FROM example;
Django:
>>> from django.db.models
import Min
>>>example.objects.all().aggregate(Min('age'))
{'age__min': 0}
MAX Function
SQL:
SQL:
SELECT MAX(age)
FROM example;
Django:
>>> from
django.db.models import Max
>>>example.objects.all().aggregate(Max('age'))
{'age__max': 100}
AVG Function
SQL:
SQL:
SELECT AVG(age)
FROM example;
Django:
>>> from
django.db.models import Avg
>>>example.objects.all().aggregate(Avg('age'))
{'age__avg': 50}
SUM Function
SQL:
SQL:
SELECT SUM(age)
FROM example;
Django:
>>> from
django.db.models import Sum
>>>example.objects.all().aggregate(Sum('age'))
{'age__sum': 5050}
COUNT Function
SQL:
SQL:
SELECT COUNT(*)
FROM example;
Django:
example.objects.count()
GROUP
BY Statement
Count of Person by gender
SQL:
SQL:
SELECT gender,
COUNT(*) as count
FROM example
GROUP BY gender;
Django:
example.objects.values('gender').annotate(count=Count('gender'))
HAVING Clause
Count of Person by gender if
number of person is greater than 1
SQL:
SQL:
SELECT gender,
COUNT('gender') as count
FROM example
GROUP BY gender
HAVING count > 1;
Django:
example.objects.annotate(count=Count('gender'))
.values('gender',
'count')
.filter(count__gt=1)
JOINS
Consider a foreign key relationship between books
and college.
class
Publisher(models.Model):
name =
models.CharField(max_length=100)
class college(models.Model):
college = models.ForeignKey(college,
on_delete=models.CASCADE)
Fetch college name for a
book
SQL:
SQL:
SELECT name
FROM Book
LEFT JOIN college
ON Book.college_id
= college.id
WHERE Book.id=1;
Django:
book =
Book.objects.select_related('college').get(id=1)
book.college.name
Fetch books which have
specific publisher
SQL:
SQL:
SELECT *
FROM Book
WHERE Book.publisher_id = 1;
Django:
college = college.objects.prefetch_related('book_set').get(id=1)
books = college.book_set.all()
0 Comments