Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 77 ยท Models and the ORM

QuerySets: Create, Read, Update, Delete

Learn Django ORM basics: create, get, filter, exclude, update and delete rows, bulk operations, ordering, slicing and how lazy QuerySets decide when to query.

Once your models exist and their tables are created (Models and Fields, Migrations), you need to work with the data. Django's ORM (object-relational mapper) lets you do that in Python: you call methods on your model, and Django writes and runs the SQL. Four jobs cover almost everything an application does with data, often abbreviated CRUD: create, read, update and delete.

Every model class has a default manager called objects. A manager is your entry point to the table: Book.objects.create(...), Book.objects.filter(...). Most of its methods return a QuerySet, which represents a collection of rows. The most important thing to learn about QuerySets is that they are lazy: building one does not touch the database. Understanding when the query actually runs explains most of the ORM's behavior, and most of its performance pitfalls.

All examples use a Book model with a title, author, year, price and an in_stock flag. The setup block (settings, the model, and a helper that creates the table) is the same in each example, so every editor on this page stands on its own.

Create

There are several ways to insert a row, and they differ only in convenience:

  1. Create an instance and call save(). Before the first save the object has no primary key; after it, Django has filled it in.
  2. Book.objects.create(...) does both steps in one call and returns the saved object.
  3. bulk_create([...]) inserts many objects with a single INSERT. It is much faster than a loop, but it skips the model's save() method and signals.
  4. get_or_create(lookup, defaults={...}) fetches the row if it exists, or creates it. It returns an (object, created) pair, which is handy for "make sure this exists".

Read: get, first and friends

get() returns exactly one object and is the tool for looking up a row by something unique, such as its primary key. If nothing matches it raises Model.DoesNotExist, and if several match it raises Model.MultipleObjectsReturned. Both exceptions are attributes of your model class, so you can catch them precisely. For "give me the first one or nothing", use first() or last(), which return None instead of raising.

Python Creating and fetching rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The primary key was None before save() and 1 afterwards. bulk_create handled three books at once, get_or_create found the existing Emma but created Ulysses, and the two error cases showed the exact messages of DoesNotExist and MultipleObjectsReturned. In a view, you usually want a 404 page for a missing row, and Django has a shortcut for that, get_object_or_404, which you will meet in Function-Based Views.

QuerySets are lazy

filter(), exclude(), order_by(), all() and similar methods each return a new QuerySet and run no SQL. You can chain as many as you like, and Django merges them into one query. The query is executed only when you actually need results:

  • iterating (for book in qs), list(qs), len(qs) or bool(qs)
  • indexing one element (qs[0]) or slicing with a step
  • calling get(), first(), count(), exists(), aggregate() and similar methods that return a value
flowchart LR
    A["Book.objects"] --> B[".filter(price__lt=9)"]
    B --> C[".exclude(author='...')"]
    C --> D[".order_by('-price')"]
    D --> E{"Evaluated?"}
    E -- "no: still a QuerySet" --> F["No SQL yet"]
    E -- "for / list / len / get" --> G["One SQL query,<br/>results cached"]

Chaining builds a description of a query; iteration, slicing with an index or aggregates run it.

Once a QuerySet has been evaluated, it stores its results. Iterating it again reuses the cache instead of querying the database again. The next example counts the queries to prove both facts, prints the SQL Django generates, and then shows ordering, slicing and the values() shortcuts.

Python Filtering, laziness, ordering and slicing

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output closely:

  • Building the chain (filter, exclude, order_by) produced 0 queries. Printing the titles caused 1, and printing them again still shows 1: the second pass used the cached results.
  • str(queryset.query) prints the SQL. Seeing it is the fastest way to understand what a chain does; here the two conditions in one filter() call were joined with AND.
  • order_by("-year") sorts descending, and several fields sort by the first, then the second.
  • Slicing translates to LIMIT and OFFSET, so [2:4] fetches rows without loading the whole table. Negative indexes are not supported.
  • values() returns dictionaries and values_list(..., flat=True) a flat list, both without creating model instances.

Choosing the right evaluation method

You want to... Use Why
Know whether any row matches exists() Stops at the first row
Know how many rows count() One SELECT COUNT(*), no objects loaded
Loop over results you will use anyway for x in qs One query, then cached
Get only some columns values() / values_list() Skips building model objects
The first or last row first() / last() Adds LIMIT 1 and returns None if empty

Writing len(qs) when you only need a count loads every row. Writing if qs: on a huge table does the same. Prefer count() and exists(). Filtering with more detailed conditions (__contains, __in, Q objects) is the topic of Lookups, Q Objects and F Expressions.

Update

There are two ways to change data, and they work at different levels:

  • On an object: change attributes, then call save(). It writes the whole row by default. Pass update_fields=[...] to write only the named columns, which is faster and avoids overwriting another process's change to a different column.
  • On a QuerySet: queryset.update(field=value) runs a single UPDATE for all matching rows and returns how many were changed. No objects are loaded and the model's save() method is not called, so signals do not fire and auto_now fields are not refreshed.

For many objects that need different values, bulk_update(objects, fields) writes them all in one statement. update_or_create() mirrors get_or_create().

Delete

instance.delete() removes one row, and queryset.delete() removes every matching row. Both return a tuple: the total number of deleted rows and a dictionary with a count per model. The counts include rows removed by cascades, which matters once models have relationships.

Python Updating and deleting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice these details in the output:

  • save(update_fields=["price"]) touched only that column.
  • update() reported that it changed two rows, and bulk_update applied a different new price to each book with one statement.
  • delete() returned (2, {'library.Book': 2}). After an instance is deleted, its pk is set to None.
  • The last line deleted everything. Book.objects.all().delete() is legal and has no confirmation, so always double-check the filter in front of a delete(). (Django refuses Book.objects.delete() without .all() as a small safety net.)

update() and delete() on a QuerySet go straight to the database. They bypass save(), per-object validation and signals. If your model relies on custom logic in save(), either loop and call save() per object, or make sure the logic does not depend on it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. When does Django actually run the SQL for Book.objects.filter(year__gt=1900).order_by('title')?

    QuerySets are lazy. Chaining filter and order_by only builds a query; the database is hit when results are needed, such as iteration, len(), list(), get() or first().
  2. 2. Which call tells you whether at least one row matches, with the least work for the database?

    exists() runs a cheap query that stops at the first matching row. len() and list() load every row, and count() still counts them all.
  3. 3. What does Model.objects.get(...) do when two rows match?

    get() expects exactly one match. Zero matches raise DoesNotExist and more than one raises MultipleObjectsReturned. Use filter().first() when you want a lenient lookup.
  4. 4. What is true about queryset.update(in_stock=False)?

    update() translates to a single UPDATE for all matching rows and returns the row count. It does not load objects, call save() or send signals.
  5. 5. What does delete() return?

    delete() returns (total, {'app.Model': count, ...}). The counts include related rows deleted by cascades.

Summary

  • Each model has a manager, objects. Use create(), bulk_create(), get_or_create() and save() to insert rows.
  • get() returns exactly one object (or raises DoesNotExist / MultipleObjectsReturned); filter(), exclude(), order_by() and all() return new QuerySets.
  • QuerySets are lazy and cache their results once evaluated. Inspect the SQL with str(qs.query).
  • Prefer exists() and count() over len() and bool(), and use slicing for pagination.
  • save() updates one object; update() and bulk_update() change many rows with few statements but skip save() and signals.
  • delete() returns (total, per_model_counts); always check the filter in front of it.

Next, go beyond simple equality filters with Lookups, Q Objects and F Expressions.