Query expressions in Django allow you to create complex database queries by combining database fields, operators, and functions. They are an essential part of building efficient and flexible database queries. In this article, we will look at various types of query expressions in Django with the help of a Python project.
Types of Query Expressions in Django
- Aggregating Data
- Performing Calculations
- Updating Records
- Filtering data
Aggregating Data
Suppose we want to calculate the total value of all products in our database. We can use the Sum query expression for this purpose. Here, we use the Sum expression to calculate the sum of the ‘price’ field across all Product objects. The result is stored in the ‘total_value’ dictionary.
Python3
from django.db.models import Sumtotal_value = Product.objects.aggregate(total_value=Sum('price'))print(total_value['total_value']) |
Performing Calculations
Let’s say we want to retrieve a list of products with their total value (price multiplied by quantity). We can use the F() expression for this calculation. The annotated method allows us to add a calculated field to each object in the query set. In this case, we calculate the total value using the F() expression, which references the ‘price’ and ‘quantity’ fields.
Python3
from django.db.models import Fproducts_with_total_value = Product.objects.annotate(total_value=F('price') * F('quantity'))for product in products_with_total_value: print(f"{product.name}: Total Value - {product.total_value}") |
Updating Records
You can also use query expressions to update records efficiently. For instance, let’s say we want to increase the price of all products by 10%. Here, we use the update method along with the F() expression to multiply the ‘price’ field by 1.1, effectively increasing the price by 10%.
Python3
from django.db.models import FProduct.objects.update(price=F('price') * 1.1) |
Filtering Data
Query expressions can also be used in filters to retrieve specific records. For example, let’s fetch products with a price greater than $50. In this query, we filter products where the ‘price’ is greater than 10 times the ‘quantity’ using the F() expression.
Python3
from django.db.models import Fexpensive_products = Product.objects.filter(price__gt=F('quantity') * 10) |
Setting up the Project
Starting the project
To install django follow these steps.
Command to start a project in Django
django-admin startproject queryexpressionsproject
cd my_app
Command to start the app
python3 manage.py startapp my_app
Now add this app to the ‘settings.py’
Setting up the files
model.py
You can use Django’s ORM to create, retrieve, update, and delete Product objects in your database. You can also use Django’s admin interface to manage these objects.
Python3
from django.db import modelsclass Product(models.Model): name = models.CharField(max_length=100) price = models.DecimalField(max_digits=10, decimal_places=2) quantity = models.PositiveIntegerField() def __str__(self): return self.name |
views.py
In summary, this code defines two Django view functions. The home view returns a simple “Hello, Falcon World!” message, likely intended for a homepage. The expensive_products view queries the database to find expensive products, calculates their total values, and renders an HTML template with the filtered products as context data, presumably for displaying a list of expensive products to the user.
Python3
from .models import Productfrom django.http import HttpResponsefrom django.shortcuts import redirect, renderfrom django.db import modelsdef home(request): return HttpResponse('Hello, Falcon World!')def expensive_products(request): # Calculate the total value and filter for expensive products expensive_products = Product.objects.annotate( total_value=models.ExpressionWrapper( models.F('price') * models.F('quantity'), output_field=models.DecimalField()) ).filter(total_value__gt=1000) return render(request, 'myapp/index.html', {'expensive_products': expensive_products}) |
Note: The F() expression allows you to reference database fields within your queries, making it possible to perform operations on database fields without retrieving the data into Python memory.
index.html
So, in summary, this HTML template is designed to display a list of expensive products with their names and total values.
HTML
<!DOCTYPE html><html><head> <title>Expensive Products</title></head><body> <h1>Expensive Products</h1> <ul> {% for product in expensive_products %} <li>{{ product.name }} - Total Value: ${{ product.total_value }}</li> {% endfor %} </ul></body></html> |
urls.py
In Django, the urls.py file is used to map URLs to views or functions that handle HTTP requests and generate responses.
Python3
from django.urls import pathfrom . import viewsurlpatterns = [ path('hello/', views.home, name='home'), path('expensive-products/', views.expensive_products, name='expensive_products'),] |
Deployement of the project
Run these commands to apply the migrations:
python3 manage.py makemigrations
python3 manage.py migrate
Run the server with the help of following command:
python3 manage.py runserver
Output:
