In SQLAlchemy, how do Table(..., autoload_with=engine), MetaData.reflect() and inspect(engine) differ when you read an existing database's schema?
answer
- objects versus plain descriptions
- one table or many
- foreign keys pull in neighbours
- Inspector returns dicts
basics
~10 sTable(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.
solid answer
~40 s`Table("invoices", metadata, autoload_with=engine)` queries the database and fills the `Table` with `Column`, `ForeignKey`, index and constraint objects; because `resolve_fks` defaults to `True`, it also reflects `accounts` into the same `MetaData`. `metadata.reflect(engine)` reflects every table in the schema, or a subset with `only=[...]` (a missing name raises `InvalidRequestError`), plus views when `views=True`. Both produce real `Table` objects you can use in `select()` or `insert()`, and you can override a reflected column by passing your own `Column`. `inspect(engine)` returns an `Inspector`: `get_table_names()`, `get_columns("invoices")`, `get_foreign_keys()` and `get_indexes()` return lists of names and dicts for checks and tooling, and it can cache what it fetched, so after DDL make a new one or call `clear_cache()`.
code
python · 24 linesfrom sqlalchemy import MetaData, create_engine, inspect, text
engine = create_engine("sqlite://")
with engine.begin() as conn: # stands in for the legacy database
conn.execute(text("CREATE TABLE accounts (id INTEGER PRIMARY KEY, email VARCHAR(255) NOT NULL)"))
conn.execute(
text(
"CREATE TABLE invoices (id INTEGER PRIMARY KEY, "
"account_id INTEGER NOT NULL REFERENCES accounts (id), total NUMERIC(12, 2))"
)
)
conn.execute(text("CREATE TABLE audit_log (id INTEGER PRIMARY KEY, note TEXT)"))
insp = inspect(engine)
print(insp.get_table_names()) # ['accounts', 'audit_log', 'invoices']
print([col["name"] for col in insp.get_columns("invoices")]) # ['id', 'account_id', 'total']
legacy = MetaData()
legacy.reflect(engine, only=["invoices"])
print(sorted(legacy.tables)) # ['accounts', 'invoices']: the foreign key pulled accounts in
invoices = legacy.tables["invoices"]
print(invoices.c.account_id.references(legacy.tables["accounts"].c.id)) # True
print(repr(invoices.c.total.type)) # NUMERIC(precision=12, scale=2)go deeper
Remember the three entry points: autoload_with for one table, MetaData.reflect() for many, and inspect(engine) for plain descriptions.
Explain resolve_fks pulling in referenced tables, only= and its error for a missing name, column overrides, and the Inspector's dict results and cache.
Show what reflection loses: Python-side defaults, info, sequence links and exact server-default spelling, and why DDL compiled from reflected tables may not match the original.
Weigh reflection's startup queries and loss of detail against declared tables when a team depends on a legacy schema it does not own.
## Reflection in one sentence **Reflection** means asking a live database what its schema looks like and building SQLAlchemy objects from the answer, instead of declaring them by hand. It suits a legacy database whose tables your code reads but did not create. SQLAlchemy offers three entry points with different outputs. | entry point | what you get | typical use | |---|---|---| | `Table("invoices", metadata, autoload_with=engine)` | one `Table` (plus tables it references) in your `MetaData` | a few known tables | | `metadata.reflect(engine, only=[...])` | many `Table` objects in `metadata.tables` | a whole schema or a list | | `inspect(engine)` | an `Inspector` returning lists and dicts | checks, scripts, tooling | ## Table(..., autoload_with=engine) Passing `autoload_with` with an `Engine` or `Connection` makes the constructor query the database and fill in `Column`, `ForeignKey`, index and unique-constraint objects. - **Neighbours come along.** With `resolve_fks=True`, the default, a foreign key from `invoices` to `accounts` reflects `accounts` into the same `MetaData`. Pass `resolve_fks=False` to stop that. - **One object per name.** Asking for `Table("accounts", metadata)` again returns the already-reflected object and issues no new queries. - **Overrides.** A `Column` passed alongside `autoload_with` replaces the reflected one: `Column("total", Numeric(12, 2, asdecimal=True))` fixes a type, and `Column("view_id", Integer, primary_key=True)` adds a key the database does not declare, which is how a view becomes usable. - **Missing tables.** A name that does not exist raises `NoSuchTableError`. - **Schemas.** `schema="billing"` reads from another schema; the table is then keyed `billing.invoices` in `metadata.tables`. ## MetaData.reflect() `metadata.reflect(engine)` lists the tables in the default schema (or `schema=`) and reflects each one that is not already present. 1. `only=["invoices", "accounts"]` limits it to those names; a name that does not exist raises `InvalidRequestError` ("Could not reflect: requested table(s) not available ..."). `only` may also be a callable that receives each name and the `MetaData`. 2. `views=True` adds views; the default reflects tables only. 3. `resolve_fks` works as above, so `only=["invoices"]` still brings in `accounts`. 4. Calling it again picks up tables created since; a table dropped from the database stays in the `MetaData`. After it returns, `metadata.tables["invoices"]` and `metadata.sorted_tables` behave as if you had written the tables yourself. ## inspect(engine) and the Inspector `sqlalchemy.inspect(engine)` returns an `Inspector`, a lower-level, backend-neutral reader: - `get_table_names(schema=None)` returns real tables only; views come from `get_view_names()`; - `get_columns("invoices")` returns a list of dicts with keys such as `name`, `type`, `nullable` and `default` (the server default as text); - `get_foreign_keys()`, `get_indexes()`, `get_pk_constraint()` and `get_unique_constraints()` describe the rest; - `has_table("invoices")` answers a yes-or-no question cheaply. Nothing here becomes a `Table`, so nothing joins a `MetaData`. The Inspector may cache what it has fetched (single-table methods generally do, and SQLite's `get_columns()` does); after DDL runs, create a new one or call `clear_cache()`. ## Picking the entry point - **A handful of known tables you will query**: `autoload_with` per table keeps the catalog queries to those tables and their foreign-key neighbours. - **A whole schema, for a one-off script**: `metadata.reflect(engine)`, after which `reversed(metadata.sorted_tables)` gives a safe order for deleting every row. - **A question about the schema** (does this column exist, is it nullable, which indexes are there) with no need to build statements: the `Inspector`. - **Comparing a declared schema with the live one**: the `Inspector` again, because plain dicts are easy to diff against your own `Table` objects. ## Working with other schemas `MetaData(schema="billing")` sets a default schema for every table declared or reflected into it, and `Table(..., schema="billing")` sets it for one table. `metadata.reflect(engine, schema="billing")` reflects that schema's tables, and `inspect(engine).get_table_names(schema="billing")` lists them. Keys in `metadata.tables` then carry the prefix, as in `"billing.invoices"`, and compiled SQL names the table as `billing.invoices`. ## What reflection cannot give back Reflection reads only what the database stores. It cannot restore a Python-side `default=` function, the `Column.info` dictionary, or which `Sequence` a column was meant to use, and server defaults may come back in the database's own spelling, for instance with a type cast. Reflected types are the dialect's own (`NUMERIC(precision=12, scale=2)` on SQLite), so DDL compiled from reflected tables is not guaranteed to match the original. Reflected constraint names are whatever the database holds, and an unnamed foreign key in SQLite reflects with `name` set to `None`.
- A reflected invoices.total column comes back as a generic NUMERIC and you want your own type on it. How?Pass the column yourself next to `autoload_with`: `Table("invoices", metadata, Column("total", Numeric(12, 2, asdecimal=True)), autoload_with=engine)`. Columns you supply replace the reflected ones of the same name, and every other column is reflected as usual.
- Your script runs ALTER TABLE and then calls get_columns() on the same Inspector, which shows the old columns. Why?An `Inspector` can cache what it has fetched, as single-table methods generally do, so the second call can return the earlier answer. Create a new one with `inspect(engine)` or call `Inspector.clear_cache()` after the DDL.
- inspect(engine).get_columns() returns Column objects you can select from
- MetaData.reflect() also reflects views unless told not to
- Reflecting one table never adds other tables to the MetaData
- Reflection restores Python-side column defaults and the info dictionary
- Calling Table() with the same name re-queries the database every time