Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 37 ยท Core and Expressions

MetaData and Table Definitions

Describe a database schema with MetaData, Table and Column, create and drop tables, add foreign keys, inspect and reflect existing databases, and read the generated DDL.

Core thinks in tables. Before you can build SELECT or INSERT statements, SQLAlchemy needs a description of the tables involved: names, columns, types, keys. That description is a Python data structure, and it lives in a MetaData object. The same structure powers CREATE TABLE, query building, type conversion and, later, ORM mapping.

You define a schema once, in Python, and use it everywhere. This lesson shows how to declare tables, relate them with foreign keys, create them in a database, and go the other way: read the structure of a database that already exists.

MetaData, Table and Column

MetaData is a registry of tables. A Table takes a name, the metadata it belongs to, and a list of Column objects.

Python Declaring two related tables

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Useful things to notice:

  • customer.c is the column collection. Access a column as customer.c.name or customer.c["name"]. It has .keys(), iteration and so on.
  • ForeignKey("customer.id") refers to a column by table.column string. SQLAlchemy resolves it when needed, so the order you declare tables in does not matter.
  • metadata.sorted_tables returns tables in dependency order, which is how create_all() knows to create customer first.
  • CreateTable(...).compile(engine) shows the DDL for a given database without running it.

Creating and dropping tables

metadata.create_all(engine) issues CREATE TABLE for each table, skipping those that already exist (it checks first). drop_all() removes them in reverse dependency order. You can also target one table: customer.create(engine).

Python create_all and drop_all

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Column("author_id", ForeignKey("author.id")) leaves out the type on purpose: SQLAlchemy takes it from the referenced column.

create_all() is great for tests, prototypes and first runs. It does not alter existing tables, so a changed column never reaches a database that already has the table. Production schema changes belong to migrations with Alembic (chapter 7).

Naming conventions for constraints

When you leave constraints unnamed, the database invents names, and those differ between systems, which makes later migrations painful. A naming_convention on MetaData gives every constraint a predictable name.

Python
convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}
metadata = MetaData(naming_convention=convention)

Set this once at the top of a real project. Alembic's autogenerate relies on stable names to drop and alter constraints reliably.

Reflection: reading an existing database

You do not always own the schema. Reflection loads table definitions from a live database into Table objects, so you can query tables you never described in Python.

Python Reflecting tables and inspecting a database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Reflection is a quick way to start working with a legacy database. For code you maintain, an explicit definition is better: it documents the schema in your repository and does not need a live connection at import time.

Table, column and schema options

  • Table("t", metadata, ..., schema="reporting") places the table in a named schema (PostgreSQL, SQL Server).
  • Column(..., index=True) adds an index; Column(..., primary_key=True) marks the key. A composite primary key marks several columns.
  • Column(..., comment="...") stores a comment where the database supports it.
  • Table.c, Table.primary_key, Table.foreign_keys, Table.indexes expose the structure for inspection.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is MetaData in SQLAlchemy?

    MetaData holds the Table definitions. create_all(), drop_all() and reflection work on it.
  2. 2. What does metadata.create_all(engine) do when a table already exists?

    create_all() checks first and only creates missing tables. It never alters existing ones.
  3. 3. How do you read the column named title of a Table object called book?

    Columns live in the table's .c collection.
  4. 4. When is Table(..., autoload_with=engine) useful?

    autoload_with reflects the existing table definition from the database.

Summary

  • MetaData collects Table objects; each Table has Column objects reachable through .c.
  • ForeignKey("table.column") links tables and lets SQLAlchemy copy the type.
  • create_all() and drop_all() create and drop tables in dependency order but never alter existing tables.
  • Use a naming_convention for stable constraint names, and reflection (autoload_with, inspect) to read existing schemas.

Next: Column Types, Defaults and Constraints