Feb. 21, 2021, 10:51 a.m.
Top Django Querysets, How to filter records | Fetching data from database efficiently - Django
Fetching data from the database should be as fast as possible. It makes the server response time fast (i.e minimum). For this purpose QuerySets plays an important role. So in this article, I am going to explain how you can fetch data efficiently from database using querysets.
Making Some models To keep the things simple i am taking an example of some model classes which are described below :
#In models.py
from django.db import models
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __str__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=200)
email = models.EmailField()
def __str__(self):
return self.name
class Entry(models.Model):
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
authors = models.ManyToManyField(Author)
def __str__(self):
return self.headline
from django.db import models
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __str__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=200)
email = models.EmailField()
def __str__(self):
return self.name
class Entry(models.Model):
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
authors = models.ManyToManyField(Author)
def __str__(self):
return self.headline
Basic Querysets Here are 2 basic querysets which are generally used when intracting with database :
1.) all() :
Syntax :
blogs=Blog.objects.all()
blogs=list(blogs)
#Output : list of all blog's objects
all() function will return all the records from the table or model class. After extracting data by this queryset you can filter that data further also.
blogs=list(blogs)
#Output : list of all blog's objects
2.) get() :
Syntax :
blog=Blog.objects.get(id=69)
#Output : Single object whose id is equal to 69.
#Output : Single object whose id is equal to 69.
If there are no results that matches the query, get() will raise a exception i.e DoesNotExist . Similarly, Django will complain if more than one item matches the get() query. In this case, it will raise MultipleObjectsReturned. And it will be returned when a parameter other than id is used because id is unique, thus no MultipleObjectsReturned exception will be returned if we use id in get().
filter() and exclude() The concept of filter and exclude comes when we have to filter data/objects/files from a table/database. There are several parameters which can be passed in filter() & exclude() functions. Here are some examples :
Objects_of_2006=Entry.objects.filter(pub_date__year=2006)
The above queryset will return all the blogs which are created in 2006. pub_date__year=2006 (double under score) will filter all the blogs which have 2006 in their pub_date field.
q1 = Entry.objects.filter(headline__startswith="What")
q2 = q1.exclude(pub_date__gte=datetime.date.today())
q3 = q1.filter(pub_date__gte=datetime.date.today())
q2 = q1.exclude(pub_date__gte=datetime.date.today())
q3 = q1.filter(pub_date__gte=datetime.date.today())
All of the 3 QuerySets shown above are different. The 1st is a base QuerySet containing all entries that contain a headline starting with “What”. The 2nd is a sub-set of the 1st queryset, with an additional criteria that excludes records whose pub_date is today or in the future. The 3rd is a sub-set of the 1st, with an additional criteria which include only the records whose pub_date is today or in the future. The initial QuerySet (i.e q1) remains unaffected in this whole process. Chaining filters The result of a queryset is again also a queryset. So yes, it is possible to chain the querysets. An example is shown below :
>>> Entry.objects.filter(headline__startswith='What').exclude(pub_date__gte=datetime.date.today()).filter( pub_date__gte=datetime.date(2005, 1, 30))
Limiting QuerySets We can limit the query execution process by the help of Python’s array-slicing syntax. For this purpose you can do something like this :
>>> Entry.objects.all()[:5]
#output : returns the first 5 objects
>>> Entry.objects.all()[5:10]
#output : returns the 6th to 10th objects
>>> Entry.objects.all()[:10:2]
#output : returns 5 objects from 1st to 10th objects with a step of 2
Thus, limiting the queryset execution process is also efficient if you have millions of records in your database tables. It reduces server response time which makes the website responsive.
Field lookups
Basic lookups keyword arguments take the form field__lookuptype=value.#output : returns the first 5 objects
>>> Entry.objects.all()[5:10]
#output : returns the 6th to 10th objects
>>> Entry.objects.all()[:10:2]
#output : returns 5 objects from 1st to 10th objects with a step of 2
For example:
Entry.objects.filter(pub_date__lte='2006-01-01')
The above queryset will return all the records with the field pub_date whose values are less than or equal to '2006-01-01'.
exact
Entry.objects.get(headline__exact="Cat bites dog")
Output : An exact match with the headline field whose value is "Cat bites dog" will be returned. iexact
Blog.objects.get(name__iexact="beatles blog")
Output : A case-insensitive match exact match with the name field, whose value is "beatles blog" will be returned.
Contains contains is used for a Case-sensitive containment test. For example :
Entry.objects.filter(headline__contains='is going to dead')
Output : All the records having 'is going to dead' in their headline field will be returned.
I my personal experience this is the most used queryset. But it is not limiting to this, it depends on your reqirement obviously. Hence these were the top and important as well querysets you should know.
Some recent posts
How to deploy a django website on pythonanywhere | django Web app deploying...
Complete process of changing database from SQLite to MySQL - How to migrat...
" How to download a file in django | Making a large file downloadable from ...
How to use proxy in python requests | http & https proxies - Scraping...
Top Django Querysets, How to filter records | Fetching data from database e...
How to change base url or domain in sitemap - Django ...
How to Make a Website - Everything you should know about web development...
What is Javascript - Features and usages ...
Top 5 Interview Questions : Tips for HR round ...
How to get job in IT : Perfect resume guide for IT job ...
View all...
Popular Posts
How to compress image before saving to DBMigrating from SQLite to MySQL in Django
Choosing correct M.2 SSD
How to deploy a web app on pythonanywhere
5 Steps to create sitemap in Django
How to send mails automatically in Django
Handling arguments in django urls
Media and Static configuration in Django
Language Translation & detection using python
React Js vs Angular Js
5 Tips to improve Programming Logics