SQLAlchemy ORM Basics

In enterprise software engineering, connecting object-oriented application code with relational database schemas is a fundamental challenge. SQLAlchemy is the standard Object-Relational Mapping (ORM) toolkit for Python.

With the release of SQLAlchemy 2.0, the library unified its Core and ORM APIs, introduced first-class support for Python type annotations (Mapped, mapped_column), and transitioned to a declarative SQL-like querying paradigm.

1. The Modern SQLAlchemy 2.0 Declarative Architecture

SQLAlchemy maps Python classes to relational database tables. In 2.0, classes inherit from DeclarativeBase and define column attributes using type-annotated descriptors:

2. Engine, Metadata, and Session Lifecycle

Three primary architectural constructs manage database operations:

1. 1
   Engine: The low-level connection pool and SQL dialect translator. Created via create_engine().
2. 2
   Session: The Unit of Work and Identity Map pattern coordinator. It tracks changes to objects and flushes transactions.
3. 3
   DeclarativeBase: The root class collecting schema metadata and table definitions.

The Four States of an ORM Entity

• Transient: Newly instantiated object (User(...)); not associated with any session; has no database identity.
• Pending: Added to a session (session.add(u)); not yet flushed to the database.
• Persistent: Flushed or queried from the database; has a primary key; tracked in session.
• Detached: The session was closed; the object remains in memory, but changes to it are untracked.

3. Production Implementation: Schema Definition & CRUD

4. Modern 2.0 Querying: select() and session.scalars()

In legacy SQLAlchemy 1.x, querying relied on session.query(User).filter(...).

In SQLAlchemy 2.0, all queries use explicit select() statements:

• session.execute(select(User)): Returns a Result containing row tuples (User,).
• session.scalars(select(User)): Automatically unwraps single-entity rows into ScalarResult containing User instances directly.

5. Architectural Summary Table

Multiple Choice Questions

1.

How are table columns defined with strict type-safety in modern SQLAlchemy 2.0? A. col = Column(Integer) B. col: Mapped[int] = mapped_column(...) C. col = Field(int) D. col = db.Integer()

2.

What is the difference between session.execute(select(User)) and session.scalars(select(User))? A. execute() only works for inserts, while scalars() works for selects. B. execute() returns rows of tuples (User,), whereas scalars() unwraps the first column of each row into raw scalar ORM instances. C. scalars() does not support where clauses. D. execute() bypasses the database engine.

3.

What is the state of a newly created ORM object user = User(name="Alex") before session.add(user) is executed? A. Persistent B. Pending C. Transient D. Detached

4.

What does Base.metadata.create_all(engine) do? A. Deletes all data from the database. B. Inspects all mapped models registered under Base and issues CREATE TABLE DDL statements for any tables that do not yet exist in the database. C. Compiles Python code to SQLite binaries. D. Drops the database connection pool.

5.

Why should SQLAlchemy sessions always be managed using a with Session(engine) as session: context manager block? A. Because Python refuses to compile sessions outside of with blocks. B. To guarantee that database connections are properly closed, pooled, and cleaned up upon exit, avoiding connection leaks. C. To turn on SQLite WAL mode. D. To disable transaction isolation.