Django ORM

Oct. 6, 2021, 1:51 a.m.

One of the most powerful features of Django is its Object-Relational Mapper (ORM), which enables us to interact with database, like SQL. In fact, Django's ORM is just a pythonical way to create SQL to query and manipulate database and get results in a pythonic fashion. The main benefits of using Django ORM instead of SQL is a huge improvement in development speed, code maintenance, security and ease of development in general. In this blog we will take a look at jango ORM from beginner to advanced level but first let's take a look at the models. We will use three models here where many to one and many to many relation exists-

To get all the data from a table-

Musician.objects.all()

Result-

We can limit the query for showing specific number of data-

Musician.objects.all()[0:3]

Result-

To get particular column or field data-

Musician.objects.values('first_name')

Result-

We can also get multiple column or field-

Musician.objects.values('first_name','last_name')

Result-

We can also return a list of column or field objects from the database-

Musician.objects.values_list('first_name', flat=True)

Result-

To return flat object instead of tuple-

Musician.objects.values_list('first_name', flat=True)

Result-

To return only list instead of queryset-

list(Musician.objects.values_list('id', flat=True))

Result-

['Mary', 'Michael', 'Meghan', 'Jessica', 'Jennifer', 'Samuel', 'Natasha', 'Jason', 'Alexander', 'Teresa', 'Russell', 'Tammy', 'Kelli', 'William', 'Sara']

Get data using Primary key-

Musician.objects.get(pk=62)

Result-

<Musician: Sara Carr>

We can get data using the field which is acting as a primary key on the same way.

To exclude results from the querysets-

Musician.objects.exclude(id=74)

Result-

Retrieving Objects with Filters

Return the whole row filtering a column value-

Musician.objects.filter(first_name='Samuel')

Result-

This works in the same way if we provide a lookup type like underscore exact

Musician.objects.filter(first_name__exact='samuel')

Result-

If we want to search in a case insensitive way-

Musician.objects.filter(first_name__iexact='samuel')

Result-

We can filter by multiple column-

Musician.objects.filter(first_name__iexact='samuel', last_name__iexact='miles')

Result-

We can also filter by multiple value-

Musician.objects.filter(id__in=[71,72,73])

Result-

'__startwith' to retrieve the rows starts with the given string

Musician.objects.filter(first_name__startswith='sa')

Result-

'__endswith' to retrieve the rows ends with the given string

Musician.objects.filter(first_name__endswith='sa')

Result-

'contains' keyword exactly same as %like% keyword

Musician.objects.filter(first_name__contains='ss')

Result-

There is an case insensitivie version of contains keyword-

Musician.objects.filter(first_name__icontains='ss')

Relational operators

gt -Greater than.

gte -Greater than or equal to.

lt -Less than.

lte -Less than or equal to.

Album.objects.filter(release_date__lte= datetime.date(2021, 9, 25))

Result-

<QuerySet [<Album: It Was Too Good To Be True>, <Album: Underestimated By Many>, <Album: My Hidden Pain>, <Album: You Were A True Friend>, <Album: Love has no ending>, <Album: Suffering Is Optional>, <Album: the little things>, <Album: The Grand Creator>, <Album: Fearing God>, <Album: Holy Life>, <Album: Plight of a widow>, <Album: Be Strong & Courageous>, <Album: Talents>, <Album: Behind The Scene Mate/s>, <Album: Justice To All>, <Album: Typical of Friends>, <Album: The Beauty Of Life>, <Album: Disconnected>, <Album: A Poison To My Heart>, <Album: A dance to my sorrows>, '...(remaining elements truncated)...']>

Boolean

exists() method is used to check the result of a query. If the query result exists it returns True otherwise False-

Album.objects.filter(release_date__gte= datetime.date(2021, 9, 25)).exists()

Result-

False

Aggregate

Aggregate functions- Sum, Avg, Max, Min

Musician.objects.aggregate(Avg('age'))

Result-

{'age__avg': 71.73333333333333}

We can also different arithemtic operations here-

such as difference of age between max age musician and min age musician-

Musician.objects.aggregate(age_diff=(Max('age')-Min('age')))

Result-

{'age_diff': 93}

These aggregate functions produce querysets for the whole database.

To generate aggregiate for each item in the query set we have to use annotate method-

To get number of album for each Musician-

from django.db.models import Count

album_count=Musician.objects.annotate(num_album=Count('album'))

album_count[0].num_album

Result-

values() clause is used to constrain the columns that are returned in the result set.This also groups the unique values and return them. This means whenever we will use values() with annotate it will not return result for each object rather it willl group the unique objects and return the queryset.

Musician.objects.values('first_name').annotate(num_album=Count('album'))

Result-

We can also use order_by to customize the queryset according to any alias defined as part of an annotate() clause in the query.

Musician.objects.values('first_name').annotate(num_album=Count('album')).order_by('-num_album')

Result-

If we want to count number of Musician per concert-

concert_count= Concert.objects.annotate(num_musician=Count('artists'))
>>> concert_count[0].num_musician

Result-

If we wanted to get the average number of Musician per concert-

Concert.objects.annotate(num_musician=Count('artists')).aggregate(Avg('num_musician'))

Result-

{'num_musician__avg': 6.105263157894737}

F expression()

An F() object represents the value of a model field, transformed value of a model field, or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

Let’s try this with an example. Normally, one might do something like this:

reporter = Reporters.objects.get(name='Tintin')

reporter.stories_filed += 1

reporter.save()

Here, we have pulled the value of reporter.stories_filed from the database into memory and manipulated it using familiar Python operators, and then saved the object back to the database. But instead we could also have done:

from django.db.models import F

reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()

If you want to compare the value of a model field with another field on the same model F() is used

Musician.objects.annotate(new_age=F('age')*2).values('age','new_age')

Result-

Q expression()

When we use keyword arguments queries like queries with filter() and get(), if we use comma separated multiple argument, those condition works like AND operator. If we want to use more complex queries like OR, we need to use Q object. It works like F object which represents the value of a model field or annotation. They make it possible to define and reuse conditions, and combine them using operators.

from django.db.models import Q
Musician.objects.filter(Q(first_name__contains='ss')|Q(age__gt=45))

Result-

Foreignkey Related queries

For many to one it's pretty straigh forward-

album1=Album.objects.get(id=89)
album1.artist

Result-

<Musician: Amber Alvarez>

Everytime we execute album1.artist, it will hit the database to get the related artist object. If we want to select additional related objects when a query initially executes we can use select_related(). One uses select_related when the object that you’re going to be selecting is a single object, so OneToOneField or a ForeignKey forward relationship. Select_related works by creating an SQL join and including the fields of the related object in the SELECT statement.

album1= Album.objects.select_related('artist').get(id=89)
album1.artist

Result-

<Musician: Amber Alvarez>

This will not hit the database again.

For reverse relationship means one to many-

musician1= Musician.objects.get(id=91)
musician1.album_set.all()

Result-

musician1.album_set.filter(release_date__lte=datetime.date(2021,9,1))

Result-

Many to Many Related Queries

For forward relationship its the same as Foreignkey relationship-

concert1=Concert.objects.get(id=76)
concert1.artists.all()

Result-

For backward relationship-

musician1.concert_set.all()

Result-

We can use prefetch_related() in terms of many to many and reverse foreignkey relationship because it does a separate lookup for each relationship, and does the ‘joining’ in Python-

all_concert = Concert.objects.all().prefetch_related('artists')
all_concert[0].artists.values()

Result-

Another example for reverse foreignkey relationship-

musician1= Musician.objects.prefetch_related().get(id=91)
musician1.album_set.values()

Result-

Data Insertion

Data can be inserted two ways into a table-

Create a model object and save it later.
user model manager and create() object.

new_musician=Musician(first_name='Micheal', last_name='Jackson', age=46)
new_musician.save()

new_musician1=Musician.objects.create(first_name='Sonu', last_name='Nigam', age=49)

Insert data to a table which has foreignkey associated with it-

new_album=Album(name='Bad', release_date=datetime.date(2021,1,31))
musician=Musician.objects.get(id=92)
new_album.artist=musician
new_album.save()

Inseting data to a many to many field works differently. we need to use the add method-

concert=Concert.objects.get(id=76)
concert.artists.add(musician)

We can also add multiple musician object here like-

concert.artists.add(musician1, musician2)

Data Deletion

The delete() method is used to delete any or all object in database-

musician=Musician.objects.get(id=92)
musician.delete()

If we want to delete all the data in a table-

Musician.objects.all().delete()

Removing the Foreignkey data

we can use remove() method to detach a related object from a model-

album=Album.objects.get(id=90)
album.artist=None

In reverse relationship, if we wanted to remove a object from a particular related object-

musician=Musician.objects.get(id=92)
album=Album.objects.get(id=90)

musician.album_set.remove(album)

and for bulk clear-

musician.album_set.clear()

Note: These queries are valid for foreignkey objects if null=True. For our Album table, it is invalid.

Removing many to many data

We ca remove a many to many related object in the same ways as foreignkey-

concert=Concert.objects.prefetch_related.get(id=69)
artist=Musician.objects.get(id=79)

artist.concert_set.remove(concert)