Python SQLAlchemy: Insert, Select, Update, and Delete

python sqlalchemy insert select update and delete requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

SQLAlchemy's ORM routes insert, select, update, and delete operations through a Session object. The session tracks loaded objects, queues pending changes, and translates those changes into SQL statements when you flush. Understanding how the session manages this lifecycle is what separates working CRUD code from code that silently misses updates or raises DetachedInstanceError.

For the examples in this article, assume a minimal model and session setup:

from sqlalchemy import create_engine, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker

engine = create_engine("sqlite:///app.db")
SessionLocal = sessionmaker(bind=engine)

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(255), unique=True)

This uses SQLAlchemy 2.0's declarative style. The sessionmaker produces new Session objects bound to the engine. Every CRUD example below assumes a session created with session = SessionLocal().

Inserting Rows with add() and add_all()

To insert a row, construct a model instance and pass it to session.add(). The row is not written to the database until the session flushes.

user = User(name="Ada Lovelace", email="ada@example.com")
session.add(user)
session.commit()

commit() flushes the pending insert and ends the transaction. After the flush, user.id is populated with the database-generated primary key.

For multiple rows, add_all() avoids repeated calls:

users = [
    User(name="Grace Hopper", email="grace@example.com"),
    User(name="Alan Turing", email="alan@example.com"),
]
session.add_all(users)
session.commit()

One important detail: add() does not execute SQL immediately. If you need the generated primary key before committing, call session.flush() explicitly. The flush emits the INSERT statement while keeping the transaction open. This matters when you must insert a parent row and then reference its ID while constructing child rows in the same transaction.

Selecting Rows with select() and scalars()

Selection uses the select() construct together with session.scalars() for ORM-mapped results.

from sqlalchemy import select

stmt = select(User).where(User.email == "ada@example.com")
user = session.scalars(stmt).one()

session.scalars() returns a ScalarResult, which yields ORM instances rather than row tuples. .one() raises if the result does not contain exactly one row. For queries that may return zero rows, use .first() or iterate the result.

stmt = select(User).where(User.name.like("A%")).order_by(User.name)
users = session.scalars(stmt).all()

The select() statement is lazy: constructing it builds SQL text only when the session executes it. The query runs against the database at that point, and the returned objects are attached to the session's identity map. Two loads of the same primary key within the same session return the same Python object.

session.get() is shorter when you only need a row by primary key:

user = session.get(User, 1)

Use get() for single-row lookups by ID and reserve select() for filtering, ordering, and joins.

Updating Rows with Attribute Assignment and update()

The ORM-style update loads an object, changes attributes, and commits. The session detects the changes when it flushes.

user = session.get(User, 1)
user.name = "Ada King"
session.commit()

This works because the loaded object is tracked. The flush compares the current attribute values against the snapshot taken when the object was loaded and emits an UPDATE only for changed columns.

For updates that do not require loading objects into memory, use the update() construct:

from sqlalchemy import update

stmt = (
    update(User)
    .where(User.id == 1)
    .values(name="Ada King")
)
session.execute(stmt)
session.commit()

The update() statement runs directly on the database and does not load the affected rows. By default, SQLAlchemy synchronizes the session with the database after execution, which is controlled by the synchronize_session parameter. The default behavior is safe for most cases; synchronize_session="fetch" re-selects the affected rows to refresh the session state, while "evaluate" uses Python-side evaluation. When you update rows that are not currently loaded in the session, the default works without extra round trips.

Deleting Rows with delete() and session.delete()

For a loaded ORM object, session.delete() marks it for deletion:

user = session.get(User, 1)
session.delete(user)
session.commit()

The DELETE statement is emitted during flush. After commit, the object is detached from the session, and accessing its attributes raises DetachedInstanceError unless you re-load it.

For deleting rows without loading them, use the delete() construct:

from sqlalchemy import delete

stmt = delete(User).where(User.email == "old@example.com")
result = session.execute(stmt)
session.commit()

result.rowcount reports the number of rows deleted, though the accuracy depends on the database driver. The same synchronize_session considerations apply here: the default synchronizes the session, but rows not present in the session are simply removed from the database.

Flush, Commit, and Rollback Behavior

The session batches changes until a flush. A flush can happen implicitly during commit() or explicitly via session.flush(). Understanding the distinction prevents two common bugs.

If you call session.flush() and then raise an exception before committing, the transaction remains open and must be rolled back:

try:
    session.add(User(name="Temporary", email="temp@example.com"))
    session.flush()
    # ... further work that fails
    session.commit()
except Exception:
    session.rollback()
    raise

rollback() discards all uncommitted changes in the current transaction and expires the session's objects. After a rollback, previously loaded objects are expired, and accessing their attributes triggers a fresh SELECT. If you need to reuse the session after an error, roll back first; otherwise, the session remains in a partially failed state that can produce confusing errors on the next flush.

Bulk Operations and Session Lifecycle

For large inserts, session.add_all() with thousands of objects can be slow because the session accumulates every object in its identity map. A common pattern is to flush and commit in batches:

batch = []
for i in range(10_000):
    batch.append(User(name=f"user-{i}", email=f"user-{i}@example.com"))
    if len(batch) >= 500:
        session.add_all(batch)
        session.commit()
        batch.clear()

This keeps the identity map small and avoids holding one giant transaction open. The same reasoning applies to large delete() operations: deleting in batches reduces lock contention and transaction size on most databases.

The session is not thread-safe. A Session instance is intended for a single thread and a single unit of work. In a web application, create a session per request and close it when the request finishes. Reusing one session across concurrent threads leads to race conditions that are difficult to reproduce.

For read-heavy paths, session.scalars() returns fully tracked ORM objects, which costs memory. If you only need a few columns, selecting specific columns returns lightweight row tuples instead:

stmt = select(User.name, User.email).where(User.id == 1)
row = session.execute(stmt).one()

This avoids loading full ORM objects and keeps the identity map smaller, at the cost of losing attribute-style access and automatic tracking.