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 pageshowhide
explore
- Engine Layer11 questions
- Connection URLs & Results5 questions
- Pool Sizing & Health6 questions
- Core Expression Language17 questions
- Table Metadata & Reflection5 questions
- Composing Selects6 questions
- Insert, Update & Upsert6 questions
- Declarative Mapping21 questions
- Mapped Classes & Annotations5 questions
- Related Objects & Cascades5 questions
- Polymorphic Class Hierarchies5 questions
- Hybrids & Custom Types6 questions
- Unit-of-Work Session11 questions
- Transaction Scope & Commit5 questions
- Flush, Identity & Expiry6 questions
- Querying Mapped Classes17 questions
- Entity Selects & Results6 questions
- Loader Strategy Options5 questions
- Bulk Statement Writes6 questions
- Asyncio Extension11 questions
- Async Engines & Sessions5 questions
- Implicit IO Errors6 questions
- Migrations with Alembic12 questions
- Autogenerated Revisions6 questions
- Revision Chains & Deploys6 questions
- Hooks & Diagnostics12 questions
- Engine & Mapper Listeners6 questions
- Test Transactions & SQL Logs6 questions
questions
112 · 8 sectionsIn SQLAlchemy, how do you pass values into a text() statement, and what does SQLAlchemy do with the :name placeholders?
basics
~20 sSQLAlchemy'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.
In SQLAlchemy 2.1, what does a create_engine() URL encode, and how do you pass a password containing @ or / safely?
basics
~10 sA 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.
In SQLAlchemy 2.x, how does engine.connect() with Connection.commit() differ from engine.begin(), and what happens to uncommitted work?
basics
~20 sengine.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.
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?
basics
~20 spool_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.
In SQLAlchemy, how do Result.one(), first(), one_or_none(), scalar_one(), scalars() and mappings() differ, and when do they raise?
basics
~20 sSQLAlchemy'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.
In SQLAlchemy Core, how do MetaData, Table and Column fit together, and what does metadata.create_all() do with a table that already exists?
basics
~20 sMetaData 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.
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?
basics
~10 sStart 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.
In SQLAlchemy Core, how does a select() send Python values to the database, and how do you see the SQL it will emit?
basics
~20 sPython 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.
In SQLAlchemy Core, how do you insert one row with insert().values() versus many rows passed as a list to Connection.execute()?
basics
~20 sFor 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.
In SQLAlchemy 2.1, how does the insertmanyvalues feature let an executemany INSERT with returning() send back every new row?
basics
~10 sBecause 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.
In SQLAlchemy's 2.0-style ORM, how do you declare a mapped class with DeclarativeBase, Mapped[] and mapped_column()?
basics
~10 sSubclass 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.
In SQLAlchemy 2.1, how do you map an Author with many Posts using relationship() and back_populates, and what does back_populates do?
basics
~10 sPut 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.
In SQLAlchemy declarative mapping, how does a Mapped[] annotation decide whether a column is NULL or NOT NULL, and what overrides it?
basics
~10 sAn 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.
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?
basics
~20 sThe 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.
In SQLAlchemy, what does a Mapped[Status] column store for a Python enum.Enum member, and how do you store its value instead?
basics
~20 sA 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]).
In SQLAlchemy 2.1, how does `with Session() as session, session.begin():` differ from `with Session() as session:` followed by `session.commit()`?
basics
~20 sThe 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.
In SQLAlchemy 2.1, when does a Session autoflush, and why can an IntegrityError surface on a statement that only reads?
basics
~20 sWith 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.
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?
basics
~20 sSession.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.
A SQLAlchemy Product returned after its Session closed raises DetachedInstanceError when a report reads product.stock; why, and how do you fix it?
basics
~20 sA 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.
In SQLAlchemy 2.1, what does Session.get(Product, 5) do when called twice in one Session, and how does a select() differ?
basics
~20 sSession.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.
In SQLAlchemy 2.1, what SQL does relationship()'s default lazy='select' strategy emit when code first reads Order.customer and Order.lines?
basics
~20 sNothing 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.
With a SQLAlchemy Session, what does session.execute(select(Ticket)) return compared with session.scalars(select(Ticket)), and when do you use each?
basics
~20 ssession.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.
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?
basics
~20 sselectinload() 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().
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?
basics
~10 sSelect 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.
With a SQLAlchemy 2.1 Session, how do you update or delete every Product row that matches a condition without loading the objects?
basics
~10 sBuild 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.
With SQLAlchemy's AsyncSession, which attribute accesses on a mapped object raise MissingGreenlet, and why do they fail instead of loading?
basics
~20 sUnder 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.
In SQLAlchemy 2.1, how do create_async_engine, async_sessionmaker and AsyncSession fit together to run one unit of work?
basics
~20 screate_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().
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?
basics
~20 sBy 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.
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?
basics
~20 sawait 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.
In Alembic, what must env.py and alembic.ini provide before `alembic revision --autogenerate` can draft a migration from SQLAlchemy models?
basics
~20 senv.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.
In Alembic, how do `revision`, `down_revision` and the `alembic_version` table decide which scripts `alembic upgrade head` runs?
basics
~20 sEach 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.
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?
basics
~20 sAlembic 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.
For Alembic autogenerate, why should a SQLAlchemy MetaData carry a naming_convention before the first revision is drafted?
basics
~20 sWithout 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.
On SQLite, why does an Alembic autogenerated op.alter_column() fail at upgrade, and what does render_as_batch=True change in the draft?
basics
~10 sSQLite'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.
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()?
basics
~10 sOpen 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.
In SQLAlchemy 2.1, what does the @validates decorator do on a mapped class, and when does it not run?
basics
~20 sSQLAlchemy'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.
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?
basics
~20 sPass 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.
In SQLAlchemy, why do the docs steer audit stamping from mapper before_update listeners to Session before_flush, and what may each one change?
basics
~20 sSession 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.
With SQLAlchemy's before_cursor_execute and after_cursor_execute events, how do you log every statement slower than a set threshold?
basics
~20 sRegister 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.