Working with QuerySet and managers

Now that you have a fully functional administration site to manage your blog's content, it's time to learn how to retrieve information from the database and interact with it. Django comes with a powerful database abstraction API that lets you create, retrieve, update, and delete objects easily. The Django Object-relational mapper is compatible with MySQL, PostgreSQL, SQLite, and Oracle. Remember that you can define the database of your project in the DATABASES setting of your project's settings.py file. Django can work with multiple databases at a time, and you can program database routers to create custom routing schemes.

Once you have created your data models, Django gives you a free API to interact with them. You can find the data model reference of the official documentation at https://docs.djangoproject.com/en/2.0/ref/models/.

Creating objects

Open the terminal and run the following command to open the Python shell:

python manage.py shell

Then, type the following lines:

>>> from django.contrib.auth.models import User
>>> from blog.models import Post
>>> user = User.objects.get(username='admin')
>>> post = Post(title='Another post',
                slug='another-post',
                body='Post body.',
                author=user)
>>> post.save()

Let's analyze what this code does. First, we will retrieve the user object with the username admin:

user = User.objects.get(username='admin')

The get() method allows you to retrieve a single object from the database. Note that this method expects a result that matches the query. If no results are returned by the database, this method will raise a DoesNotExist exception, and if the database returns more than one result, it will raise a MultipleObjectsReturned exception. Both exceptions are attributes of the model class that the query is being performed on.

Then, we create a Post instance with a custom title, slug, and body, and we set the user we previously retrieved as the author of the post:

post = Post(title='Another post', slug='another-post', body='Post body.', author=user)

This object is in memory and is not persisted to the database.

Finally, we save the Post object to the database using the save() method:

post.save()

The preceding action performs an INSERT SQL statement behind the scenes. We have seen how to create an object in memory first and then persist it to the database, but we can also create the object and persist it into the database in a single operation using the create() method, as follows:

Post.objects.create(title='One more post', slug='one-more-post', body='Post body.', author=user)

Updating objects

Now, change the title of the post to something different and save the object again:

>>> post.title = 'New title'
>>> post.save()

This time, the save() method performs an UPDATE SQL statement.

The changes you make to the object are not persisted to the database until you call the save() method.

Retrieving objects

The Django object-relational mapping (ORM) is based on QuerySets. A QuerySet is a collection of objects from your database that can have several filters to limit the results. You already know how to retrieve a single object from the database using the get() method. We have accessed this method using Post.objects.get(). Each Django model has at least one manager, and the default manager is called objects. You get a QuerySet object using your model manager. To retrieve all objects from a table, you just use the all() method on the default objects manager, like this:

>>> all_posts = Post.objects.all()

This is how we create a QuerySet that returns all objects in the database. Note that this QuerySet has not been executed yet. Django QuerySets are lazy; they are only evaluated when they are forced to. This behavior makes QuerySets very efficient. If we don't set the QuerySet to a variable, but instead write it directly on the Python shell, the SQL statement of the QuerySet is executed because we force it to output results:

>>> Post.objects.all()

Using the filter() method

To filter a QuerySet, you can use the filter() method of the manager. For example, we can retrieve all posts published in the year 2017 using the following QuerySet:

Post.objects.filter(publish__year=2017)

You can also filter by multiple fields. For example, we can retrieve all posts published in 2017 by the author with the username admin:

Post.objects.filter(publish__year=2017, author__username='admin')

This equates to building the same QuerySet chaining multiple filters:

Post.objects.filter(publish__year=2017) \
            .filter(author__username='admin')

Queries with field lookup methods are built using two underscores, for example, publish__year, but the same notation is also used for accessing fields of related models, such as author__username.

Using exclude()

You can exclude certain results from your QuerySet using the exclude() method of the manager. For example, we can retrieve all posts published in 2017 whose titles don't start with Why:

Post.objects.filter(publish__year=2017) \
            .exclude(title__startswith='Why')

Using order_by()

You can order results by different fields using the order_by() method of the manager. For example, you can retrieve all objects ordered by their title, as follows:

Post.objects.order_by('title')

Ascending order is implied. You can indicate descending order with a negative sign prefix, like this:

Post.objects.order_by('-title')

Deleting objects

If you want to delete an object, you can do it from the object instance using the delete() method:

post = Post.objects.get(id=1)
post.delete()

Note that deleting objects will also delete any dependent relationships for ForeignKey objects defined with on_delete set to CASCADE.

When QuerySets are evaluated

You can concatenate as many filters as you like to a QuerySet, and you will not hit the database until the QuerySet is evaluated. QuerySets are only evaluated in the following cases:

• The first time you iterate over them
• When you slice them, for instance, Post.objects.all()[:3]
• When you pickle or cache them
• When you call repr() or len() on them
• When you explicitly call list() on them
• When you test them in a statement, such as bool(), or , and, or if

Creating model managers

As we previously mentioned, objects is the default manager of every model that retrieves all objects in the database. However, we can also define custom managers for our models. We will create a custom manager to retrieve all posts with the published status.

There are two ways to add managers to your models: you can add extra manager methods or modify initial manager QuerySets. The first method provides you with a QuerySet API such as  Post.objects.my_manager(), and the latter provides you with Post.my_manager.all(). The manager will allow us to retrieve posts using Post.published.all().

Edit the models.py file of your blog application to add the custom manager:

class PublishedManager(models.Manager):
    def get_queryset(self):
        return super(PublishedManager,
                     self).get_queryset()\
                          .filter(status='published')

class Post(models.Model):
    # ...
    objects = models.Manager() # The default manager.
    published = PublishedManager() # Our custom manager.

The get_queryset() method of a manager returns the QuerySet that will be executed. We override this method to include our custom filter in the final QuerySet. We have defined our custom manager and added it to the Post model; we can now use it to perform queries. Let's test it.

Start the development server again with the following command:

python manage.py shell

Now, you can retrieve all published posts whose title starts with Who using the following command:

Post.published.filter(title__startswith='Who')