Chapter 6. Getting a Model's Data with Querysets

Querysets are used for data retrieval rather than for constructing SQL queries directly. They are part of the ORM used by Django. An ORM is used to link the view and controller by a layer of abstraction. In this way, the developer uses object model types without the need to write a SQL query. We will use querysets to retrieve the data we have stored in the database through models. These four operations are often summarized by CRUD (Create, Read, Update, and Delete).

The discussed examples in this chapter are intended to show you how the querysets work. The next chapter will show you how to use forms, and thus, how to save data sent from a client in the models.

By the end of this chapter, we will know how to:

• Save data in the database
• Retrieve data from the database
• Update data from the database

The persisting model's data on the database

Data storage is simple with Django. We just need to fill the data in the models, and use methods to store them in a database. Django handles all the SQL queries; the developer does not need to write any.

Filling a model and saving it in the database

Before you can save data from a model instance to the database, we need to define all the values of the model's required fields. We can show the examples in our view index.

The following example shows how to save a model:

from TasksManager.models import Project # line 1
from django.shortcuts import render
def page(request):
  new_project = Project(title="Tasks Manager with Django", description="Django project to getting start with Django easily.", client_name="Me") # line 2
  new_project.save() # line 3
  return render(request, 'en/public/index.html', {'action':'Save datas of model'})

We will explain the new lines of our view:

• We import our models.py file; it's the model that we will use in the view
• We then create an instance of our Project model and fill it with data
• Finally, we execute the save() method that saves the present data in the instance

We will test this code by starting the development server (or runserver) and then go to our URL. In the render() method, the value that we defined in the action variable is displayed. To check if the query is executed, we can use the administration module. There is also the software for managing databases.

We need to add more records by changing the values randomly in line 2. To find out how to do this, we'll need to read this chapter.

Getting data from the database

Before using Django to retrieve data from a database, we were using SQL queries to retrieve an object containing the result. With Django, there are two ways to retrieve records from the database depending on whether we want to get back one or several records.

Getting multiple records

To retrieve records from a model, we must first import the model into the view as we have done before to save the data in a model.

We can retrieve and display all the records in the Project model as follows:

from TasksManager.models import Project
from django.shortcuts import render
def page(request):
  all_projects = Project.objects.all()
  return render(request, 'en/public/index.html', {'action': "Display all project", 'all_projects': all_projects})

The code template that displays the projects becomes:

{% extends "base.html" %}
{% block title_html %}
  Projects list
{% endblock %}
{% block h1 %}
  Projects list
{% endblock %}
{% block article_content %}
  <h3>{{ action }}</h3>
  {% if all_projects|length > 0 %}
  <table>
    <thead>
      <tr>
        <td>ID</td>
        <td>Title</td>
      </tr>
    </thead>
    <tbody>
    {% for project in all_projects %}
      <tr>
        <td>{{ project.id }}</td>
        <td>{{ project.title }}</td>
      </tr>
    {% endfor %}
    </tbody>
  </table>
  {% else %}
  <span>No project.</span>
  {% endif %}
{% endblock %}

The all() method can be linked to a SQL SELECT * FROM query. Now, we will use the filter() method to filter our results and make the equivalent of a SELECT * FROM Project WHERE field = value query.

The following is the code to filter model records:

from TasksManager.models import Project
from django.shortcuts import render
def page(request):
  action='Display project with client name = "Me"'
  projects_to_me = Project.objects.filter(client_name="Me")
  return render(request, 'en/public/index.html', locals())

We used a new syntax to send the variables to the template. The locals() function sends all the local variables to the template, which simplifies the render line.

Tip

Best practices recommend that you pass the variables one by one and only send the necessary variables.

Each argument from the filter() method defines a filter for the query. Indeed, if we wanted to make two filters, we would have written the following line of code:

projects_to_me = Project.objects.filter(client_name="Me", title="Project test")

This line is equivalent to the following:

projects_to_me = Project.objects.filter(client_name="Me")
projects_to_me = projects_to_me.filter(title="Project test") 

The first line can be broken into two, because the querysets are chainable. Chainable methods are methods that return a queryset such that other queryset methods can be used.

The response obtained with the all() and filter() methods is of the queryset type. A queryset is a collection of model instances that can be iterated over.

Getting only one record

The methods that we will see in this chapter return objects of the Model type, which will be used to record relationships or to modify the instance of the model recovered.

To retrieve a single record with a queryset, we should use the get() method as in the following line:

first_project = Project.objects.get(id="1")

The get() method when used as the filter() method accepts filter arguments. However, you should be careful with setting the filters that retrieve a single record.

If the argument to get() is client_name = "Me", it would generate an error if we had more than two records corresponding to client_name.

Getting a model instance from the queryset instance

We said that only the get() method makes it possible to retrieve an instance of a model. This is true, but sometimes it can be useful to retrieve an instance of a model from a queryset.

For example, if we want to get the first record of the customer Me, we will write:

queryset_project = Project.objects.filter(client_name="Me").order_by("id")
# This line returns a queryset in which there are as many elements as there are projects for the Me customer

first_item_queryset = queryset_project[:1]
# This line sends us only the first element of this queryset, but this element is not an instance of a model

project = first_item_queryset.get()
# This line retrieves the instance of the model that corresponds to the first element of queryset

These methods are chainable, so we can write the following line instead of the previous three lines:

project = Project.objects.filter(client_name="Me").order_by("id")[:1].get()

Using the get parameter

Now that we have learned how to retrieve a record and we know how to use a URL, we will create a page that will allow us to display the record of a project. To do this, we will see a new URL syntax:

url(r'^project-detail-(?P<pk>\d+)$', 'TasksManager.views.project_detail.page', name="project_detail"),

This URL contains a new string, (?P<pk>\d+). It allows the URL with a decimal parameter to be valid because it ends with \d. The + character at the end means that the parameter is not optional. The <pk> string means that the parameter's name is pk.

The system routing Django will directly send this parameter to our view. To use it, simply add it to the parameters of our page() function. Our view changes to the following:

from TasksManager.models import Project
from django.shortcuts import render
def page(request, pk):
  project = Project.objects.get(id=pk)
  return render(request, 'en/public/project_detail.html', {'project' : project})

We will then create our en/public/project_detail.html template extended from base.html with the following code in the article_content block:

<h3>{{ project.title }}</h3>
<h4>Client : {{ project.client_name }}</h4>
<p>
  {{ project.description }}
</p>

We have just written our first URL containing a parameter. We will use this later, especially in the chapter about the class-based views.

Saving the foreign key

We have already recorded data from a model, but so far, we have never recorded it in the relationship database. The following is an example of recording a relationship that we will explain later in the chapter:

from TasksManager.models import Project, Task, Supervisor, Developer
from django.shortcuts import render
from django.utils import timezone
def page(request):
  # Saving a new supervisor
  new_supervisor = Supervisor(name="Guido van Rossum", login="python", password="password", last_connection=timezone.now(), email="python@python.com", specialisation="Python") # line 1
  new_supervisor.save()
  # Saving a new developer
  new_developer = Developer(name="Me", login="me", password="pass", last_connection=timezone.now(), email="me@python.com", supervisor=new_supervisor)
  new_developer.save()
  # Saving a new task
  project_to_link = Project.objects.get(id = 1) # line 2
  new_task = Task(title="Adding relation", description="Example of adding relation and save it", time_elapsed=2, importance=0, project=project_to_link, developer=new_developer) # line 3
  new_task.save()
  return render(request, 'en/public/index.html', {'action' : 'Save relationship'})

In this example, we have loaded four models. These four models are used to create our first task. Indeed, a spot is related to a project and developer. A developer is attached to a supervisor.

Following this architecture, we must first create a supervisor to add a developer. The following list explains this:

• We create a new supervisor. Note that the extending model requires no additional step for recording. In the Supervisor model, we define the fields of the App_user model without any difficulties. Here, we use timezone to record the current day's date.
• We look for the first recorded project. The result of this line will record a legacy of the Model class instance in the project_to_link variable. Only the get() method gives the instance of a model. Therefore, we must not use the filter() method.
• We create a new task, and attribute the project created in the beginning of the code and the developer that we just recorded.

This example is very comprehensive, and it combines many elements that we have studied from the beginning. We must understand it in order to continue programming in Django.

Updating records in the database

There are two mechanisms to update data in Django. Indeed, there is a mechanism to update one record and another mechanism to update multiple records.

Updating a model instance

Updating the existing data is very simple. We have already seen what it takes to be able to do so. The following is an example where it modifies the first task:

from TasksManager.models import Project, Task
from django.shortcuts import render
def page(request):
  new_project = Project(title = "Other project", description="Try to update models.", client_name="People")
  new_project.save()
  task = Task.objects.get(id = 1)
  task.description = "New description"
  task.project = new_project
  task.save()
  return render(request, 'en/public/index.html', {'action' : 'Update model'})

In this example, we created a new project and saved it. We searched our task for id = 1. We changed the description and project to the task it is attached to. Finally, we saved this task.

Updating multiple records

To edit multiple records in one shot, you must use the update() method with a queryset object type. For example, our People customer is bought by a company named Nobody, so we need to change all the projects where the client_name property is equal to People:

from TasksManager.models import Project
from django.shortcuts import render
def page(request):
  task = Project.objects.filter(client_name = "people").update(client_name="Nobody")
  return render(request, 'en/public/index.html', {'action' : 'Update for many model'})

The update() method of a queryset can change all the records related to this queryset. This method cannot be used on an instance of a model.

Deleting a record

To delete a record in the database, we must use the delete() method. Removing items is easier than changing items, because the method is the same for a queryset as for the instances of models. An example of this is as follows:

from TasksManager.models import Task
from django.shortcuts import render
def page(request):
  one_task = Task.objects.get(id = 1)
  one_task.delete() # line 1
  all_tasks = Task.objects.all()
  all_tasks.delete() # line 2
  return render(request, 'en/public/index.html', {'action' : 'Delete tasks'})

In this example, line 1 removes the stain with id = 1. Then, line 2 removes all the present tasks in the database.

Be careful because even if we use a web framework, we keep hold of the data. No confirmation will be required in this example, and no backup has been made. By default, the rule for model deletion with ForeignKey is the CASCADE value. This rule means that if we remove a template instance, the records with a foreign key to this model will also be deleted.

Getting linked records

We now know how to create, read, update, and delete the present records in the database, but we haven't recovered the related objects. In our TasksManager application, it would be interesting to retrieve all the tasks in a project. For example, as we have just deleted all the present tasks in the database, we need to create others. We especially have to create tasks in the project database for the rest of this chapter.

With Python and its comprehensive implementation of the object-oriented model, accessing the related models is intuitive. For example, we will retrieve all the project tasks when login = 1:

from TasksManager.models import Task, Project
from django.shortcuts import render
def page(request):
  project = Project.objects.get(id = 1)
  tasks = Task.objects.filter(project = project)
  return render(request, 'en/public/index.html', {'action' : 'Tasks for project', 'tasks':tasks})

We will now look for the project task when id = 1:

from TasksManager.models import Task, Project
from django.shortcuts import render
def page(request):
  task = Task.objects.get(id = 1)
  project = task.project
  return render(request, 'en/public/index.html', {'action' : 'Project for task', 'project':project})

We will now use the relationship to access the project task.

Advanced usage of the queryset

We studied the basics of querysets that allow you to interact with the data. In specific cases, it is necessary to perform more complex actions on the data.

Using an OR operator in a queryset

In queryset filters, we use a comma to separate filters. This point implicitly means a logical operator AND. When applying an OR operator, we are forced to use the Q object.

This Q object allows you to set complex queries on models. For example, to select the projects of the customers Me and Nobody, we must add the following lines in our view:

from TasksManager.models import Task, Project
from django.shortcuts import render
from django.db.models import Q
def page(request):
  projects_list = Project.objects.filter(Q(client_name="Me") | Q(client_name="Nobody"))
  return render(request, 'en/public/index.html', {'action' : 'Project with OR operator', 'projects_list':projects_list})

Using the lower and greater than lookups

With the Django queryset, we cannot use the < and > operators to check whether a parameter is greater than or less than another.

You must use the following field lookups:

• __gte: This is equivalent to SQL's greater than or equal to operator, >=
• __gt: This is equivalent to SQL's greater than operator, >
• __lt: This is equivalent to SQL's lower than operator, <
• __lte: This is equivalent to SQL's lower than or equal to operator, <=

For example, we will write the queryset that can return all the tasks with a duration of greater than or equal to four hours:

tasks_list = Task.objects.filter(time_elapsed__gte=4)

Performing an exclude query

The exclude queries can be useful in the context of a website. For example, we want to get the list of projects that do not last for more than four hours:

from TasksManager.models import Task, Project
from django.shortcuts import renderdef page(request):
  tasks_list = Task.objects.filter(time_elapsed__gt=4)
  array_projects = tasks_list.values_list('project', flat=True).distinct()
  projects_list = Project.objects.all()
  projects_list_lt4 = projects_list.exclude(id__in=array_projects)
  return render(request, 'en/public/index.html', {'action' : 'NOT IN SQL equivalent', 'projects_list_lt4':projects_list_lt4})

The following is an explanation of the code snippet:

• In the first queryset, we first retrieve the list of all the tasks for which time_elapsed is greater than 4
• In the second queryset, we got the list of all the related projects in these tasks
• In the third queryset, we got all the projects
• In the fourth queryset, we excluded all the projects with tasks that last for more than 4 hours

Making a raw SQL query

Sometimes, developers may need to perform raw SQL queries. For this, we can use the raw() method, defining the SQL query as an argument. The following is an example that retrieves the first task:

first_task = Project.objects.raw("SELECT * FROM TasksManager_project")[0]

To access the name of the first task, just use the following syntax:

first_task.title

Summary

In this chapter, we learned how to handle the database, thanks to the Django ORM. Indeed, thanks to the ORM, the developer does not need to write SQL queries. In the next chapter, we will learn how to create forms using Django.