Query Data in Django Model
Setting Up a Django Model
First, ensure you have a Django model defined. For example, let’s say you have a model called Book:
from django.db import models
class Book(models.Model):
title = models.CharField(max_length=200)
author = models.CharField(max_length=100)
published_date = models.DateField()
isbn = models.CharField(max_length=13, unique=True)
def __str__(self):
return self.title
Querying Data
1. Retrieving All Records
To retrieve all records of the Book model:
books = Book.objects.all()
2. Filtering Records
To filter records, you can use the filter method. For example, to get all books by a specific author:
books_by_author = Book.objects.filter(author='John Doe')
You can also chain multiple filters:
recent_books = Book.objects.filter(author='John Doe', published_date__year=2023)
3. Retrieving a Single Record
To retrieve a single record, you can use the get method. This is useful when you are sure the query will return only one result:
book = Book.objects.get(isbn='1234567890123')
Note: get will raise a DoesNotExist exception if no record is found, and a MultipleObjectsReturned exception if more than one record is found.
4. Excluding Records
To exclude certain records, use the exclude method:
non_recent_books = Book.objects.exclude(published_date__year=2023)
5. Ordering Records
To order records, use the order_by method:
ordered_books = Book.objects.order_by('published_date')
To order in descending order:
ordered_books_desc = Book.objects.order_by('-published_date')
6. Limiting Querysets
To limit the number of results returned, you can use Python’s array slicing:
limited_books = Book.objects.all()[:10]
7. Aggregating Data
To perform aggregation, use the aggregate method along with Django’s aggregation functions like Count, Max, Min, Avg, and Sum:
from django.db.models import Avg
average_published_year = Book.objects.aggregate(Avg('published_date__year'))
8. Using Q Objects for Complex Queries
For more complex queries, such as OR conditions, use Q objects:
from django.db.models import Q books = Book.objects.filter(Q(author='John Doe') | Q(author='Jane Doe'))
9. Raw SQL Queries
If needed, you can execute raw SQL queries:
from django.db import connection
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM myapp_book WHERE author = %s", ['John Doe'])
books = cursor.fetchall()
Or use raw manager method:
books = Book.objects.raw('SELECT * FROM myapp_book WHERE author = %s', ['John Doe'])
References
https://docs.djangoproject.com/en/5.0/topics/db/queries/