Most of the web applications are implemented with the database now. queryset is used in the Django application to retrieve records by filtering or slicing or ordering the database table without changing the original data. The model used Django to create the table in the database. So, the knowledge of using the model in Django is necessary to understand the use of queryset. The main function of the queryset is to iterate the records of database tables by converting them into SQL queries. It can be used from the python command line or by writing the python script to display the browser’s output. The uses of queryset for retrieving data from a database table in different ways have been explained in this tutorial.
Prerequisites:
Before practicing the script of this tutorial, you have to complete the following tasks.
- Install the Django version 3 on Ubuntu 20 (preferably)
- Create a Django project
- Run the Django server to check the server is working properly or not.
Setup a Django app:
Run the following command to create a Django app named queryapp.
$ python3 manage.py startapp queryapp
Run the following command to create the user for accessing the Django database. If you have created the user before, then you don’t need to run the command.
$ python3 manage.py createsuperuser
Add the app name in the INSTALLED_APP part of the settings.py file.
INSTALLED_APPS = [
…..
‘queryapp’
]
Create a folder named templates inside the queryapp folder and set the template’s location of the app in the TEMPLATES part of the settings.py file.
TEMPLATES = [
{
….
‘DIRS’: [‘/home/fahmida/django_pro/queryapp/templates’],
….
},
]
Create a model for the database table:
Open the models.py file from the queryapp folder and add the following script to define the structure of products tables. Product class is defined to create a table named products with name, type, brand, and price fields. Here, name, type, and brand fields will store character data, and the price field will store the integer data.
models.py
# Import models module
from django.db import models
# Define class to create products table
class Product(models.Model):
name = models.CharField(max_length=100)
type = models.CharField(max_length=30)
brand = models.CharField(max_length=50)
price = models.IntegerField()
Run the makemigrations command to create a new migration based on the changes made by the models.
$ python3 manage.py makemigrations queryapp
Run the migrate command to execute the SQL commands and create all tables in the database that are defined in the models.py file.
$ python3 manage.py migrate
Modify the content of the admin.py file with the following content. Here, the models’ Product class is registered by using the register() method to display the products tables in the Django administration dashboard.
admin.py
# Import admin module
from django.contrib import admin
# Import Product model
from .models import Product
# Register Product model
admin.site.register(Product)
Create a template file named productList.html inside the queryapp/templates/ with the following script. This script will display all data of products table in tabular form with a search box. The user will be able to search the particular records from the products table by using the search form. for loop is used in the script to iterate the data passed from the views.py file.
productList.html
<html>
<head>
<title>
Django QuerySet Tutorial
</title>
<style>
th { text-align:left; color:blue; }
table, th, td { border: 1px solid;}
h1{ color:green;}
#name{ width:350px;}
</style>
</head>
<body>
<center><h1 style=“margin-left:20px;”>Searching Product</h1>
<form method=“get” action=“”>
{% csrf_token %}
Search Product: <input name=“src” type=“text” placeholder=“Search…” value=“”>
</form>
</center>
<center>
<table>
<tr>
<th>ID</th><th id=“name”>Name</th><th>Brand</th><th>Price</th>
</tr>
{% for product in object_list %}
<tr>
<td>{{product.id}} </td> <td>{{product.name}}</td> <td>{{product.brand}}</td><td
style=“text-align:right”>${{product.price}}</td>
</tr>
{% endfor %}
</table>
</center>
</body>
</html>
Modify the content of the views.py file with the following script. The model and template names are defined in the ProductList class. get_queryset() method of the class is defined in the script to filter the data based on the content submitted by the search box of the template. Product.objects.all() method returns all records of the products table. request.GET.keys() method is used in the script to check any data is submitted by the search form. If this method returns true, then the request.GET.get(‘src’) method is used to check the submitted value is empty or not. If this method returns a non-empty value, then the value will be stored in the variable, keyword, and it will be used for filtering the data based on the brand and type fields from the products table.
views.py
# Import ListView module
from django.views.generic import ListView
# Import Product module
from .models import Product
# Import Q module
from django.db.models import Q
# Define class for Querying data
class ProductList(ListView):
# Define model
model = Product
# Define template
template_name = ‘productList.html’
def get_queryset(self):
# Set the default query set
queryset = Product.objects.all()
# Check the form value is submitted or not
if self.request.GET.keys():
# Check the search keyword
if self.request.GET.get(‘src’) != ”:
keyword = self.request.GET.get(‘src’)
# Set the query set based on search keyword
queryset = Product.objects.filter(Q(brand=keyword.capitalize()) | Q(type=keyword.capitalize()))
return queryset
Modify the content of the urls.py file with the following script. In the script, the ‘searchPro/’ path is defined to call the ProductList.as_view() method that will send all data and the filtered data of the products table to the template file.
urls.py
# Import admin module
from django.contrib import admin
# Import path and include module
from django.urls import path
# Import SearchEmployee module
from queryapp.views import ProductList
urlpatterns = [
# Define the path for admin
path(‘admin/’, admin.site.urls),
# Define the path to search product
path(‘searchPro/’, ProductList.as_view()),
Add records into the table:
Open the Django Administration page and add some records into the products table to apply the queryset on then. Here, five records have been inserted.
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-40.jpeg" data-lazy- height="521" src="data:image/svg xml,” width=”991″>
All records of the products with the search box will be displayed in the browser after executing the following URL.
http://localhost:8000/searchPro
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-41.jpeg" data-lazy- height="462" src="data:image/svg xml,” width=”1002″>
All the shampoo products displayed if the product type, ‘shampoo‘ will be searched in the search box.
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-42.jpeg" data-lazy- height="399" src="data:image/svg xml,” width=”1001″>
The milk powder products of the Fresh brand will be displayed if the product brand, ‘fresh‘ will be searched in the search box.
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-43.jpeg" data-lazy- height="357" src="data:image/svg xml,” width=”1001″>
Conclusion:
The way of filtering the data of a simple database table by using queryset has explained in this tutorial. The data can be filtered in different ways. The readers will understand using a queryset to filter or search data in the browser after reading this tutorial.
About the author
<img alt="Fahmida Yesmin" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/channel-logo-150×150.jpg60396d665c46b.jpg" height="112" src="data:image/svg xml,” width=”112″>
Fahmida Yesmin
I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.
