Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python Django Models, ORM, and Querysets

Learn how Django models, the ORM, and querysets work together to query databases efficiently, with practical examples and performance tips.

~5 min readPython
#Django#ORM#Querysets#Database
Illustration of Django ORM mapping Python model classes to database tables with a queryset filter symbol.

Django models, the ORM, and querysets form the core of database interaction in Python. When you define a model, you create a Python class that maps to a database table. The ORM translates Python operations into SQL, and querysets are the API you use to fetch and manipulate data. Understanding how these pieces fit together is essential for writing efficient, maintainable database code.

Defining Models and the ORM Mapping

A Django model is a subclass of django.db.models.Model. Each attribute of the model represents a database field, and Django uses the model's metadata to generate migrations that create the corresponding table. For example:

python
from django.db import models class Author(models.Model): name = models.CharField(max_length=100) email = models.EmailField(unique=True) def __str__(self): return self.name class Book(models.Model): title = models.CharField(max_length=200) publication_date = models.DateField() author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')

Here, Author and Book are models. The Book model has a ForeignKey to Author, establishing a many-to-one relationship. The ORM uses this definition at runtime to generate SQL for queries, while migrations use the same model metadata to create the table structure. The related_name allows you to access all books of an author via author.books.

The ORM does not execute any database queries when you define models. It only builds a mapping. Queries are executed lazily when you evaluate a queryset, which we'll cover shortly.

Creating and Saving Objects

To insert a row into the database, you create an instance of the model and call save():

python
author = Author(name='Jane Austen', email='jane@example.com') author.save()

You can also use the create() method on the manager to combine instantiation and saving:

python
book = Book.objects.create( title='Pride and Prejudice', publication_date='1813-01-28', author=author )

Updating an existing object is similar: modify attributes and call save() again. Django tracks whether the object has a primary key; if it does, it issues an UPDATE, otherwise an INSERT.

For bulk operations, bulk_create() and bulk_update() can reduce the number of database round trips, but they bypass some model validation and signals. Use them when performance matters and the data is already validated.

Querying with Querysets

A queryset is a collection of model instances that can be filtered, ordered, and sliced. Querysets are lazy: constructing one does not hit the database. The query is executed when the queryset is evaluated—for example, when you iterate over it, call list(), or access an index. For example:

python
# No SQL executed yet qs = Book.objects.filter(author__name='Jane Austen') # SQL executed here for book in qs: print(book.title)

Querysets are chainable. You can add filters, exclusions, and ordering without evaluating the queryset until needed:

python
recent_books = Book.objects.filter( publication_date__year__gte=2000 ).exclude( title__icontains='draft' ).order_by('-publication_date')

The filter() method adds a WHERE clause, exclude() adds a NOT condition, and order_by() adds an ORDER BY. The ORM builds the SQL incrementally as you chain methods.

Filtering with Field Lookups

Django provides a rich set of field lookups that you can use in filter(), exclude(), and get(). These are expressed as keyword arguments with double underscores. Common lookups include:

LookupDescriptionExample
exactExact match (default)name__exact='Jane'
iexactCase-insensitive exactname__iexact='jane'
containsSubstring matchtitle__contains='Pride'
icontainsCase-insensitive substringtitle__icontains='pride'
inValue in a listid__in=[1,2,3]
gt, gte, lt, lteComparisonspublication_date__gte='2000-01-01'
startswith, endswithPrefix/suffixname__startswith='Jane'
isnullIS NULL checkemail__isnull=True

You can also traverse relationships by chaining lookups. For example, to get all books whose author's name starts with J:

python
books = Book.objects.filter(author__name__startswith='J')

This generates a JOIN between book and author tables automatically.

When you have a ForeignKey, Django creates a reverse relation from the target model to the model that has the foreign key. The related_name sets the accessor name; with related_name='books', accessing author.books returns a queryset that you can filter further:

python
author = Author.objects.get(name='Jane Austen') early_books = author.books.filter(publication_date__year__lt=1820)

For ManyToManyField, the same pattern applies. To avoid the N+1 query problem, use select_related() for forward ForeignKey relations and prefetch_related() for reverse relations and many-to-many fields.

python
# select_related: joins the author table in one query books = Book.objects.select_related('author').all() # prefetch_related: separate query for related objects, then caches them authors = Author.objects.prefetch_related('books').all()

select_related() works with single-valued relationships (ForeignKey, OneToOne). prefetch_related() works with multi-valued relationships (ManyToMany, reverse ForeignKey). Using these methods can dramatically reduce the number of queries your application executes.

Aggregation and Annotation

Django's ORM provides aggregation functions like Count, Sum, Avg, Min, and Max. The aggregate() method returns a dictionary of results for the entire queryset:

python
from django.db.models import Count, Avg result = Book.objects.aggregate( total_books=Count('id'), average_year=Avg('publication_date__year') )

To compute per-group values, use annotate(). This adds a computed field to each object in the queryset. For example, to count books per author:

python
from django.db.models import Count authors_with_counts = Author.objects.annotate( book_count=Count('books') ) for author in authors_with_counts: print(author.name, author.book_count)

annotate() generates a GROUP BY clause. You can combine it with filter() and order_by() to refine the results. Be careful with multiple annotations that involve joins; they can produce unexpected row multiplication if not handled properly.

Performance Considerations

Querysets are lazy, but that laziness can lead to performance issues if you are not careful. Common pitfalls include:

  • N+1 queries: Accessing related objects in a loop without select_related or prefetch_related causes one query per object. Always prefetch when you know you'll access relations.
  • Unnecessary evaluation: Converting a queryset to a list just to check if it's empty is wasteful. Use .exists() instead.
  • Large result sets: Use .iterator() to stream results and avoid loading everything into memory when processing large tables.
  • Missing indexes: Django does not automatically index every field. Add db_index=True to fields that are frequently used in filters or order_by.
python
# Efficient existence check if Book.objects.filter(title__icontains='pride').exists(): pass # Streaming large querysets for book in Book.objects.all().iterator(): process(book)

The ORM also caches queryset results in memory after the first evaluation. If you reuse the same queryset, it will not hit the database again unless you explicitly force a refresh. This is useful for repeated access within a request, but be aware that the cache is not invalidated when the database changes.

Common Pitfalls and How to Avoid Them

One frequent mistake is using filter() when you expect a single object. Use get() for that, but handle DoesNotExist and MultipleObjectsReturned exceptions:

python
try: author = Author.objects.get(name='Jane Austen') except Author.DoesNotExist: author = None except Author.MultipleObjectsReturned: author = Author.objects.filter(name='Jane Austen').first()

Another issue is relying on the default ordering of a model. If you define ordering in the model's Meta, it applies to all queries unless overridden. This can cause unexpected performance problems if the ordering field is not indexed.

When using annotate() with multiple aggregations, you may get incorrect counts if the joins multiply rows. For example, annotating a queryset with both Count('books') and Count('publishers') on a model with both relations can produce a cartesian product. Use distinct=True in the count or separate the annotations into different querysets.

Finally, remember that the ORM is not a replacement for understanding SQL. For complex queries, you can use RawSQL or extra(), but these should be used sparingly. The ORM's queryset API is powerful enough for most use cases, and staying within it keeps your code portable across database backends.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.