Convert SQL to Django ORM


Django Models - Python Django Tutorials
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 :-


from django.db import models
# Create your models here.
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

id | first_name | last_name | age | text
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

‘__isnull’ is used to filter the null values in the Django ORM. It accepts True or False.

>>>example.objects.filter(age__isnull=True).values('id','age')
<QuerySet []>
>>>example.objects.filter(age__isnull=False).values('id','age')
<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.

>>>example.objects.filter(age__isnull=True).values('id','age').exists()
False
>>> User.objects.filter(age__isnull=False).values('id','age').exists()
True

SQL ‘LIKE’ with Django ORM

>>>example.objects.filter(city__contains='city').values('id','city')
<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.

>>>example.objects.filter(city__exact='text').values('id','text')
<QuerySet []>
>>>example.objects.filter(text__startswith='text').values('id','text')
<QuerySet [{'id': 1, 'text': 'new text'}, {'id': 2, 'text': 'world say hello'}, {'id': 3, 'text': 'new one'}]>

>>>example.objects.filter(text__startswith='text').values('id','text')
<QuerySet []>

>>>>example.objects.filter(text__endswith='2').values('id','text')
<QuerySet [{'id': 2, 'text': 'world say hello'}]>

Relational operators

Basically we used some relational operators mostly in the django projects.
·         gt :-Greater than.
·         gte :-Greater than or equal to.
·         lt :-Less than.
·         lte :-Less than or equal to.

>>>example.objects.filter(age__gt=20).values('id','age')
<QuerySet [{'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>

>>>example.objects.filter(age__gte=20).values('id','age')
<QuerySet [{'id': 1, 'age': 50}, {'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>

>>>example.objects.filter(age__lt=25).values('id','age')
<QuerySet [{'id': 1, 'age': 50}]>

>>>example.objects.filter(age__lte=25).values('id','age')
<QuerySet [{'id': 1, 'age': 50}, {'id': 2, 'age': 25}]>

Select some columns of the table

In Django ORM values() method is used to select a few column values of the table :-

>>>example.objects.values('id')
<QuerySet [{'id': 1}, {'id': 2}, {'id': 3}]>
>>>example.objects.values('id','first_name','last_name')
<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

‘__in’ is used to filter on multiple values.

>>>example.objects.filter(id__in=[1,2])
<QuerySet [<User: User object (1)>, <User: User object (2)>]>

exclude()

Excludes objects from the queryset which match with the lookup parameters.

>>>example.objects.exclude(id=1)
<QuerySet [<User: User object (2)>, <User: User object (3)>]>

Rename objects ‘As’ in the SQL

The extra() method is used to rename columns in the ORM.

>>>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'}]>
In this ORM, I’ve renamed first_name to FirstName and last_name to LastName.

Group By and Order By

The aggregate() function is used to perform aggregation operations like sum, average, min, max, etc.

>>>example.objects.aggregate(Sum('age'))
{'age__sum': 75}
>>>example.objects.aggregate(Avg('age'))
{'age__avg': 25.0}
>>>example.objects.aggregate(Max('age'))
{'age__max': 50}
>>>example.objects.aggregate(Min('age'))
{'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.

>>>example.objects.values('text').annotate(Sum('age'))
<QuerySet [{'text': 'new text', 'age__sum': 50}, {'city': 'city2', 'world say hello': 25}, {'city': 'new one', 'age__sum': 35}]>
Also, we can sort a queryset using order_by().

>>>example.objects.values('text').annotate(total_age=Sum('age')).order_by('-total_age')
<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}]>
In the output, new text with a total_age 50 does not exist because of the filter.

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.

AND
‘&’ is used for OR operation between 2 Q expressions.

>>>example.objects.filter(Q(text='new text') & Q(age=25)).values('id','text','age')
<QuerySet []>

>>>example.objects.filter(Q(text='new text') & Q(age=20)).values('id','text','age')
<QuerySet [{'id': 1, 'text': 'new text', 'age': 50}]>
This ORM will find all users with text=new text and age= 25.

OR
‘|’ is used for OR operation between 2 Q expressions.
>>>example.objects.filter(Q(text='new text') | Q(age=25)).values('id','text','age')
<QuerySet [{'id': 1, 'text': 'new text', 'age': 50}, {'id': 2, 'text': 'world say hello', 'age': 25}]>
This ORM will find all users having text=new text or age= 25.

NOT
‘~’ is used for OR operation between 2 Q expressions.
>>>example.objects.filter(Q(text='new text') & ~Q(age=50)).values('id','text','age')
<QuerySet []>

>>>example.objects.filter(Q(text='new text') & ~Q(age=50)).values('id','text','age')
<QuerySet [{'id': 2, 'text': 'world say hello', 'age': 25}]>
The query will find all users with text=worlds say hello and age is other than 50.

F() Expressions

In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.

>>>example.objects.annotate(new_age=F('age')*2).values('id','age','new_age')
<QuerySet [{'id': 1, 'age': 50, 'new_age': 100}, {'id': 2, 'age': 25, 'new_age': 50}, {'id': 3, 'age': 35, 'new_age': 70}]>
We can also use F() expression in the filter.

>>>example.objects.filter(id__lt=F('age')).values('id','age')
<QuerySet [{'id': 1, 'age': 50}, {'id': 2, 'age': 25}, {'id': 3, 'age': 35}]>

>>>example.objects.filter(id__gt=F('age')).values('id','age')
<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).

For example, multiple age column value with 2.

>>>example.objects.update(age=F('age')*2)
3
>>>example.objects.values('id','age')
<QuerySet [{'id': 1, 'age': 100}, {'id': 2, 'age': 50}, {'id': 3, 'age': 70}]>
We can update a single object also.

>>>value=example.objects.get(id=1)
>>>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.

Delete a single object
>>>value=example.objects.get(id=1)
>>>value.delete()
(1, {'django_orms.example': 1})
Delete multiple objects
>>>example.objects.all().delete()
(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:
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:
SELECT name, age
FROM Person;
Django:
example.objects.only('name', 'age')
Fetch distinct rows
SQL:
SELECT DISTINCT name, age
FROM Person;
Django:
example.objects.values('name', 'age').distinct()
Fetch specific number of rows
SQL:
SELECT *
FROM Person
LIMIT 10;
Django:
example.objects.all()[:10]
LIMIT AND OFFSET keywords
SQL:
SELECT *
FROM Person
OFFSET 5
LIMIT 5;
Django:
example.objects.all()[5:10]

WHERE Clause

Filter by single column
SQL:
SELECT *
FROM Person
WHERE id = 1;
Django:
example.objects.filter(id=1)
Filter by comparison operators
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:
SELECT *
FROM Person
WHERE age BETWEEN 10 AND 20;
Django:
example.objects.filter(age__range=(10, 20))
LIKE operator
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:
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:
SELECT *
FROM example
order by age;
Django:
example.objects.order_by('age')
Descending Order
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:
UPDATE example
SET age = 25
WHERE id = 1;
Django:
example= example.objects.get(id=1)
example.age = 25
example.save()
Update multiple rows
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:
DELETE FROM example;
Django:
example.objects.all().delete()
Delete specific rows
SQL:
DELETE FROM example
WHERE age <20;
Django:
example.objects.filter(age__lt=10).delete()

Aggregation

MIN Function
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:
SELECT MAX(age)
FROM example;
Django:
>>> from django.db.models import Max
>>>example.objects.all().aggregate(Max('age'))
{'age__max': 100}
AVG Function
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:
SELECT SUM(age)
FROM example;
Django:
>>> from django.db.models import Sum
>>>example.objects.all().aggregate(Sum('age'))
{'age__sum': 5050}
COUNT Function
SQL:
SELECT COUNT(*)
FROM example;
Django:
example.objects.count()

GROUP BY Statement

Count of Person by gender
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:
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:
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:
SELECT *
FROM Book
WHERE Book.publisher_id = 1;
Django:
college = college.objects.prefetch_related('book_set').get(id=1)
books = college.book_set.all()


Reactions

Post a Comment

0 Comments