Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 37 ยท ORM Mapping

Working with Objects: Add, Query, Update, Delete

Learn the everyday ORM operations with a Session: add and add_all, get by primary key, select entities, update by attribute or bulk statement, delete, and refresh.

A mapped class describes a table. To actually move data in and out you need a Session. Think of the session as a workspace: you load objects into it, change them, add new ones, and tell it to save. Everything you do with an object while it belongs to a session is tracked, and a single commit() turns your changes into INSERT, UPDATE and DELETE statements.

This lesson covers the four everyday operations (create, read, update, delete) with a small Task model. Chapter 6 returns to sessions in depth: how they track state, flush, expire and handle transactions.

Opening a session

Session(engine) creates one, and the with block closes it for you. A session uses a connection from the engine's pool, begins a transaction when it first needs one, and keeps it open until you commit() or rollback().

Python
with Session(engine) as session:
    ...                      # use the session
    session.commit()         # make the changes permanent

For writes there is a shorter form, with Session(engine) as session, session.begin():, which commits when the block succeeds and rolls back when it raises.

Create: add() and add_all()

add() puts a new object in the session as pending. Nothing is sent to the database yet. When the session flushes (at commit(), at flush(), or before a query), the INSERT runs, and database-generated values such as the primary key appear on the object.

Python Creating objects

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.

flush() sends pending SQL inside the current transaction but does not commit. You rarely call it yourself, except when you need a generated key before the commit.

Read: get() and select()

There are two main ways to read:

  • session.get(Task, 2) fetches by primary key and returns the object or None. It checks the session's cache first, so repeating it costs no query.
  • session.scalars(select(Task)...) runs any query and yields objects. Finish with .all(), .first(), .one() or .one_or_none() as in chapter 2.
Python Reading objects

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 second get(Task, 2) returned the very same Python object without another query. That behaviour is the identity map: within one session, each database row corresponds to at most one object.

Update: change an attribute

To update, load the object and assign to its attributes. The session notices the change and writes an UPDATE containing only the changed columns when it flushes.

Python Updating by attribute

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.

session.dirty lists objects with pending changes. Assigning the same value again is not a change. For changes to many rows at once, loading every object is wasteful, so use a bulk statement instead.

Bulk update and delete with statements

update() and delete() from the Core work with the ORM too. They run in the database and touch no objects, so they are the right tool for "mark all of these as done".

Python Bulk update and delete statements

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.

By default, a bulk statement also tries to keep objects already loaded in the session in step (synchronize_session). That subtlety matters in chapter 6.

Delete: session.delete()

session.delete(obj) marks the object for deletion, and the DELETE is emitted at flush. After the commit, the object is detached from the database and should not be used again. Deleting through the session lets the ORM apply relationship cascades, which bulk delete() does not.

Python Deleting objects and refresh()

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.

refresh(obj) re-reads the row and overwrites the object's attributes. It is the explicit answer to "my object is out of date".

The whole cycle at a glance

stateDiagram-v2
    [*] --> Transient: Task(...)
    Transient --> Pending: session.add()
    Pending --> Persistent: flush / commit (INSERT)
    Persistent --> Persistent: change attributes, flush (UPDATE)
    Persistent --> Deleted: session.delete()
    Deleted --> Detached: commit (DELETE)
    Persistent --> Detached: session.close()

An object moves through states as you add, flush, delete and close. You can inspect the state with inspect(obj).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does session.get(Task, 5) return if no row has id 5?

    get() returns None when no row matches the primary key.
  2. 2. When is the INSERT for session.add(obj) executed?

    add() only registers the object. SQL is sent at flush time.
  3. 3. You want to mark 100,000 rows as done. Which approach is best?

    A single UPDATE statement runs in the database and avoids loading objects.
  4. 4. Why does s.get(Task, 2) is s.get(Task, 2) evaluate to True inside one session?

    The session's identity map returns the object it already holds for that primary key.

Summary

  • A Session tracks objects: add() and add_all() create, get() and select() read, attribute assignment updates, delete() removes.
  • SQL is sent at flush time (including commit()); flush() writes without committing.
  • Use update() and delete() statements for bulk changes, and refresh() to reload an object.
  • Within a session each row maps to one object (identity map).

Next: Mixins, Inheritance and Hybrid Properties