Django ORM Querysets

In this tutorial, you will be understanding about Django ORM(Object Relational Mapping)

Django ORM:

Here comes the main essence of the Django Framework that makes interacting with relational databases much easy without any SQL queries.

How?

Django ORM is Object-relational Mapping Layer that provides a database abstraction API with which we can create, update, retrieve, and delete objects (I mean the data in the database).

Pros of ORM ?

As we are in the age of demand of full-stack developers who has to take care of everything from top to toe in application development. Mostly the Application developers are not experts in writing the SQL queries like Database developers, where you can forget about the database SQL queries, simply write the object language using the database-abstraction API.

One more thing is that you don’t require to change your code by changing your database from MySQL to SQLite or PostgreSQL.

cons:

If you are already the master of SQL, then you can avoid to write your high performing SQL queries. Learning ORM again doesn’t make many add-ons for an expert in SQL.

QuerySets:

Create Objects:

Creating objects is nothing inserting the data into the database through a database abstraction API.

First, you need to connect to the shell by giving below command.

python manage.py shell

Below is the way how you can insert the data.

(learndjango) C:\Users\admin\learndjango\lms>python manage.py shell
Python 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from coursera.models import Author, Course, Subscription
>>> from django.contrib.auth.models import User
>>> data = Course.objects.all()
>>> data
<QuerySet [<Course: Python>, <Course: Machine Learning>, <Course: AI>]>
 
>>> new_data=Course.objects.create(course_name='Django', course_topics='orm, querysets', author_desc='trainer', course_desc='django framework', course_fee=100, course_duration='30', author_id=1)
>>> data = Course.objects.all()
>>> data
<QuerySet [<Course: Python>, <Course: Machine Learning>, <Course: AI>, <Course: Django>]>
>>> new_data.save()

Note: Actually save() method is not required when you are working with create() method.

Retrieve Objects:

Retrieving the objects is nothing but the selection and viewing of the data.

Below is example:

(learndjango) C:\Users\admin\learndjango\lms>python manage.py shell
Python 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from coursera.models import Author, Course, Subscription
>>> from django.contrib.auth.models import User
>>> Course.objects.values_list()
<QuerySet [(1, 'Python', 'Lists, set, dictionary, tuple', 'Ravi', 'artificial Intelligence', 100, 30, 3, datetime.datetime(2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>), datetim
e.datetime(2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>)), (2, 'Machine Learning', 'regression, classification', 'trainer', 'artificial Intelligence', 100, 30, 2, datetime.datet
ime(2020, 4, 7, 17, 51, 26, 405086, tzinfo=<UTC>), datetime.datetime(2020, 4, 7, 17, 52, 5, 805691, tzinfo=<UTC>)), (3, 'AI', 'data cleaning, Opencv', 'trainer', 'artificial Int
elligence', 100, 30, 1, datetime.datetime(2020, 4, 7, 17, 51, 57, 555565, tzinfo=<UTC>), datetime.datetime(2020, 4, 7, 17, 51, 57, 555565, tzinfo=<UTC>)), (4, 'Django', 'orm, qu
erysets', 'trainer', 'django framework', 100, 30, 1, datetime.datetime(2020, 4, 7, 19, 10, 41, 25624, tzinfo=<UTC>), datetime.datetime(2020, 4, 7, 19, 11, 27, 338836, tzinfo=<UT
C>))]>

If you want to check the relevant SQL query which is used in the background for a database.

>>> query=Course.objects.values_list()
>>> sql=Course.objects.values_list()
>>> str(sql.query)
'SELECT "coursera_course"."id", "coursera_course"."course_name", "coursera_course"."course_topics", "coursera_course"."author_desc", "coursera_course"."course_desc", "coursera_c
ourse"."course_fee", "coursera_course"."course_duration", "coursera_course"."author_id", "coursera_course"."created_at", "coursera_course"."updated_at" FROM "coursera_course"'
>>>

Update the Objects:

Update the Objects refers to the Updating the row or a field in the table.

Below is the Example

>>> data = Course.objects.all()
>>> data
<QuerySet [<Course: Python>, <Course: Machine Learning>, <Course: AI>, <Course: Django>]>
>>> data = Course.objects.get(course_name='Django')
>>> data
<Course: Django>
>>> data.course_name='Java'
>>> data.save()
>>> data1 = Course.objects.all()
>>> data1
<QuerySet [<Course: Python>, <Course: Machine Learning>, <Course: AI>, <Course: Java>]>

Filter the Objects:

filter() method is nothing but the where condition. In the below example, we can understand that the results are filtered based on the course_duration=30 and then for loop is applied to list all the results.

You can also filter by using multiple fields.

Below is the Example:

>>> d=Course.objects.filter(course_duration=30).values_list()
>>> for i in d:
...     print(i);
...
(1, 'Python', 'Lists, set, dictionary, tuple', 'Ravi', 'artificial Intelligence', 100, 30, 3, datetime.datetime(2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>), datetime.datetime(
2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>))
(2, 'Machine Learning', 'regression, classification', 'trainer', 'artificial Intelligence', 100, 30, 2, datetime.datetime(2020, 4, 7, 17, 51, 26, 405086, tzinfo=<UTC>), datetime
.datetime(2020, 4, 7, 17, 52, 5, 805691, tzinfo=<UTC>))
(3, 'AI', 'data cleaning, Opencv', 'trainer', 'artificial Intelligence', 100, 30, 1, datetime.datetime(2020, 4, 7, 17, 51, 57, 555565, tzinfo=<UTC>), datetime.datetime(2020, 4,
7, 17, 51, 57, 555565, tzinfo=<UTC>))
(4, 'Java', 'orm, querysets', 'trainer', 'django framework', 100, 30, 1, datetime.datetime(2020, 4, 7, 19, 10, 41, 25624, tzinfo=<UTC>), datetime.datetime(2020, 4, 7, 19, 38, 29
, 388750, tzinfo=<UTC>))

Exclude()

Exclude method will help us to exclude unwanted data from the filtered or retrieved data in your QuerySet. In the below example, I will exclude the course name = Java

Below is the example:

>>> d=Course.objects.filter(course_duration=30).exclude(course_name='Java').values_list()
>>> for i in d:
...     print(i);
...
(1, 'Python', 'Lists, set, dictionary, tuple', 'Ravi', 'artificial Intelligence', 100, 30, 3, datetime.datetime(2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>), datetime.datetime(
2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>))
(2, 'Machine Learning', 'regression, classification', 'trainer', 'artificial Intelligence', 100, 30, 2, datetime.datetime(2020, 4, 7, 17, 51, 26, 405086, tzinfo=<UTC>), datetime
.datetime(2020, 4, 7, 17, 52, 5, 805691, tzinfo=<UTC>))
(3, 'AI', 'data cleaning, Opencv', 'trainer', 'artificial Intelligence', 100, 30, 1, datetime.datetime(2020, 4, 7, 17, 51, 57, 555565, tzinfo=<UTC>), datetime.datetime(2020, 4,
7, 17, 51, 57, 555565, tzinfo=<UTC>))

Delete()

Delete method will help us to delete the objects. In the below example, I tried deleting the course name called Java.

Below is the example:

>>> d=Course.objects.get(id=4)
>>> d
<Course: Java>
>>> d.delete()
(1, {'coursera.Subscription': 0, 'coursera.Course': 1})

Orderby() :

Orderby method will help us to retrieve the objects in an order.

Below is the example:

>>> d=Course.objects.order_by('course_name').values_list()
>>> for i in d:
...     print(i);
...
(3, 'AI', 'data cleaning, Opencv', 'trainer', 'artificial Intelligence', 100, 30, 1, datetime.datetime(2020, 4, 7, 17, 51, 57, 555565, tzinfo=<UTC>), datetime.datetime(2020, 4,
7, 17, 51, 57, 555565, tzinfo=<UTC>))
(2, 'Machine Learning', 'regression, classification', 'trainer', 'artificial Intelligence', 100, 30, 2, datetime.datetime(2020, 4, 7, 17, 51, 26, 405086, tzinfo=<UTC>), datetime
.datetime(2020, 4, 7, 17, 52, 5, 805691, tzinfo=<UTC>))
(1, 'Python', 'Lists, set, dictionary, tuple', 'Ravi', 'artificial Intelligence', 100, 30, 3, datetime.datetime(2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>), datetime.datetime(
2020, 4, 7, 17, 50, 48, 111998, tzinfo=<UTC>))
>>> Course.objects.order_by('course_name')
<QuerySet [<Course: AI>, <Course: Machine Learning>, <Course: Python>]>