Hey geek, You have come a long way with us. Till now we were working with django models. We learned how to create models and how to create objects from admin panel. We will discuss how to Create, Update & Delete objects from frontend later. Now we will see how to fetch & filter data from the database.

There are several ways of retrieving data from database. We will execute the following methods:

  • All objects
  • Only One Object
  • A Set of Specific Objects
  • Ordering Objects

We have created some models which we have described in our previous tutorials. Now we will fetch data from those model. Here we are using 'windows powershell'. To run python interactive console use the command:

python manage.py shell

(env) PS E:STUDYPython Bloging MaterialsDjangoprojectmy_project> python manage.py shell
Python 3.7.9 (tags/v3.7.9:13c94747c7, Aug 17 2020, 18:58:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>

all()

Now we will import our Person model & will fetch all the objects of person model using all() method.

from first_app.models import Person 
>>>queryset = Person.objects.all() 
>>>queryset <QuerySet [<Person: Inayra Mutahharah>, <Person: Zaira Anaya>, <Person: Zahra Ibnat>]>

Do you want to know what was the query ran behind the scene?

>>> print(queryset.query) 
SELECT "first_app_person"."id","first_app_person"."first_name", "first_app_person"."last_name", "first_app_person"."email", "first_app_person"."gender" FROM "first_app_person"  

get()

We have seen how to fetch all objects of a table. Now we will fetch a single object using get. we will have to pass the unique key to specify the object. Remember get() doesn't returns querysets.

>>>one_person = Person.objects.get(id=1) 
>>>one_person 
<Person: InayraMutahharah> 
>>>  
>>>one_person.email 
'inayra@gmail.com' 
>>> 

we have fetched all the data related to the person_id = 1. Now, as each "Person" object has a 'PersonsProfile'. Can we access that object too!! Let's try it...

>>>one_person.personsprofile.phone_no
2222222

See, It works. 'phone_no' is an attribute of 'PersonsProfile' objects. We can access because the models are related. We can see that the "Car" model is realted with "Person" model by "ManyToManyField". Let's find out all cars owned by "Person Object - 2"

>>>one_person = Person.objects.get(id=2) >>>one_person.car_set.all() 
<QuerySet [<Car: AUDI>, <Car: FERRARI>, <Car: BMW>]>

filter()

filter() method filters objects based on some logical condition, Multiple parameters can be passed in this function. 

>>>queryset = Person.objects.filter(pk__gt = 1)
>>>queryset
<QuerySet [<Person: Zaira Anaya>, <Person: Zahra Ibnat>]>
>>>
>>>queryset = Person.objects.filter(first_name__startswith = 'Z')
>>>queryset
<QuerySet [<Person: Zaira Anaya>, <Person: Zahra Ibnat>]>
  

Here we told the filter() method to fetch all the objects whose primary_key(pk) is greater than(gt) 1 and in our second query we have filtered out those persons whose 'first_name' starts with 'Z'.

querysets are iterable. We can iterate over them...

>>> for p in queryset:
...     print(p.first_name)
...
Zaira
Zahra

order_by()

This method helps us to order objects in terms of 'id' or any other field. Here we will use 'id'. To sort the objects in reverse order we used '-' operator. If you want them in ascending order don't use '-' negative sign.

>>>queryset.order_by('-id')
<QuerySet [<Person: Zahra Ibnat>, <Person: Zaira Anaya>]>
>>>queryset = Person.objects.filter().order_by('-id')
>>>queryset
<QuerySet [<Person: Zahra Ibnat>, <Person: Zaira Anaya>, <Person: Inayra Mutahharah>]>

We can use '?' sign to fetch data randomly.

Limiting objects.

When we use filter(), it brings all the objects which satisfy the given requirements. Sometimes we don't need all the objects. Suppose, In a blog site we want 5 related blogs that will be shown on the page. But there can be more than 5 related blogs. In cases like that we will have to limit the objects. As queryset is an iterable object, We can use slicing... 

>>>queryset = Person.objects.filter()[:1]
>>>queryset
<QuerySet [Person: Inayra Mutahharah>]>

exclude()

exclude() is used keep objects out of the queryset. Suppose we want all the persons without them whose "first_name" is "Zaira"

>>>queryset = Person.objects.filter().exclude(first_name = 'Zaira')
>>>queryset
<QuerySet [<Person: Inayra Mutahharah>, <Person: Zahra Ibnat>]>

count()

It counts the number of objects that are retrieved.

>>>queryset = Person.objects.filter().exclude(first_name = 'Zaira').count()
>>>queryset
2

values()

This method returns dictionaries of values rather than querysets. The values() method takes optional positional arguments, *fields, which specify field names.

>>>queryset = Person.objects.filter().values('first_name')
>>>queryset
<QuerySet [{'first_name': 'Inayra'}, {'first_name': 'Zaira'}, {'first_name': 'Zahra'}]>

>>>queryset = Person.objects.values('email')
>>>queryset
<QuerySet[{'email':'inayra@gmail.com'},{'email':'zahra@gmail.com'},{'email': 'zaira@gmail.com'}]>

You can also see the only() & values_list() method.

annotate()

is used to  summarize every object.. When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.  

Here we will find the " number_of_cars " per person.

>>> from django.db.models import Count
>>> persons = Person.objects.annotate(number_of_cars=Count("car"))
>>>persons
<QuerySet [<Person: Inayra Mutahharah>, <Person: Zaira Anaya>, <Person: Zahra Ibnat>]>

Now, Every person has an attribute called "number_of_cars".

>>>persons[0].number_of_cars
1
>>>persons[1].number_of_cars
3

aggregate()

Sometimes we need to get aggregated values from theobjects. Aggregation can be done using functions like Sum, Avg, Min, Max. Let's add a field named 'Price' in the "Car" model. Then we will see some examples.

>>> from first_app.models import Car
>>> from django.db.models import Sum, Avg, Min, Max
>>> total_price_of_cars = Car.objects.aggregate(Sum('price'))
>>> total_price_of_cars
{'price__sum': 560000}
>>> average_price_of_cars = Car.objects.aggregate(Avg('price'))
>>> average_price_of_cars
{'price__avg': 140000.0}
>>> minimum_price = Car.objects.aggregate(Min('price'))
>>> minimum_price.+++-
{'price__min': 120000}
>>> maximum_price = Car.objects.aggregate(Max('price'))
>>> maximum_price
{'price__max': 160000}

There are more methods & operations used to fetch data. You can see the django documentation for more details.

+ posts

Author | Python-Django Developer

+ posts

Full-stack Developer (Python | Django | React | React-Native | Angular | Vue)