After learning how to define models and apply database migrations in Django, it’s time to learn how to query that data. Building a Django web application or REST API in almost all cases implies communicating with the database. To make your life easier, Django comes with a database-abstraction API, also known as Django QuerySet API, that helps you interact with the database through numerous out-of-the-box methods.
In this article, you’ll learn what Django QuerySet is, how to use it, and what best practices you should follow. After you finish reading this article, you can also learn more advanced techniques by reading part 2: Django QuerySet – Set Operations, Aggregations, Q and F Objects Explained.
What is Django QuerySet API?
QuerySet API includes methods for creating, retrieving, updating, and deleting data (CRUD operations) as well as more advanced methods for aggregation, optimization, and set operations. These methods typically return a collection of queries, also called the queryset. Querysets are lazy, which means the database query isn’t executed immediately but only when the queryset is evaluated.
You may be wondering how the QuerySet API works under the hood. To answer that question, you must first understand Django’s ORM (Object-Relational Mapper). It is one of the most powerful Django features, enabling you to write queries in a pythonical way instead of writing raw SQL queries. So, ORM allows you to interact with the database through objects and their methods instead of SQL statements.
By executing provided methods, ORM translates Python code to SQL statements that are executed in the database. This process is also known as the queryset evaluation – rows matched by the QuerySet are fetched from the database and converted into Django objects. Fetched results are then stored inside the QuerySet’s built-in cache, reducing the need for unnecessary subsequent queries towards the database. Nice little trick there, Django!
When are Django querysets evaluated?
Django querysets are lazy. The database will not be hit until a specific action is performed. You can even chain multiple QuerySet methods together without triggering the additional database hits. That’s Django’s way of optimizing the number of queries performed towards the database. Still, to retrieve results from the database, queryset must be evaluated at some point. Actions that will evaluate the queryset are:
- Iteration
- Slicing
- Pickling/caching
- repr() method
- len() method
- list() method
- bool() method
To illustrate how the evaluation works, let’s take a look at the following code snippet:
books = Book.objects.all() # 0 db hits
filtered = books.filter(title__contains='451') # 0 db hits
for book in filtered: # 1 db hit
print(book.title)
print(list(filtered)) # 0 db hits (cached results)
for book in filtered: # 0 db hits (cached results)
print(book.title)
As you can see, the database was hit only at the moment of performing iteration. This behavior enables you to chain multiple methods, with Django optimizing the execution of the final query towards the database.
In the above example, you can also notice that converting the queryset to a list and performing an additional iteration resulted in zero additional database hits. That is because Django used cached results and didn’t have to hit the database again.
If for some reason, you want to get new results from the database, you can trigger the evaluation again by adding the all() method to the previously evaluated queryset.
print(list(filtered.all())) # 1 db hit (no cached results)
Okay, now we can proceed to examples I prepared showing the most common queries and how to use them.
Using Django QuerySet API – Basic Queries
In the following examples, the Book model will be used to demonstrate various queryset methods.
class Book(models.Model):
title = models.CharField(max_length=255)
published_date = models.DateTimeField(null=True)
If you didn’t apply migrations for the Book model, do it now to be able to use it in the interactive shell. Then start the shell by executing the following command in your terminal:
$ python manage.py shell
Before using the Book model, you have to import it first.
>>> from library.models import Book
Creating objects
To see some results, let’s start first by creating new rows in the database. You can create a new Book object by invoking the create() method that accepts model attributes as arguments. Example of creating a Book object:
>>> Book.objects.create(title="Fahrenheit 451") <Book: Book object (1)>
Another way of creating a new record in the database is by instantiating the new object and then calling the save() method to save it to the database.
>>> book = Book(title="Fahrenheit 451") >>> book.save()
Retrieving all objects
To retrieve all objects, use the all() method that returns a copy of the current QuerySet via the model manager.
>>> Book.objects.all() <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
Notice how the returned result isn’t a list, array, or set but the QuerySet collection. An important thing to notice is that the returned queryset awaits for evaluation to hit the database.
Retrieving single object
Similarly, to retrieve a single object via the model’s attribute, you should invoke the get() method. Only, this time the exact object will be returned instead of queryset. In this example, we want to get the Book object with the id = 1.
>>> Book.objects.get(id=1) <Book: Book object (1)>
WARNING - Make sure to use the get() method passing the attribute that is marked as a primary key or unique=True. Since the get() method assumes only one object will be returned in the queryset, Django will throw the MultipleObjectsReturned error if multiple objects are returned. Similarly, if no objects are found, Django will throw the DoesNotExist error.
Updating objects
You know how to create a new object, but what if you want to update the value of the existing one? Well, it’s as easy as it can be. Just change the model’s field value and call the save() method as you did when creating an object.
>>> book = Book.objects.get(id=1) >>> book.title = "New Title" >>> book.save()
If you are only updating some fields, you can add the update_fields argument to the save() method to slightly boost the performance. The update_fields argument accepts an array of field names you want to update.
>>> book = Book.objects.get(id=1) >>> book.title = "New Title" >>> book.save(update_fields=['title'])
Deleting objects
To delete an object, first retrieve the object and then call the delete() method to remove the row from the database.
>>> book = Book.objects.get(id=1) >>> book.delete()
Filtering objects
In a case when you don’t want a single object or all objects, but only those that contain specific values, you should use the filter() method. Filtering enables you to retrieve only a subset of all objects based on the lookup field you provide to the filter() method.
An important thing to know before we proceed is how the filtering syntax looks like actually. The filter() method expects an argument in the following format:field__lookuptype=value
Where field and lookuptype are separated by the double-underscore (__) sign. To further clarify:
fieldis the model’s attribute upon which objects are filtered.lookuptypeis a function used to define how filtering will be performed.valuedefines the parameter by which objects will be filtered.
Since there are multiple ways of filtering data, I’ll show a few different examples.
Filter Book objects that contain “451” in title:
>>> Book.objects.filter(title__contains="451") <QuerySet [<Book: Book object (3)>]>
Filter Book objects by title starting with “Fahrenheit”:
>>> Book.objects.filter(title__startswith="Fahrenheit") <QuerySet [<Book: Book object (3)>]>
Filter Book objects by title starting with “fahrenheit” (case-insensitive):
>>> Book.objects.filter(title__istartswith="fahrenheit") <QuerySet [<Book: Book object (3)>]>
Filter Book objects published before 1/1/2000:
>>> Book.objects.filter(published_date__date__lt=date(2000, 1, 1)) <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
You can visit Django’s Official documentation to see all field lookups.
Ordering objects
Sometimes you’ll want to retrieve objects in a specific order. You can use the order_by() method provided by the model’s manager for that purpose.
NOTE - By default, results are ordered by the attribute named ordering defined in the model’s Meta. Calling the order_by() method will override this default setting.
Order Book objects by published_date (ascending):
>>> Book.objects.order_by('published_date')
<QuerySet [<Book: Book object (3)>, <Book: Book object (2)>, <Book: Book object (1)>]>
Order Book objects by published_date (descending):
>>> Book.objects.order_by('-published_date')
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
Notice how the ordering changed from ascending to descending order when the - sign was added to the parameter passed to the order_by() method.
Django QuerySet to list
One way of evaluating the returned QuerySet is by casting it to the list, which will result in a list of returned objects. To cast the QuerySet to the list, wrap a QuerySet result inside the list keyword.
>>> list(Book.objects.all()) [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]
As you can see, the result is not any more of type QuerySet. Now it contains only objects returned from the database that you can access as you would any list item normally.
Django QuerySet to dict
Django offers the values() method that can help you represent QuerySet results as a list of dictionaries or JSON objects. So, instead of returning model objects, this method will return dicts containing model attributes as keys and their values.
>>> Book.objects.values()
<QuerySet [{'id': 1, 'title': "The Hitchhiker's Guide to the Galaxy", 'published_date': datetime.datetime(1979, 10, 12, 0, 0, tzinfo=<UTC>)}, {'id': 2, 'title': 'Do Androids Dream of Electric Sheep?', 'published_date': datetime.datetime(1968, 1, 1, 0, 0, tzinfo=<UTC>)}, {'id': 3, 'title': 'Fahrenheit 451', 'published_date': datetime.datetime(1953, 10, 13, 0, 0, tzinfo=<UTC>)}]>
You can also specify what attributes you want to return. For example, let’s return only the id and title this time. By specifying the id and title as parameters passed to the values() method, Django will return a queryset containing dicts (valid JSON objects).
>>> Book.objects.values('id', 'title')
<QuerySet [{'id': 1, 'title': "The Hitchhiker's Guide to the Galaxy"}, {'id': 2, 'title': 'Do Androids Dream of Electric Sheep?'}, {'id': 3, 'title': 'Fahrenheit 451'}]>
Django QuerySet to list of values
If you don’t want to return key-value pairs but only specific values of a chosen attribute, then you should use the values_list() method. It returns a list of tuples containing values. Returned values in tuples are picked from the model attributes that were passed to the method.
>>> Book.objects.values_list('title')
<QuerySet [("The Hitchhiker's Guide to the Galaxy",), ('Do Androids Dream of Electric Sheep?',), ('Fahrenheit 451',)]>
By adding the flat=True parameter, returned list will contain flat values.
>>> Book.objects.values_list('title', flat=True)
<QuerySet ["The Hitchhiker's Guide to the Galaxy", 'Do Androids Dream of Electric Sheep?', 'Fahrenheit 451']>
Django QuerySet count
To count total objects returned, use the count() method. It’s faster than loading the results and calling the len() method.
>>> Book.objects.count() 3
Summary
For any kind of Django development, it’s essential to know what are querysets and how they work. To recap, database records can be retrieved by executing methods provided by Django’s QuerySet API. Those methods usually retrieve a collection of queries, also called queryset. Querysets are lazy, meaning the database hit will not be performed until the queryset is evaluated first. Evaluation means fetching the matched rows from the database and converting them to Django objects. Queryset evaluation can be triggered in multiple ways, such as by performing iteration, slicing, pickling, or by using some standard Python methods such as len(), list(), repr(), bool(). Fetched results are then cached and reused if the same queryset is used again.
Now you can also learn about more advanced techniques in part 2: Django QuerySet – Set Operations, Aggregations, Q and F Objects Explained.