Chapter 5: Database Operations

5.3 QuerySet and Database API

QuerySet Concept

QuerySet is the core concept of Django ORM, representing a collection of database queries. QuerySet has the following characteristics:

• Lazy Execution: QuerySet only executes database queries when results are needed
• Chaining: Multiple methods can be called consecutively to build complex queries
• Cacheable: The same QuerySet caches results to avoid repeated queries

Basic Query Operations

Getting All Objects

from myapp.models import Article

# Get all articles
articles = Article.objects.all()

# Convert to list (triggers query execution)
article_list = list(articles)

# Iterate through QuerySet (triggers query execution)
for article in articles:
    print(article.title)

Getting a Single Object

# Get by primary key
article = Article.objects.get(pk=1)

# Get by field
article = Article.objects.get(title="Django Tutorial")

# Handle non-existent objects
try:
    article = Article.objects.get(pk=999)
except Article.DoesNotExist:
    print("Article does not exist")

# Use get_or_create
article, created = Article.objects.get_or_create(
    title="New Article",
    defaults={'content': 'Default content'}
)

Filter Queries

# Basic filtering
published_articles = Article.objects.filter(is_published=True)

# Multi-condition filtering
recent_published = Article.objects.filter(
    is_published=True,
    created_at__gte=timezone.now() - timedelta(days=7)
)

# Exclude queries
unpublished_articles = Article.objects.exclude(is_published=True)

# Chained filtering
articles = Article.objects.filter(category='tech').exclude(is_published=False)

Field Lookups

Django provides rich field lookup methods:

# Exact match
articles = Article.objects.filter(title__exact="Django Tutorial")

# Contains query
articles = Article.objects.filter(title__contains="Django")

# Starts with
articles = Article.objects.filter(title__startswith="Django")

# Ends with
articles = Article.objects.filter(title__endswith="Tutorial")

# Null query
articles = Article.objects.filter(content__isnull=True)

# Range query
from datetime import datetime, timedelta
start_date = datetime(2025, 1, 1)
end_date = datetime(2025, 12, 31)
articles = Article.objects.filter(created_at__range=(start_date, end_date))

# IN query
categories = ['tech', 'science', 'art']
articles = Article.objects.filter(category__in=categories)

# Comparison queries
articles = Article.objects.filter(views__gt=100)   # Greater than
articles = Article.objects.filter(views__gte=50)   # Greater than or equal
articles = Article.objects.filter(views__lt=10)    # Less than
articles = Article.objects.filter(views__lte=5)    # Less than or equal

Sorting and Slicing

# Ascending sort
articles = Article.objects.order_by('created_at')

# Descending sort
articles = Article.objects.order_by('-created_at')

# Multi-field sorting
articles = Article.objects.order_by('category', '-created_at')

# Slicing operations (LIMIT and OFFSET)
first_5_articles = Article.objects.all()[:5]          # LIMIT 5
next_5_articles = Article.objects.all()[5:10]         # LIMIT 5 OFFSET 5
last_5_articles = Article.objects.all()[-5:]          # Last 5

# Random sorting
import random
articles = Article.objects.order_by('?')[:10]         # Random 10 articles

Aggregation and Annotations

Basic Aggregation

from django.db.models import Count, Avg, Max, Min, Sum

# Count
article_count = Article.objects.count()
category_count = Article.objects.aggregate(Count('category'))

# Average
avg_views = Article.objects.aggregate(Avg('views'))

# Maximum and minimum values
max_views = Article.objects.aggregate(Max('views'))
min_views = Article.objects.aggregate(Min('views'))

# Sum
total_views = Article.objects.aggregate(Sum('views'))

# Multiple aggregations
stats = Article.objects.aggregate(
    total=Count('id'),
    avg_views=Avg('views'),
    max_views=Max('views')
)

Group Aggregation

# Count articles by category
category_stats = Article.objects.values('category').annotate(
    article_count=Count('id'),
    avg_views=Avg('views')
).order_by('-article_count')

# Count by author
author_stats = Article.objects.values('author__username').annotate(
    article_count=Count('id'),
    total_views=Sum('views')
)

# Count by date
from django.db.models.functions import TruncDate
daily_stats = Article.objects.annotate(
    date=TruncDate('created_at')
).values('date').annotate(
    count=Count('id')
).order_by('date')

Annotations

from django.db.models import F, Value
from django.db.models.functions import Concat, Coalesce

# Use F expressions to reference field values
articles = Article.objects.annotate(
    popularity_score=F('views') * 0.7 + F('likes') * 0.3
)

# Field calculation
articles = Article.objects.annotate(
    reading_time=F('word_count') / 200  # Assuming reading speed of 200 words/minute
)

# String concatenation
articles = Article.objects.annotate(
    full_title=Concat('category', Value(': '), 'title')
)

# Handle null values
articles = Article.objects.annotate(
    safe_content=Coalesce('content', Value('No content'))
)

# Combine multiple functions
articles = Article.objects.annotate(
    display_title=Concat(
        Substr('title', 1, 20), 
        Value('...') if Length('title') > 20 else Value('')
    ),
    safe_summary=Coalesce('summary', Value('Click to view details'))
)

# Use F expressions for field operations
from django.db.models import F
articles = Article.objects.annotate(
    title_with_views=Concat('title', Value(' ('), F('view_count'), Value(' views)'))
)

Related Queries

Forward Queries

# One-to-one relationship
class AuthorProfile(models.Model):
    author = models.OneToOneField(Author, on_delete=models.CASCADE)
    bio = models.TextField()

# Get author's profile
author = Author.objects.get(pk=1)
profile = author.authorprofile

# One-to-many relationship
class Comment(models.Model):
    article = models.ForeignKey(Article, on_delete=models.CASCADE)
    content = models.TextField()

# Get all comments for an article
article = Article.objects.get(pk=1)
comments = article.comment_set.all()

# Many-to-many relationship
class Tag(models.Model):
    name = models.CharField(max_length=50)
    articles = models.ManyToManyField(Article)

# Get all tags for an article
article = Article.objects.get(pk=1)
tags = article.tag_set.all()

Reverse Queries

# Reverse query through foreign key
articles_with_comments = Article.objects.filter(comment__isnull=False)

# Reverse query through many-to-many
articles_with_popular_tags = Article.objects.filter(
    tag__name__in=['django', 'python']
)

# Optimize related queries
articles = Article.objects.select_related('author').prefetch_related('tags')

Query Optimization

select_related and prefetch_related

When looping through related object attributes in templates, always use select_related or prefetch_related to preload data. This is a basic Django performance optimization skill; otherwise, it will cause N+1 query problems.

# select_related: For one-to-one and one-to-many relationships (SQL JOIN)
articles = Article.objects.select_related('author')  # Reduce query count

# prefetch_related: For many-to-many and reverse relationships (additional queries)
articles = Article.objects.prefetch_related('tags', 'comment_set')

# Combined usage, only 1 query
articles = Article.objects.select_related('author').prefetch_related(
    'tags', 'comment_set'
)

only and defer

# only: Load only specified fields
articles = Article.objects.only('title', 'created_at')

# defer: Exclude specified fields
articles = Article.objects.defer('content', 'metadata')

Bulk Operations

# Bulk create
articles_data = [
    Article(title=f'Article{i}', content=f'Content{i}')
    for i in range(100)
]
Article.objects.bulk_create(articles_data)

# Bulk update
Article.objects.filter(is_published=False).update(is_published=True)

# Bulk delete
Article.objects.filter(created_at__lt=timezone.now() - timedelta(days=365)).delete()

Raw SQL Queries

Executing Raw SQL

from django.db import connection

# Execute SQL directly
with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM blog_article WHERE views > %s", [100])
    rows = cursor.fetchall()

# Use raw method
articles = Article.objects.raw('SELECT * FROM blog_article WHERE views > 100')

# Raw query with parameters
articles = Article.objects.raw(
    'SELECT * FROM blog_article WHERE category = %s AND views > %s',
    ['tech', 50]
)

Custom SQL Queries

# Custom manager method
class ArticleManager(models.Manager):
    def popular_articles(self, min_views=100):
        return self.raw('''
            SELECT * FROM blog_article 
            WHERE views > %s 
            ORDER BY views DESC
        ''', [min_views])

class Article(models.Model):
    # ... field definitions ...
    objects = ArticleManager()

# Use custom query
popular_articles = Article.objects.popular_articles(200)

Practical Example: Article Search Function

from django.db.models import Q

def search_articles(query, category=None, author=None):
    """
    Article search function
    """
    # Build query conditions
    conditions = Q()
    
    # Keyword search (title or content)
    if query:
        conditions |= Q(title__icontains=query)
        conditions |= Q(content__icontains=query)
    
    # Category filter
    if category:
        conditions &= Q(category=category)
    
    # Author filter
    if author:
        conditions &= Q(author__username=author)
    
    # Execute query and optimize
    articles = Article.objects.filter(conditions).select_related(
        'author'
    ).prefetch_related(
        'tags'
    ).order_by('-created_at')
    
    return articles

# Usage example
results = search_articles('Django', category='tech')

Advanced Query Techniques

Conditional Expressions

from django.db.models import Case, When, Value, IntegerField

# Conditional annotation
articles = Article.objects.annotate(
    popularity=Case(
        When(views__gt=1000, then=Value('Popular')),
        When(views__gt=100, then=Value('Average')),
        default=Value('Cold'),
        output_field=CharField()
    )
)

# Conditional update
Article.objects.update(
    status=Case(
        When(views__gt=1000, then=Value('popular')),
        default=Value('normal')
    )
)

Window Functions

Window Function Advantages

• Database-side computation: Reduces application layer logic and improves performance
• Complex analysis: Supports ranking, partitioning, moving averages, and other advanced analytics
• Flexibility: Multiple window functions can be combined

Usage Notes

• Requires database support for window functions (PostgreSQL, MySQL 8.0+, SQLite 3.25+)
• Complex window functions may affect query performance
• Be careful about N+1 query problems when using in templates

from django.db.models import Window, F, Avg, Sum
from django.db.models.functions import Rank, DenseRank, RowNumber, NthValue

# Rank by views descending, get top 10
# Result characteristics:
# Articles with the same views will get the same rank
# Ranks will have gaps (e.g.: 1, 2, 2, 4, 5...)
articles = Article.objects.annotate(
    rank=Window(
        expression=Rank(),  # Use Rank() ranking function
        order_by=F('views').desc()  # Order by views descending
    )
).filter(rank__lte=10)  # Only take top 10 ranked articles

# Dense ranking (no gaps)
articles = Article.objects.annotate(
    dense_rank=Window(
        expression=DenseRank(),
        order_by=F('views').desc()
    )
)

# Add row numbers for each article (ordered by creation time ascending)
articles = Article.objects.annotate(
    row_number=Window(
        expression=RowNumber(),  # Use RowNumber() row number function
        order_by=F('created_at').asc()  # Order by creation time ascending
    )
)

# Ranking within categories
articles = Article.objects.annotate(
    category_rank=Window(
        expression=Rank(),
        order_by=F('views').desc(),
        partition_by=F('category')  # Partition by category
    )
)

# Moving average (average views of the last 3 articles)
articles = Article.objects.annotate(
    moving_avg=Window(
        expression=Avg('views'),
        order_by=F('created_at').asc(),
        frame=Window.frame(start=-2, end=Window.CURRENT_ROW)
    )
)

Performance Monitoring and Debugging

Query Analysis

# Enable query logging
import logging
logger = logging.getLogger('django.db.backends')
logger.setLevel(logging.DEBUG)

# Use django-debug-toolbar
# Install: pip install django-debug-toolbar
# After configuration, you can view detailed query information

# Manually check queries
articles = Article.objects.all()
print(articles.query)  # View generated SQL

Query Optimization Recommendations

1. Use indexes: Create database indexes for frequently queried fields
2. Avoid N+1 queries: Use select_related and prefetch_related
3. Bulk operations: Use bulk_create, bulk_update to reduce database round trips
4. Limit result sets: Use slicing operations to limit returned data
5. Cache queries: Use caching for data that doesn't change frequently

Summary

Django's QuerySet and database API provide powerful and flexible database operation capabilities:

• ✅ Rich query methods: filter, exclude, get, annotate, etc.
• ✅ Multiple field lookups: exact match, range queries, fuzzy search, etc.
• ✅ Aggregation and grouping: Count, Sum, Avg, and other aggregation functions
• ✅ Related query optimization: select_related, prefetch_related
• ✅ Raw SQL support: raw queries and custom SQL
• ✅ Performance optimization: bulk operations, query optimization techniques

Mastering these features can help you build efficient and maintainable database query logic.

Next

We will learn about using the Django view system.

6.1 Function Views →

Contents

Back to Course Outline