Querying the Database in Django

Django's Object-Relational Mapping (ORM) makes it easy to query the database using Python code. This article explains how to retrieve, filter, and manipulate data in Django with examples.

Basic Queries

Let's assume we have the following model:

            
    from django.db import models

    class Book(models.Model):
        title = models.CharField(max_length=100)
        author = models.CharField(max_length=50)
        publication_date = models.DateField()
        price = models.DecimalField(max_digits=6, decimal_places=2)

        def __str__(self):
            return self.title

Retrieve All Records

To get all records from the database:

            
    from myapp.models import Book

    books = Book.objects.all()
    for book in books:
        print(book.title)

Retrieve a Single Record

Use get() to retrieve a single record. If no record is found or multiple records match, it raises an error:

            
    book = Book.objects.get(id=1)
    print(book.title)

Filtering Data

You can use filter() to retrieve records matching specific conditions:

            
    books_by_author = Book.objects.filter(author="J.K. Rowling")
    for book in books_by_author:
        print(book.title)

Chaining Filters

Filters can be chained for more complex queries:

            
    cheap_books = Book.objects.filter(price__lt=20).filter(publication_date__year=2020)
    for book in cheap_books:
        print(book.title)

Excluding Data

Use exclude() to exclude records matching certain conditions:

            
    non_expensive_books = Book.objects.exclude(price__gte=50)
    for book in non_expensive_books:
        print(book.title)

Field Lookups

Field lookups allow you to perform more advanced queries:

  • exact: Matches an exact value.
  • icontains: Case-insensitive partial match.
  • startswith, endswith: Matches the beginning or end of a value.
  • gte, lte: Greater than or equal to, less than or equal to.
            
    books_starting_with_harry = Book.objects.filter(title__startswith="Harry")
    for book in books_starting_with_harry:
        print(book.title)

Sorting Results

Use order_by() to sort query results:

            
    books_by_price = Book.objects.all().order_by('price')  # Ascending order
    books_by_price_desc = Book.objects.all().order_by('-price')  # Descending order

Aggregation and Annotations

Django supports aggregating data using aggregate() and annotate():

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

    average_price = Book.objects.aggregate(Avg('price'))
    print("Average Price:", average_price['price__avg'])

    book_count = Book.objects.aggregate(Count('id'))
    print("Total Books:", book_count['id__count'])

Raw SQL Queries

If you need more control, you can write raw SQL queries:

            
    from django.db import connection

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM myapp_book WHERE price < %s", [20])
        rows = cursor.fetchall()
        for row in rows:
            print(row)

Conclusion

Django's ORM provides a powerful and intuitive way to query the database without writing SQL. By mastering these techniques, you can efficiently retrieve and manipulate data for your application.




Advertisement