Engines and Sessions
24 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Your code does not talk to the database directly. SQLAlchemy puts two objects between them: the engine, which knows how to connect, and the session, which keeps track of your changes until you commit them.
The engine knows how to connect
Engine is the SQLAlchemy object that holds the database URL and a pool of connections. An app normally creates one engine and shares it everywhere.
Your app needs a way to reach the database. SQLAlchemy builds it from a URL. The URL names the database type, and for SQLite in memory it is just sqlite://.
from sqlalchemy import create_engine, text
engine = create_engine("sqlite://")with engine.connect() as conn: print(conn.execute(text("select 1 + 1")).scalar_one())Example 1 · Plain SQL through a connection
from sqlalchemy import create_engine, text
engine = create_engine("sqlite://")with engine.connect() as conn: conn.execute(text("create table product (id integer primary key, name text, price integer)")) conn.execute(text("insert into product (name, price) values (:n, :p)"), {"n": "Keyboard", "p": 1200}) conn.commit() rows = conn.execute(text("select id, name, price from product")).all()print(rows)print(engine.dialect.name)[(1, 'Keyboard', 1200)] sqlite
What this code does
:nand:pare bound parameters. SQLAlchemy passes them safely, so you never build SQL with string joins.- A connection starts a transaction. The insert is saved only because of
conn.commit().
create_engine does not connect yet. The first real connection opens when you ask for one. Create the engine once for the whole app.
The session collects your changes
Session is the SQLAlchemy object that tracks the objects you load and change, and sends them to the database in one transaction when you commit.
Working with raw connections means writing SQL by hand. A session lets you work with Python objects instead. You add objects to it, and it decides when to send the SQL.
with Session(engine) as session: customer = Customer(name="Asha") session.add(customer) print(customer.id) session.flush() print(customer.id)Example 2 · Add, flush and the identity map
from sqlalchemy import create_engine, select, func, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50))
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
with Session(engine) as session: c = Customer(name="Asha") print(c.id) session.add(c) session.flush() print(c.id) print(session.get(Customer, 1) is c) session.commit()None 1 True
What this code does
- Before the flush the id is
None. After it, the database has given id 1. session.getfinds the object in the identity map, so it is the same object and no new query is needed.
Before the flush the new customer has no id, because no row exists yet. The flush sends the INSERT, and the database gives the id.
Commit, rollback and the begin block
Unit of work is one job that must succeed or fail as a whole, such as placing an order. In SQLAlchemy it is one session and one transaction.
A transaction groups changes so they succeed or fail together. commit() makes them permanent. rollback() throws them away. A begin() block does the right one for you.
try: with Session(engine) as session, session.begin(): session.add(Customer(name="Ravi")) raise ValueError("payment failed")except ValueError: print("rolled back")Example 3 · Rollback after a flush
from sqlalchemy import create_engine, select, func, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50))
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def count(): with Session(engine) as s: return s.scalar(select(func.count()).select_from(Customer))
with Session(engine) as session: session.add(Customer(name="Ravi")) session.commit()with Session(engine) as session: session.add(Customer(name="Temp")) session.flush() session.rollback()print(count())1
What this code does
- Ravi was committed, so he stays. Temp was flushed but never committed.
- The rollback undoes the flushed INSERT, so one row remains.
Example 4 · begin() commits or rolls back
from sqlalchemy import create_engine, select, func, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50))
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def names(): with Session(engine) as s: return s.scalars(select(Customer.name).order_by(Customer.name)).all()
with Session(engine) as session, session.begin(): session.add(Customer(name="Asha"))try: with Session(engine) as session, session.begin(): session.add(Customer(name="Ravi")) raise ValueError("stop")except ValueError: passprint(names())['Asha']
What this code does
- The first block ends cleanly and commits Asha.
- The second block raises, so Ravi is rolled back. Only Asha is in the table.
The block ended with an error, so the session rolled back and Ravi was never saved. Without an error, the block commits when it ends.
Next step
Check your understanding
11 quick questions