Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 37 ยท ORM Mapping

Declarative Models with Mapped and mapped_column

Map Python classes to tables with DeclarativeBase, Mapped annotations and mapped_column. Learn nullable types, constructors, __repr__, table args and dataclass mapping.

The ORM lets you work with Python classes instead of tables. You describe a class once, say which table it belongs to and what its attributes are, and SQLAlchemy takes care of the rest: creating the table, converting rows to objects and writing changes back. This style is called declarative mapping, because the class declaration is the table definition.

SQLAlchemy 2.0 uses ordinary Python type annotations to describe columns. A Mapped[int] attribute is an integer column; Mapped[str | None] is a nullable text column. Your editor and type checker understand these annotations, so you get autocompletion and error checking for free.

The base class and your first model

Every mapped class inherits from a common base. Subclassing DeclarativeBase gives you one, and it owns the MetaData that collects all your tables.

Python A first declarative model

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.

What the annotations mean:

  • __tablename__ names the table. It is the only required class attribute (besides a primary key).
  • Mapped[int] with mapped_column(primary_key=True) is the integer primary key. SQLite and PostgreSQL create an auto-incrementing key for a single integer primary key.
  • Mapped[str] alone is enough for a NOT NULL text column. Mapped[str | None] (or Optional[str]) makes it nullable.
  • mapped_column(default=False) sets a Python-side default, and you can also pass server_default.
  • You did not write __init__: declarative classes get a constructor that accepts the mapped attribute names as keyword arguments.

Python type to column type, by default: int to Integer, str to String, float to Float, bool to Boolean, datetime to DateTime, date to Date, Decimal to Numeric, bytes to LargeBinary, uuid.UUID to Uuid, enum.Enum subclasses to Enum. You can override any of them with an explicit type in mapped_column(String(50)).

Table and Core behind the class

A mapped class is a thin layer over a Core Table. You can always reach it:

  • User.__table__ is the Table object.
  • User.id, User.name are class-level attributes that behave like columns in expressions: User.name == "Ada" builds SQL, exactly like table.c.name == "Ada".
  • On an instance, the same attribute is the plain Python value: ada.name is "Ada".
Python One attribute, two roles

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.

This dual nature is the heart of the ORM. The class describes the table and builds queries. Instances carry the values for one row.

Customizing columns and table options

mapped_column() accepts everything a Core Column does: explicit types, ForeignKey, index, unique, server_default, comment, and so on. Table-level options go in __table_args__, a tuple whose last element may be a dictionary.

Python mapped_column options and __table_args__

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.

Notice that no column in the generated DDL is nullable except body. Declarative mapping makes a column required unless its annotation says otherwise, which is the safe default.

Reusable annotated types

If many columns share the same definition (an integer primary key, a 100-character name), define it once with typing.Annotated and reuse it. Better still, change what a plain Python type means for the whole project with type_annotation_map on the base class.

Python Annotated and type_annotation_map

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.

Dataclass-style models

If you prefer classes that behave like dataclasses (positional constructor, __repr__, __eq__ generated for you), inherit from MappedAsDataclass as well. Fields without defaults must come before those with defaults, as in any dataclass.

Python MappedAsDataclass

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.

init=False keeps the generated primary key out of the constructor. Dataclass mapping is optional. Plain declarative classes remain the most common style in existing code bases.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Mapped[str | None] declare?

    Optional annotations make the column nullable. A bare Mapped[str] is NOT NULL.
  2. 2. What is User.name at class level and ada.name on an instance?

    Mapped attributes are instrumented: they behave as column expressions on the class and as values on instances.
  3. 3. Where do table-level constraints and indexes go in a declarative class?

    table_args is a tuple of constraints and indexes, optionally ending with a dictionary of table options.
  4. 4. How do you give every bare str annotation in the project the type String(100)?

    type_annotation_map = {str: String(100)} changes the default mapping for that Python type.

Summary

  • Subclass DeclarativeBase once for the project, then subclass that base for each model with __tablename__.
  • Mapped[...] annotations plus mapped_column() define columns; | None means nullable.
  • Class attributes build SQL expressions; instance attributes hold values; Model.__table__ is the Core table.
  • Use __table_args__, Annotated types, type_annotation_map and MappedAsDataclass to reduce repetition.

Next: Column Options, Computed and Deferred Columns