skip to content

SQLAlchemy

SQLAlchemy is Python's standard SQL toolkit and ORM: a Core expression language under a Session-based mapper. Interviewers probe sessions, loading and async use in web apps.

on this pageshow

explore

questions

112 · 8 sections

In SQLAlchemy, how do you pass values into a text() statement, and what does SQLAlchemy do with the :name placeholders?

level: juniorimportance: must knowfreq 55%
basics
~20 s

SQLAlchemy's text() marks values as :name placeholders, and Connection.execute() takes their values as a dict, or a list of dicts for executemany. SQLAlchemy rewrites each placeholder into the driver's parameter style and sends values separately from the SQL.

open as a page

In SQLAlchemy 2.1, what does a create_engine() URL encode, and how do you pass a password containing @ or / safely?

level: juniorimportance: must knowfreq 60%
basics
~10 s

A SQLAlchemy URL reads dialect+driver://user:password@host:port/database, and create_engine() turns it into a dialect, a DBAPI driver and a pool. Percent-encode special password characters, or build the URL with URL.create() and pass them raw.

open as a page

In SQLAlchemy 2.x, how does engine.connect() with Connection.commit() differ from engine.begin(), and what happens to uncommitted work?

level: middleimportance: must knowfreq 66%
basics
~20 s

engine.connect() gives a Connection that autobegins a transaction and commits only when you call commit(); closing it rolls back anything uncommitted. engine.begin() wraps the block in one transaction that commits on normal exit and rolls back on an exception.

open as a page

In SQLAlchemy 2.1, what do create_engine's pool_size, max_overflow and pool_timeout control, and what happens when every pooled connection is checked out?

level: middleimportance: must knowfreq 48%
basics
~20 s

pool_size (default 5) is how many connections the QueuePool keeps open when idle, max_overflow (default 10) how many extra it may open, and pool_timeout (default 30 s) how long a checkout waits at that ceiling before raising TimeoutError.

open as a page

In SQLAlchemy, how do Result.one(), first(), one_or_none(), scalar_one(), scalars() and mappings() differ, and when do they raise?

level: middleimportance: should knowfreq 48%
basics
~20 s

SQLAlchemy's one() demands exactly one row, raising NoResultFound or MultipleResultsFound; one_or_none() allows zero; first() returns the first row or None and silently discards the rest. scalar_one() and scalars() return column values instead of rows; mappings() returns dict-like rows.

open as a page

In SQLAlchemy Core, how do MetaData, Table and Column fit together, and what does metadata.create_all() do with a table that already exists?

level: juniorimportance: must knowfreq 55%
basics
~20 s

MetaData holds Table objects, each built from Column and constraint objects. metadata.create_all(engine) checks which tables already exist, creates only the missing ones in foreign-key order, and never alters an existing table, even when its Python definition changed.

open as a page

In SQLAlchemy Core, how do you build a select() step by step with where(), group_by(), order_by() and limit(), and why reassign each call?

level: juniorimportance: must knowfreq 62%
basics
~10 s

Start from select(columns) and chain where(), join(), group_by(), having(), order_by() and limit(). Each call returns a new Select and leaves the original untouched, so a call whose result is not reassigned is silently lost.

open as a page

In SQLAlchemy Core, how does a select() send Python values to the database, and how do you see the SQL it will emit?

level: middleimportance: must knowfreq 58%
basics
~20 s

Python values compared in an expression become bound parameters: a placeholder in the SQL, with the value sent to the driver separately. print(stmt) shows generic SQL; stmt.compile(dialect=...) shows the target's SQL and compiled.params the values.

open as a page

In SQLAlchemy Core, how do you insert one row with insert().values() versus many rows passed as a list to Connection.execute()?

level: juniorimportance: should knowfreq 45%
basics
~20 s

For one row, build insert(table).values(col=value) and execute it. For many rows, execute a bare insert(table) with a list of dictionaries: SQLAlchemy compiles one INSERT from the first dictionary's keys and runs it as an executemany.

open as a page

In SQLAlchemy 2.1, how does the insertmanyvalues feature let an executemany INSERT with returning() send back every new row?

level: middleimportance: should knowfreq 30%
basics
~10 s

Because a DB-API executemany cannot return rows, SQLAlchemy 2.1 normally rewrites the INSERT ... RETURNING into batched multi-row VALUES statements, runs each with cursor.execute(), and joins their returned rows into one Result.

open as a page

In SQLAlchemy's 2.0-style ORM, how do you declare a mapped class with DeclarativeBase, Mapped[] and mapped_column()?

level: juniorimportance: must knowfreq 80%
basics
~10 s

Subclass DeclarativeBase once as Base, then give each model a tablename and Mapped[] annotated attributes, using mapped_column() for keys, lengths and options. SQLAlchemy builds a Table in Base.metadata and maps the class to it.

open as a page

In SQLAlchemy 2.1, how do you map an Author with many Posts using relationship() and back_populates, and what does back_populates do?

level: juniorimportance: must knowfreq 70%
basics
~10 s

Put ForeignKey("authors.id") on Post.author_id, declare Author.posts: Mapped[list["Post"]] and Post.author: Mapped["Author"], each with relationship(back_populates=...) naming the other attribute. back_populates keeps both Python sides in step before any flush.

open as a page

In SQLAlchemy declarative mapping, how does a Mapped[] annotation decide whether a column is NULL or NOT NULL, and what overrides it?

level: middleimportance: must knowfreq 65%
basics
~10 s

An explicit mapped_column(nullable=...) decides first. Otherwise Mapped[str | None] or Optional[str] gives NULL and plain Mapped[str] gives NOT NULL, as for a Mapped[int] primary key. With no Mapped[] annotation, a non-key column is nullable.

open as a page

With SQLAlchemy's default relationship() cascade, what happens to a Post's Comments when the post is deleted, and what does "all, delete-orphan" change?

level: middleimportance: must knowfreq 48%
basics
~20 s

The default cascade is "save-update, merge", so deleting a Post sets each Comment's post_id to NULL, failing on a NOT NULL key. "all, delete-orphan" deletes the comments with the post and deletes any comment removed from post.comments.

open as a page

In SQLAlchemy, what does a Mapped[Status] column store for a Python enum.Enum member, and how do you store its value instead?

level: juniorimportance: should knowfreq 32%
basics
~20 s

A Mapped[Status] annotation maps to SQLAlchemy's Enum type, which writes each member's name (PAST_DUE), not its value, and returns Status members on load. To store the values, pass Enum(Status, values_callable=lambda cls: [m.value for m in cls]).

open as a page

In SQLAlchemy 2.1, how does `with Session() as session, session.begin():` differ from `with Session() as session:` followed by `session.commit()`?

level: juniorimportance: must knowfreq 72%
basics
~20 s

The begin-once form commits when its block succeeds, rolls back and re-raises on an error, then closes the Session. The commit-as-you-go form autobegins on first use, commits only at each session.commit(), and close() rolls back whatever came after.

open as a page

In SQLAlchemy 2.1, when does a Session autoflush, and why can an IntegrityError surface on a statement that only reads?

level: middleimportance: must knowfreq 50%
basics
~20 s

With autoflush on, the default, a SQLAlchemy 2.1 Session flushes its pending INSERTs, UPDATEs and DELETEs before every statement it executes, text() included. A constraint those writes break therefore fails on the read that triggered the flush.

open as a page

What does SQLAlchemy's `expire_on_commit=True` default do to a Session's objects after commit, and what does it cost the code that reads them?

level: middleimportance: must knowfreq 62%
basics
~20 s

Session.commit() expires every object it holds, erasing the loaded values. The next attribute read autobegins a transaction and SELECTs that row again, so reads after commit cost extra queries, and objects from a closed Session cannot reload.

open as a page

A SQLAlchemy Product returned after its Session closed raises DetachedInstanceError when a report reads product.stock; why, and how do you fix it?

level: seniorimportance: must knowfreq 45%
basics
~20 s

A detached object cannot load anything, and commit() expired its attributes by default, so reading product.stock needs a SELECT that has no Session to run in. Load what the report needs before the Session closes, or reattach the object to a new Session.

open as a page

In SQLAlchemy 2.1, what does Session.get(Product, 5) do when called twice in one Session, and how does a select() differ?

level: juniorimportance: should knowfreq 50%
basics
~20 s

Session.get() checks the Session's identity map before sending SQL, so a second get(Product, 5) returns the same object with no SELECT. A select() always queries, but returns that same object and leaves its loaded attributes as they were.

open as a page

In SQLAlchemy 2.1, what SQL does relationship()'s default lazy='select' strategy emit when code first reads Order.customer and Order.lines?

level: juniorimportance: must knowfreq 70%
basics
~20 s

Nothing at query time. On first access each Order fires its own SELECT for its lines, and Order.customer fires a SELECT by primary key unless that Customer is already in the Session, in which case no SQL runs.

open as a page

With a SQLAlchemy Session, what does session.execute(select(Ticket)) return compared with session.scalars(select(Ticket)), and when do you use each?

level: juniorimportance: must knowfreq 70%
basics
~20 s

session.execute(select(Ticket)) returns a Result of Row objects, each a one-element row holding a Ticket; session.scalars() returns a ScalarResult that yields the Ticket objects themselves. Use scalars() for one entity, execute() when rows carry several values.

open as a page

In SQLAlchemy 2.1, how do selectinload() and joinedload() differ in the SQL they emit, and which suits a collection versus a many-to-one?

level: middleimportance: must knowfreq 75%
basics
~20 s

selectinload() runs a second SELECT whose IN list holds the parents' keys; joinedload() adds a LEFT OUTER JOIN to the main SELECT. The guide recommends selectinload() for collections and joinedload() for many-to-ones, and a joined collection needs unique().

open as a page

With a SQLAlchemy 2.1 Session, how do you list open tickets with each customer's name in one query, and what does each result row contain?

level: middleimportance: must knowfreq 58%
basics
~10 s

Select both and join along the relationship: select(Ticket, Customer.name).join(Ticket.customer). Session.execute() then yields Row objects pairing a Ticket object with a string, read by unpacking or as row.Ticket and row.name.

open as a page

With a SQLAlchemy 2.1 Session, how do you update or delete every Product row that matches a condition without loading the objects?

level: juniorimportance: should knowfreq 35%
basics
~10 s

Build update(Product).where(...).values(...) or delete(Product).where(...) and pass it to Session.execute(). SQLAlchemy sends one UPDATE or DELETE for all matching rows, loads no objects, and commits with the session's transaction.

open as a page

With SQLAlchemy's AsyncSession, which attribute accesses on a mapped object raise MissingGreenlet, and why do they fail instead of loading?

level: middleimportance: must knowfreq 45%
basics
~20 s

Under AsyncSession, any attribute access that would emit a SELECT raises MissingGreenlet: an unloaded lazy relationship, an attribute expired by commit, a deferred column, or an unfetched server default. Plain attribute access cannot await, so SQLAlchemy refuses the IO.

open as a page

In SQLAlchemy 2.1, how do create_async_engine, async_sessionmaker and AsyncSession fit together to run one unit of work?

level: middleimportance: must knowfreq 50%
basics
~20 s

create_async_engine builds one AsyncEngine and its pool per process; async_sessionmaker is a factory with fixed session settings; each unit of work opens its own AsyncSession with async with, wraps writes in session.begin(), and shutdown awaits engine.dispose().

open as a page

An async SQLAlchemy handler loads an Order, commits, then reads order.customer.name and order.items and gets MissingGreenlet; what causes it, and what is the fix?

level: seniorimportance: must knowfreq 48%
basics
~20 s

By default commit() expires every loaded attribute, and customer and items were never loaded, so both reads need a SELECT that plain attribute access cannot await. Set expire_on_commit=False and load both relationships with loader options in the query.

open as a page

An async SQLAlchemy ingestion worker shares one AsyncSession across tasks started with asyncio.gather and fails with InvalidRequestError; why, and how should it be structured?

level: seniorimportance: must knowfreq 45%
basics
~20 s

An AsyncSession is one unsynchronised transaction on one connection, so gathered tasks race on its state and SQLAlchemy can raise errors such as "This session is provisioning a new connection; concurrent operations are not permitted". Give each task its own AsyncSession from one async_sessionmaker.

open as a page

Under SQLAlchemy's asyncio extension, how do AsyncAttrs.awaitable_attrs and AsyncSession.refresh() with attribute names load an attribute on demand, and how do they differ?

level: middleimportance: should knowfreq 32%
basics
~20 s

await obj.awaitable_attrs.items runs the attribute's normal loader inside an awaitable, so it loads only what is missing. await session.refresh(obj, ["items"]) expires the named attributes and re-selects them, loading a lazy relationship only when it is named.

open as a page

In Alembic, what must env.py and alembic.ini provide before `alembic revision --autogenerate` can draft a migration from SQLAlchemy models?

level: juniorimportance: must knowfreq 70%
basics
~20 s

env.py must set target_metadata to the models' MetaData, usually Base.metadata with every model module imported, and Alembic needs a database URL. Autogenerate diffs that metadata against the live schema and writes a candidate script to review.

open as a page

In Alembic, how do `revision`, `down_revision` and the `alembic_version` table decide which scripts `alembic upgrade head` runs?

level: juniorimportance: must knowfreq 50%
basics
~20 s

Each Alembic script names itself in revision and its parent in down_revision, forming a chain. alembic upgrade head reads the stored revision from alembic_version, runs every upgrade() between it and the newest script, and records each step.

open as a page

In Alembic, after models rename a table, add a column, change a column's type and add a named CHECK constraint, what does autogenerate draft?

level: middleimportance: must knowfreq 75%
basics
~20 s

Alembic drafts the new column and the type change correctly, turns the table rename into op.create_table() plus op.drop_table(), which would lose the rows, and leaves out the CHECK constraint, because CHECK detection is off by default.

open as a page

For Alembic autogenerate, why should a SQLAlchemy MetaData carry a naming_convention before the first revision is drafted?

level: middleimportance: should knowfreq 50%
basics
~20 s

Without a convention, unique, foreign-key and CHECK constraints get whatever name the database invents, or none, so autogenerate drafts them as None and later drops cannot target them. A naming_convention makes names predictable; adopted late, it churns existing ones.

open as a page

On SQLite, why does an Alembic autogenerated op.alter_column() fail at upgrade, and what does render_as_batch=True change in the draft?

level: middleimportance: should knowfreq 35%
basics
~10 s

SQLite's ALTER TABLE cannot change a column's type, nullability or constraints, so a plain op.alter_column() fails. render_as_batch=True makes autogenerate wrap table changes in op.batch_alter_table(), which on SQLite rebuilds the table and copies the rows.

open as a page

In SQLAlchemy 2.1, how do you bind a test's Session to an outer transaction so its writes roll back even after Session.commit()?

level: middleimportance: must knowfreq 30%
basics
~10 s

Open a Connection, call connection.begin(), and build the Session with bind=connection and join_transaction_mode="create_savepoint". The Session's commits then only release SAVEPOINTs; at teardown, rolling back the outer transaction discards everything the test wrote.

open as a page

In SQLAlchemy 2.1, what does the @validates decorator do on a mapped class, and when does it not run?

level: juniorimportance: should knowfreq 30%
basics
~20 s

SQLAlchemy's @validates marks a method that runs whenever the named attribute is set or its collection appended to; it raises to reject a value or returns the value to store. Loading rows, bulk and Core statements skip it.

open as a page

In SQLAlchemy 2.1, how do you see the SQL an engine sends during a test run, and how does echo=True differ from the 'sqlalchemy.engine' logger?

level: juniorimportance: should knowfreq 35%
basics
~20 s

Pass echo=True to create_engine to print each statement and its parameters to standard output, or set the 'sqlalchemy.engine' logger to INFO (DEBUG adds rows). The logger joins your logging setup; echo adds its own handler and can duplicate lines.

open as a page

In SQLAlchemy, why do the docs steer audit stamping from mapper before_update listeners to Session before_flush, and what may each one change?

level: middleimportance: should knowfreq 35%
basics
~20 s

Session before_flush runs before SQLAlchemy plans the flush, so it may add, delete or change any object. Mapper before_update runs mid-flush with the plan fixed: only the row's own column attributes and SQL on the given Connection are safe.

open as a page

With SQLAlchemy's before_cursor_execute and after_cursor_execute events, how do you log every statement slower than a set threshold?

level: middleimportance: should knowfreq 32%
basics
~20 s

Register both events on the engine: before_cursor_execute pushes a start time onto conn.info, after_cursor_execute pops it and logs the SQL string when the elapsed time passes the threshold, leaving the bound parameters out of the log.

open as a page