TopolearnEvery SQLAlchemy program, Core or ORM, starts with an Engine, and its commit-as-you-go model is the basis of all transaction handling.
SQLAlchemy is Python's main database toolkit. It has two layers: Core, a SQL expression language plus connection management, and the ORM, which maps classes to tables on top of Core. Version 2.x uses one consistent "2.0 style" API for both, which is what this course teaches.
The starting point is the Engine, created once per database URL with create_engine("postgresql+psycopg://user:pw@host/db"). The URL names the dialect (postgresql, mysql, sqlite) and optionally the DBAPI driver (psycopg, pymysql). The Engine holds a connection pool; it doesn't connect until you ask it to.
engine.connect() checks out a Connection, best used as a context manager so it returns to the pool. conn.execute(text("SELECT ...")) runs SQL; text() marks a string as SQL and lets you use named bound parameters like :name, passed as a dict. Never format values into the SQL string yourself.
Transactions are explicit. A Connection begins a transaction automatically on first use, and nothing is persisted until you call conn.commit(); leaving the block without committing rolls back. If you prefer the block itself to commit, use engine.begin(), which commits on success and rolls back on an exception.
Results are iterable Row objects that behave like named tuples: row.name or row[0]. Pass echo=True to create_engine while learning to see every SQL statement it emits.
Example
from sqlalchemy import create_engine, text
engine = create_engine("sqlite://") # in-memory SQLite
with engine.connect() as conn:
conn.execute(text("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"))
conn.execute(text("INSERT INTO users (name) VALUES (:name)"),
[{"name": "ada"}, {"name": "linus"}])
conn.commit() # without this, the inserts are rolled back
with engine.begin() as conn: # commits automatically at the end
conn.execute(text("UPDATE users SET name = 'Ada' WHERE id = :id"), {"id": 1})
with engine.connect() as conn:
for row in conn.execute(text("SELECT id, name FROM users ORDER BY id")):
print(row.id, row.name)
# 1 Ada
# 2 linusRemember
- One Engine per database URL per process; it owns the connection pool
- text() + named :params for raw SQL, never string formatting
- connect() needs an explicit commit(); begin() commits for you
- Rows behave like named tuples
Watch out for
- Forgetting conn.commit() and wondering why the data vanished.
- Creating a new Engine per request, which throws away the pool.