skip to content

Core Expression Language

Core builds SQL from Python objects: Table metadata, select() and DML constructs that compile per dialect. Interviewers ask how it differs from the ORM and when Core alone is the better tool.

on this pageshow

explore

questions

17

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%

answer

  1. a registry of schema objects
  2. tables keyed by name
  3. an existence check before CREATE
  4. no ALTER path at all

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.

solid answer

~40 s

`MetaData` is a registry: `Table("invoices", metadata, Column(...), ...)` registers the table in `metadata.tables`, and each `Column` carries a name, a type and flags such as `primary_key`, `nullable`, `unique`, `index` and a `ForeignKey("accounts.id")`. `metadata.create_all(engine)` first checks which tables exist (by default it checks every kind of object; SQLAlchemy 2.1 takes a `CheckFirst` flag or a plain bool), then emits `CREATE TABLE` only for the missing ones, sorted so `accounts` comes before `invoices`, followed by those tables' indexes. A table that already exists is skipped whole: a column or `Index` you added to it in Python is never emitted, because `create_all()` compares names, not definitions. Changing a live table is a migration tool's job. `drop_all()` is the mirror image, dropping in reverse dependency order.

code

python · 25 lines
python
from sqlalchemy import Column, ForeignKey, Integer, MetaData, Numeric, String, Table, create_engine, inspect

metadata = MetaData()

accounts = Table(
    "accounts",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("email", String(255), nullable=False, unique=True),
)

invoices = Table(
    "invoices",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("account_id", ForeignKey("accounts.id"), nullable=False, index=True),
    Column("total", Numeric(12, 2), nullable=False),
)

engine = create_engine("sqlite://")
metadata.create_all(engine)  # CREATE TABLE accounts, CREATE TABLE invoices, CREATE INDEX
metadata.create_all(engine)  # existence checks only: both tables exist, nothing is emitted

print([t.name for t in metadata.sorted_tables])  # ['accounts', 'invoices']
print(inspect(engine).get_table_names())  # ['accounts', 'invoices']

go deeper

for a junior

Recall the roles: MetaData collects tables, Table groups columns and constraints, Column describes one column. Then say plainly that create_all() only creates what is missing.

for a middle

Walk through create_all(): the existence check controlled by checkfirst, the foreign-key sort, CREATE TABLE then CREATE INDEX, and why an index added to an existing table never appears.

for a senior

Show you know where create_all() stops being safe: a changed table is silently skipped, so a service that relies on it at startup drifts from its own code without any error until a query fails.

for a principal

Frame create_all() as a bootstrap tool for empty databases and tests, and say where your team's schema changes go instead, so nobody mistakes a clean startup for an up-to-date schema.

## The three building blocks SQLAlchemy Core describes a database schema with plain Python objects, before any connection exists. - **`MetaData`** is the collection. Every `Table` constructed against it is registered in `metadata.tables`, a dictionary keyed by the table name, or by `schema.name` when the table has a `schema=` argument. `create_all()`, `drop_all()`, `sorted_tables` and `reflect()` all operate on one `MetaData`. - **`Table`** takes a name, the `MetaData` to register in, then `Column` objects and table-level schema items such as `UniqueConstraint`, `CheckConstraint`, `ForeignKeyConstraint` and `Index`. Asking for the same name again in the same `MetaData` hands back the existing object; passing new columns that way raises `InvalidRequestError` unless you add `extend_existing=True`. - **`Column`** carries a name, a type such as `Integer`, `String(255)` or `Numeric(12, 2)`, and flags: `primary_key`, `nullable` (`True` unless the column is in the primary key or has an `Identity`), `unique=True`, which adds a `UniqueConstraint`, and `index=True`, which adds an `Index`. - **`ForeignKey("accounts.id")`** names its target as a string, so tables may be declared in any order. When a `Column` holds a `ForeignKey` and no type, it takes the referenced column's type. ## A shared accounts-and-invoices package Two services can import one module that holds the schema as module-level objects: ```python metadata = MetaData() accounts = Table("accounts", metadata, Column("id", Integer, primary_key=True)) invoices = Table( "invoices", metadata, Column("id", Integer, primary_key=True), Column("account_id", ForeignKey("accounts.id"), nullable=False, index=True), ) ``` Importing that module runs no SQL. The objects are descriptions; both services then use `invoices.c.account_id` in `select()`, `insert()` and the rest of Core. ## What create_all() does, step by step 1. With an `Engine` it opens a connection and a transaction (`engine.begin()`); with a `Connection` it uses yours. 2. It checks which of the metadata's tables already exist. The `checkfirst` argument controls the check, and its default checks everything. SQLAlchemy 2.1 batches the table check into fewer round trips on dialects that implement it, PostgreSQL among them. 3. It sorts the missing tables by foreign-key dependency, so `accounts` is created before `invoices`. 4. For each missing table it emits `CREATE TABLE`, then `CREATE INDEX` for that table's indexes. 5. The transaction commits when the block ends. Run it again and step 2 finds both tables, so nothing is emitted. That makes it safe for a fresh database: a test run, a prototype, a local container. ## What create_all() never does - add a column you appended to an existing `Table`; - create an `Index` declared on a table that already exists, since indexes are emitted only for tables created in the same call; - change a type, a nullability or a server default; - drop a table or a column you deleted from the Python code. The existence check asks only whether a table of that name exists; it never compares definitions. Evolving a live schema with `ALTER TABLE` belongs to a migration tool, a separate subject. ## The checkfirst argument in 2.1 | value | what is checked before CREATE | |---|---| | `True` or `CheckFirst.ALL` (the `create_all()` and `drop_all()` default) | tables, views, indexes, sequences and server-side types | | `CheckFirst.TABLES` | tables only | | `False` or `CheckFirst.NONE` | nothing; an existing table makes the database raise | `CheckFirst` is a flag enumeration new in 2.1, so values combine with `|`. `Table.create(engine)` keeps a narrower default, `CheckFirst.TYPES`, so it does not look for the table at all and fails if it exists, unless you pass `checkfirst=True`. ## drop_all() and sorted_tables `metadata.sorted_tables` lists tables in creation order, referenced tables first; `reversed()` gives drop order. `drop_all()` drops in that reverse order and by default skips tables that are absent. Tables whose foreign keys reference each other break the sort, which is where constraint names start to matter. ## Where create_all() fits - **Test fixtures**: build a fresh schema at the start of a run and call `drop_all()` at the end, on a database that holds nothing else. - **Prototypes and local databases**: get from an empty database to a working schema with one call. - **Inside a larger transaction**: pass a `Connection` instead of an `Engine`, and the DDL joins the transaction you already opened, on backends whose DDL is transactional. The same call serves ORM code: declarative classes register their tables on a `MetaData` too, so `create_all()` behaves identically there. What it never replaces is a reviewed, versioned change to a database that already holds data.

  • What does invoices.create(engine) do in SQLAlchemy 2.1 if the invoices table already exists?
    `Table.create()` defaults to `checkfirst=CheckFirst.TYPES`, which checks only server-side types, so it emits `CREATE TABLE` without looking for the table and the database rejects it; SQLite raises `OperationalError` saying the table already exists. Pass `checkfirst=True` to make it skip an existing table the way `MetaData.create_all()` does.
  • You add a country column to accounts in the shared package and restart a service that calls create_all(). Why does a select of that column fail?
    `create_all()` sees that `accounts` exists and skips it, so no `ALTER TABLE ... ADD COLUMN` is ever emitted. The Python `Table` now names a column the database lacks, and the first statement that selects it fails with a database error about an unknown column. The column has to be added by a migration.
  • Can create_all() create only some of the tables in a MetaData?
    Yes: `metadata.create_all(engine, tables=[invoices])` considers only the listed `Table` objects and ignores the rest. It does not add the tables they reference, so on a backend that enforces foreign keys you list `accounts` too, or create it first.

  • create_all() adds new columns to tables that already exist
  • create_all() drops and recreates a table whose definition changed
  • Calling create_all() twice fails because the tables already exist
  • MetaData stores rows or caches query results
  • Declaring a Table opens a database connection at import time

Assumes SQLAlchemy 2.1; the CheckFirst enumeration and batched table-existence checks in create_all() are new in 2.1, and boolean checkfirst values still work.

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

How do you upsert products by SKU with SQLAlchemy's postgresql.insert() and on_conflict_do_update(), and what does stmt.excluded provide?

level: middleimportance: should knowfreq 45%

basics

~10 s

Import insert from sqlalchemy.dialects.postgresql, then call on_conflict_do_update(index_elements=[products.c.sku], set_={...}). Inside set_, stmt.excluded.<column> refers to the value the conflicting INSERT row proposed.

open as a page

In SQLAlchemy Core, how do you deactivate stale products with update() and get their ids back, and when can you trust CursorResult.rowcount?

level: middleimportance: should knowfreq 35%

basics

~10 s

Chain update(products).where(...).values(active=False).returning(products.c.id) and read the returned rows. CursorResult.rowcount counts rows matched; rely on it for a single-parameter-set UPDATE or DELETE, not with executemany or RETURNING.

open as a page

Why do teams set SQLAlchemy's MetaData(naming_convention=...), and what names do its ix, uq, ck, fk and pk templates produce?

level: middleimportance: should knowfreq 40%

basics

~20 s

MetaData(naming_convention=...) gives every unnamed constraint and index a predictable name when its Table is built, so later DDL can refer to it. Without it SQLAlchemy names only indexes; other unnamed constraints get whatever name the database assigns.

open as a page

In SQLAlchemy, how do Table(..., autoload_with=engine), MetaData.reflect() and inspect(engine) differ when you read an existing database's schema?

level: middleimportance: should knowfreq 35%

basics

~10 s

Table(name, metadata, autoload_with=engine) builds one Table from the live database, MetaData.reflect() does that for many tables at once, and inspect(engine) returns an Inspector whose methods return plain lists and dicts, not Table objects.

open as a page

How does SQLAlchemy Core's Select.join() infer the ON clause, and when must you pass an onclause or use join_from()?

level: middleimportance: should knowfreq 45%

basics

~20 s

Select.join(target) picks a left side from the statement's FROM list and builds ON from the one foreign key linking the two. With no key, several keys or a self-join, pass an onclause; use join_from() or select_from() to fix the left side.

open as a page

In SQLAlchemy Core, when do you call subquery(), cte(), scalar_subquery() or exists() on a select(), and what does each return?

level: middleimportance: should knowfreq 42%

basics

~20 s

subquery() and cte() turn a select() into FROM-clause objects with a .c collection, rendered inline or in a WITH clause. scalar_subquery() and exists() turn it into column expressions, a value or a true/false test, that auto-correlate to the enclosing select.

open as a page

A SQLAlchemy upsert built with on_conflict_do_update() leaves updated_at unchanged although its Column declares onupdate; why, and how do you fix it?

level: seniorimportance: should knowfreq 25%

basics

~10 s

Column(onupdate=...) is applied only to SET clauses SQLAlchemy builds for update() statements. The DO UPDATE SET of on_conflict_do_update() is your set_ dict verbatim, so list updated_at there, for example func.now().

open as a page

On PostgreSQL, SQLAlchemy's metadata.drop_all() raises CircularDependencyError for accounts and invoices tables whose foreign keys reference each other, though create_all() worked. Why, and how do you fix it?

level: seniorimportance: should knowfreq 20%

basics

~20 s

create_all() breaks the cycle with ALTER TABLE ... ADD FOREIGN KEY, which needs no name. drop_all() must break it with ALTER TABLE ... DROP CONSTRAINT, which does. Name the cycle's foreign keys, ideally through an fk naming convention.

open as a page

A SQLAlchemy Core report logs 'SELECT statement has a cartesian product between FROM element(s)' and its counts are inflated; what causes it and how do you fix it?

level: seniorimportance: should knowfreq 28%

basics

~20 s

A table reached the FROM clause through a column or filter but was never joined, so the database pairs every row with every row. SQLAlchemy's FROM linter flags it at compilation; join the table with an ON condition, or use the alias you meant.

open as a page

In SQLAlchemy Core, how does bindparam() let one update() apply per-row values to many rows, and why avoid column names?

level: middleimportance: nice to knowfreq 25%

basics

~20 s

Put bindparam() placeholders in where() and values(), then pass a list of dicts keyed by those names to execute(). Name them unlike any column, because parameter keys that match columns are added to the SET clause automatically.

open as a page

In SQLAlchemy, how do you decide between declaring a legacy billing database's tables as Table objects and reflecting them at startup, when another team owns that database?

level: principalimportance: nice to knowfreq 15%

basics

~20 s

Declare the tables your code writes or depends on closely: declared Table objects are reviewable and work offline. Reflect wide, read-only schemas another team changes, accepting startup catalog queries, lost Python-side detail and drift found only at query time.

open as a page