Table Configuration with Declarative

As introduced at Declarative Mapping, the Declarative style includes the ability to generate a mapped Table object at the same time, or to accommodate a Table or other FromClause object directly.

The following examples assume a declarative base class as:

  1. from sqlalchemy.orm import DeclarativeBase
  4. class Base(DeclarativeBase):
  5.     pass

All of the examples that follow illustrate a class inheriting from the above Base. The decorator style introduced at Declarative Mapping using a Decorator (no declarative base) is fully supported with all the following examples as well, as are legacy forms of Declarative Base including base classes generated by declarative_base().

Declarative Table with mapped_column()

When using Declarative, the body of the class to be mapped in most cases includes an attribute __tablename__ that indicates the string name of a Table that should be generated along with the mapping. The mapped_column() construct, which features additional ORM-specific configuration capabilities not present in the plain Column class, is then used within the class body to indicate columns in the table. The example below illustrates the most basic use of this construct within a Declarative mapping:

  1. from sqlalchemy import Integer, String
  2. from sqlalchemy.orm import DeclarativeBase
  3. from sqlalchemy.orm import mapped_column
  6. class Base(DeclarativeBase):
  7.     pass
  10. class User(Base):
  11.     __tablename__ = "user"
  13.     id = mapped_column(Integer, primary_key=True)
  14.     name = mapped_column(String(50), nullable=False)
  15.     fullname = mapped_column(String)
  16.     nickname = mapped_column(String(30))

Above, mapped_column() constructs are placed inline within the class definition as class level attributes. At the point at which the class is declared, the Declarative mapping process will generate a new Table object against the MetaData collection associated with the Declarative Base; each instance of mapped_column() will then be used to generate a Column object during this process, which will become part of the Table.columns collection of this Table object.

In the above example, Declarative will build a Table construct that is equivalent to the following:

  1. # equivalent Table object produced
  2. user_table = Table(
  3.     "user",
  4.     Base.metadata,
  5.     Column("id", Integer, primary_key=True),
  6.     Column("name", String(50)),
  7.     Column("fullname", String()),
  8.     Column("nickname", String(30)),
  9. )

When the User class above is mapped, this Table object can be accessed directly via the __table__ attribute; this is described further at Accessing Table and Metadata.

mapped_column() supersedes the use of Column()

Users of 1.x SQLAlchemy will note the use of the mapped_column() construct, which is new as of the SQLAlchemy 2.0 series. This ORM-specific construct is intended first and foremost to be a drop-in replacement for the use of Column within Declarative mappings only, adding new ORM-specific convenience features such as the ability to establish mapped_column.deferred within the construct, and most importantly to indicate to typing tools such as Mypy and Pylance an accurate representation of how the attribute will behave at runtime at both the class level as well as the instance level. As will be seen in the following sections, it’s also at the forefront of a new annotation-driven configuration style introduced in SQLAlchemy 2.0.

Users of legacy code should be aware that the Column form will always work in Declarative in the same way it always has. The different forms of attribute mapping may also be mixed within a single mapping on an attribute by attribute basis, so migration to the new form can be at any pace. See the section ORM Declarative Models for a step by step guide to migrating a Declarative model to the new form.

The mapped_column() construct accepts all arguments that are accepted by the Column construct, as well as additional ORM-specific arguments. The mapped_column.__name field, indicating the name of the database column, is typically omitted, as the Declarative process will make use of the attribute name given to the construct and assign this as the name of the column (in the above example, this refers to the names id, name, fullname, nickname). Assigning an alternate mapped_column.__name is valid as well, where the resulting Column will use the given name in SQL and DDL statements, while the User mapped class will continue to allow access to the attribute using the attribute name given, independent of the name given to the column itself (more on this at Naming Declarative Mapped Columns Explicitly).

Tip

The mapped_column() construct is only valid within a Declarative class mapping. When constructing a Table object using Core as well as when using imperative table configuration, the Column construct is still required in order to indicate the presence of a database column.

See also

Mapping Table Columns - contains additional notes on affecting how Mapper interprets incoming Column objects.

Using Annotated Declarative Table (Type Annotated Forms for mapped_column())

The mapped_column() construct is capable of deriving its column-configuration information from PEP 484 type annotations associated with the attribute as declared in the Declarative mapped class. These type annotations, if used, must be present within a special SQLAlchemy type called Mapped, which is a generic type that then indicates a specific Python type within it.

Below illustrates the mapping from the previous section, adding the use of Mapped:

  1. from typing import Optional
  3. from sqlalchemy import String
  4. from sqlalchemy.orm import DeclarativeBase
  5. from sqlalchemy.orm import Mapped
  6. from sqlalchemy.orm import mapped_column
  9. class Base(DeclarativeBase):
  10.     pass
  13. class User(Base):
  14.     __tablename__ = "user"
  16.     id: Mapped[int] = mapped_column(primary_key=True)
  17.     name: Mapped[str] = mapped_column(String(50))
  18.     fullname: Mapped[Optional[str]]
  19.     nickname: Mapped[Optional[str]] = mapped_column(String(30))

Above, when Declarative processes each class attribute, each mapped_column() will derive additional arguments from the corresponding Mapped type annotation on the left side, if present. Additionally, Declarative will generate an empty mapped_column() directive implicitly, whenever a Mapped type annotation is encountered that does not have a value assigned to the attribute (this form is inspired by the similar style used in Python dataclasses); this mapped_column() construct proceeds to derive its configuration from the Mapped annotation present.

mapped_column() derives the datatype and nullability from the Mapped annotation

The two qualities that mapped_column() derives from the Mapped annotation are:

  • datatype - the Python type given inside Mapped, as contained within the typing.Optional construct if present, is associated with a TypeEngine subclass such as Integer, String, DateTime, or Uuid, to name a few common types.

    The datatype is determined based on a dictionary of Python type to SQLAlchemy datatype. This dictionary is completely customizable, as detailed in the next section Customizing the Type Map. The default type map is implemented as in the code example below:

    1. from typing import Any
    2. from typing import Dict
    3. from typing import Type
    5. import datetime
    6. import decimal
    7. import uuid
    9. from sqlalchemy import types
    11. # default type mapping, deriving the type for mapped_column()
    12. # from a Mapped[] annotation
    13. type_map: Dict[Type[Any], TypeEngine[Any]] = {
    14.     bool: types.Boolean(),
    15.     bytes: types.LargeBinary(),
    16.     datetime.date: types.Date(),
    17.     datetime.datetime: types.DateTime(),
    18.     datetime.time: types.Time(),
    19.     datetime.timedelta: types.Interval(),
    20.     decimal.Decimal: types.Numeric(),
    21.     float: types.Float(),
    22.     int: types.Integer(),
    23.     str: types.String(),
    24.     uuid.UUID: types.Uuid(),
    25. }

    If the mapped_column() construct indicates an explicit type as passed to the mapped_column.__type argument, then the given Python type is disregarded.

  • nullability - The mapped_column() construct will indicate its Column as NULL or NOT NULL first and foremost by the presence of the mapped_column.nullable parameter, passed either as True or False. Additionally , if the mapped_column.primary_key parameter is present and set to True, that will also imply that the column should be NOT NULL.

    In the absence of both of these parameters, the presence of typing.Optional[] within the Mapped type annotation will be used to determine nullability, where typing.Optional[] means NULL, and the absense of typing.Optional[] means NOT NULL. If there is no Mapped[] annotation present at all, and there is no mapped_column.nullable or mapped_column.primary_key parameter, then SQLAlchemy’s usual default for Column of NULL is used.

    In the example below, the id and data columns will be NOT NULL, and the additional_info column will be NULL:

    ``` from typing import Optional

    from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column

  1. class Base(DeclarativeBase):
  2.     pass
  5. class SomeClass(Base):
  6.     __tablename__ = "some_table"
  8.     # primary_key=True, therefore will be NOT NULL
  9.     id: Mapped[int] = mapped_column(primary_key=True)
  11.     # not Optional[], therefore will be NOT NULL
  12.     data: Mapped[str]
  14.     # Optional[], therefore will be NULL
  15.     additional_info: Mapped[Optional[str]]
  16. ```
  18. It is also perfectly valid to have a [mapped\_column()]($3736cc9f0e9d089e.md#sqlalchemy.orm.mapped_column "sqlalchemy.orm.mapped_column") whose nullability is **different** from what would be implied by the annotation. For example, an ORM mapped attribute may be annotated as allowing `None` within Python code that works with the object as it is first being created and populated, however the value will ultimately be written to a database column that is `NOT NULL`. The [mapped\_column.nullable]($3736cc9f0e9d089e.md#sqlalchemy.orm.mapped_column.params.nullable "sqlalchemy.orm.mapped_column") parameter, when present, will always take precedence:
  20. ```
  21. class SomeClass(Base):
  22.     # ...
  24.     # will be String() NOT NULL, but can be None in Python
  25.     data: Mapped[Optional[str]] = mapped_column(nullable=False)
  26. ```
  28. Similarly, a non-None attribute that’s written to a database column that for whatever reason needs to be NULL at the schema level, [mapped\_column.nullable]($3736cc9f0e9d089e.md#sqlalchemy.orm.mapped_column.params.nullable "sqlalchemy.orm.mapped_column") may be set to `True`:
  30. ```
  31. class SomeClass(Base):
  32.     # ...
  34.     # will be String() NULL, but type checker will not expect
  35.     # the attribute to be None
  36.     data: Mapped[str] = mapped_column(nullable=True)
  37. ```

Customizing the Type Map

The mapping of Python types to SQLAlchemy TypeEngine types described in the previous section defaults to a hardcoded dictionary present in the sqlalchemy.sql.sqltypes module. However, the registry object that coordinates the Declarative mapping process will first consult a local, user defined dictionary of types which may be passed as the registry.type_annotation_map parameter when constructing the registry, which may be associated with the DeclarativeBase superclass when first used.

As an example, if we wish to make use of the BIGINT datatype for int, the TIMESTAMP datatype with timezone=True for datetime.datetime, and then only on Microsoft SQL Server we’d like to use NVARCHAR datatype when Python str is used, the registry and Declarative base could be configured as:

  1. import datetime
  3. from sqlalchemy import BIGINT, Integer, NVARCHAR, String, TIMESTAMP
  4. from sqlalchemy.orm import DeclarativeBase
  5. from sqlalchemy.orm import Mapped, mapped_column, registry
  8. class Base(DeclarativeBase):
  9.     type_annotation_map = {
  10.         int: BIGINT,
  11.         datetime.datetime: TIMESTAMP(timezone=True),
  12.         str: String().with_variant(NVARCHAR, "mssql"),
  13.     }
  16. class SomeClass(Base):
  17.     __tablename__ = "some_table"
  19.     id: Mapped[int] = mapped_column(primary_key=True)
  20.     date: Mapped[datetime.datetime]
  21.     status: Mapped[str]

Below illustrates the CREATE TABLE statement generated for the above mapping, first on the Microsoft SQL Server backend, illustrating the NVARCHAR datatype:

  1. >>> from sqlalchemy.schema import CreateTable
  2. >>> from sqlalchemy.dialects import mssql, postgresql
  3. >>> print(CreateTable(SomeClass.__table__).compile(dialect=mssql.dialect()))
  4. CREATE TABLE some_table (
  5.   id BIGINT NOT NULL IDENTITY,
  6.   date TIMESTAMP NOT NULL,
  7.   status NVARCHAR(max) NOT NULL,
  8.   PRIMARY KEY (id)
  9. )

Then on the PostgreSQL backend, illustrating TIMESTAMP WITH TIME ZONE:

  1. >>> print(CreateTable(SomeClass.__table__).compile(dialect=postgresql.dialect()))
  2. CREATE TABLE some_table (
  3.   id BIGSERIAL NOT NULL,
  4.   date TIMESTAMP WITH TIME ZONE NOT NULL,
  5.   status VARCHAR NOT NULL,
  6.   PRIMARY KEY (id)
  7. )

By making use of methods such as TypeEngine.with_variant(), we’re able to build up a type map that’s customized to what we need for different backends, while still being able to use succinct annotation-only mapped_column() configurations. There are two more levels of Python-type configurability available beyond this, described in the next two sections.

Using Python Enum types in the type map

New in version 2.0.0b4.

User-defined Python types which derive from the Python built-in enum.Enum class are automatically linked to the SQLAlchemy Enum datatype when used in an ORM declarative mapping:

  1. import enum
  3. from sqlalchemy.orm import DeclarativeBase
  4. from sqlalchemy.orm import Mapped
  5. from sqlalchemy.orm import mapped_column
  8. class Base(DeclarativeBase):
  9.     pass
  12. class Status(enum.Enum):
  13.     PENDING = "pending"
  14.     RECEIVED = "received"
  15.     COMPLETED = "completed"
  18. class SomeClass(Base):
  19.     __tablename__ = "some_table"
  21.     id: Mapped[int] = mapped_column(primary_key=True)
  22.     status: Mapped[Status]

In the above example, the mapped attribute SomeClass.status will be linked to a Column with the datatype of Enum(Status). We can see this for example in the CREATE TABLE output for the PostgreSQL database:

  1. CREATE TYPE status AS ENUM ('PENDING', 'RECEIVED', 'COMPLETED')
  3. CREATE TABLE some_table (
  4.   id SERIAL NOT NULL,
  5.   status status NOT NULL,
  6.   PRIMARY KEY (id)
  7. )

The entry used in registry.type_annotation_map links the base enum.Enum Python type to the SQLAlchemy Enum SQL type, using a special form which indicates to the Enum datatype that it should automatically configure itself against an arbitrary enumerated type. This configuration, which is implicit by default, would be indicated explicitly as:

  1. import enum
  2. import sqlalchemy
  5. class Base(DeclarativeBase):
  6.     type_annotation_map = {enum.Enum: sqlalchemy.Enum(enum.Enum)}

The resolution logic within Declarative is able to resolve subclasses of enum.Enum, in the above example the custom Status enumeration, to match the enum.Enum entry in the registry.type_annotation_map dictionary. The Enum SQL type then knows how to produce a configured version of itself with the appropriate settings, including default string length.

In order to modify the configuration of the Enum datatype used in this mapping, use the above form, indicating additional arguments. For example, to use “non native enumerations” on all backends, the Enum.native_enum parameter may be set to False for all types:

  1. import enum
  2. import sqlalchemy
  5. class Base(DeclarativeBase):
  6.     type_annotation_map = {enum.Enum: sqlalchemy.Enum(enum.Enum, native_enum=False)}

To use a specific configuration for a specific enum.Enum subtype, such as setting the string length to 50 when using the example Status datatype:

  1. import enum
  2. import sqlalchemy
  5. class Status(enum.Enum):
  6.     PENDING = "pending"
  7.     RECEIVED = "received"
  8.     COMPLETED = "completed"
  11. class Base(DeclarativeBase):
  12.     type_annotation_map = {
  13.         Status: sqlalchemy.Enum(Status, length=50, native_enum=False)
  14.     }

Mapping Multiple Type Configurations to Python Types

As individual Python types may be associated with TypeEngine configurations of any variety by using the registry.type_annotation_map parameter, an additional capability is the ability to associate a single Python type with different variants of a SQL type based on additional type qualifiers. One typical example of this is mapping the Python str datatype to VARCHAR SQL types of different lengths. Another is mapping different varieties of decimal.Decimal to differently sized NUMERIC columns.

Python’s typing system provides a great way to add additional metadata to a Python type which is by using the PEP 593 Annotated generic type, which allows additional information to be bundled along with a Python type. The mapped_column() construct will correctly interpret an Annotated object by identity when resolving it in the registry.type_annotation_map, as in the example below where we declare two variants of String and Numeric:

  1. from decimal import Decimal
  3. from typing_extensions import Annotated
  5. from sqlalchemy import Numeric
  6. from sqlalchemy import String
  7. from sqlalchemy.orm import DeclarativeBase
  8. from sqlalchemy.orm import Mapped
  9. from sqlalchemy.orm import mapped_column
  10. from sqlalchemy.orm import registry
  12. str_30 = Annotated[str, 30]
  13. str_50 = Annotated[str, 50]
  14. num_12_4 = Annotated[Decimal, 12]
  15. num_6_2 = Annotated[Decimal, 6]
  18. class Base(DeclarativeBase):
  19.     registry = registry(
  20.         type_annotation_map={
  21.             str_30: String(30),
  22.             str_50: String(50),
  23.             num_12_4: Numeric(12, 4),
  24.             num_6_2: Numeric(6, 2),
  25.         }
  26.     )

The Python type passed to the Annotated container, in the above example the str and Decimal types, is important only for the benefit of typing tools; as far as the mapped_column() construct is concerned, it will only need perform a lookup of each type object in the registry.type_annotation_map dictionary without actually looking inside of the Annotated object, at least in this particular context. Similarly, the arguments passed to Annotated beyond the underlying Python type itself are also not important, it’s only that at least one argument must be present for the Annotated construct to be valid. We can then use these augmented types directly in our mapping where they will be matched to the more specific type constructions, as in the following example:

  1. class SomeClass(Base):
  2.     __tablename__ = "some_table"
  4.     short_name: Mapped[str_30] = mapped_column(primary_key=True)
  5.     long_name: Mapped[str_50]
  6.     num_value: Mapped[num_12_4]
  7.     short_num_value: Mapped[num_6_2]

a CREATE TABLE for the above mapping will illustrate the different variants of VARCHAR and NUMERIC we’ve configured, and looks like:

  1. >>> from sqlalchemy.schema import CreateTable
  2. >>> print(CreateTable(SomeClass.__table__))
  3. CREATE TABLE some_table (
  4.   short_name VARCHAR(30) NOT NULL,
  5.   long_name VARCHAR(50) NOT NULL,
  6.   num_value NUMERIC(12, 4) NOT NULL,
  7.   short_num_value NUMERIC(6, 2) NOT NULL,
  8.   PRIMARY KEY (short_name)
  9. )

While variety in linking Annotated types to different SQL types grants us a wide degree of flexibility, the next section illustrates a second way in which Annotated may be used with Declarative that is even more open ended.

Mapping Whole Column Declarations to Python Types

The previous section illustrated using PEP 593 Annotated type instances as keys within the registry.type_annotation_map dictionary. In this form, the mapped_column() construct does not actually look inside the Annotated object itself, it’s instead used only as a dictionary key. However, Declarative also has the ability to extract an entire pre-established mapped_column() construct from an Annotated object directly. Using this form, we can define not only different varieties of SQL datatypes linked to Python types without using the registry.type_annotation_map dictionary, we can also set up any number of arguments such as nullability, column defaults, and constraints in a reusable fashion.

A set of ORM models will usually have some kind of primary key style that is common to all mapped classes. There also may be common column configurations such as timestamps with defaults and other fields of pre-established sizes and configurations. We can compose these configurations into mapped_column() instances that we then bundle directly into instances of Annotated, which are then re-used in any number of class declarations. Declarative will unpack an Annotated object when provided in this manner, skipping over any other directives that don’t apply to SQLAlchemy and searching only for SQLAlchemy ORM constructs.

The example below illustrates a variety of pre-configured field types used in this way, where we define intpk that represents an Integer primary key column, timestamp that represents a DateTime type which will use CURRENT_TIMESTAMP as a DDL level column default, and required_name which is a String of length 30 that’s NOT NULL:

  1. import datetime
  3. from typing_extensions import Annotated
  5. from sqlalchemy import func
  6. from sqlalchemy import String
  7. from sqlalchemy.orm import mapped_column
  10. intpk = Annotated[int, mapped_column(primary_key=True)]
  11. timestamp = Annotated[
  12.     datetime.datetime,
  13.     mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),
  14. ]
  15. required_name = Annotated[str, mapped_column(String(30), nullable=False)]

The above Annotated objects can then be used directly within Mapped, where the pre-configured mapped_column() constructs will be extracted and copied to a new instance that will be specific to each attribute:

  1. class Base(DeclarativeBase):
  2.     pass
  5. class SomeClass(Base):
  6.     __tablename__ = "some_table"
  8.     id: Mapped[intpk]
  9.     name: Mapped[required_name]
  10.     created_at: Mapped[timestamp]

CREATE TABLE for our above mapping looks like:

  1. >>> from sqlalchemy.schema import CreateTable
  2. >>> print(CreateTable(SomeClass.__table__))
  3. CREATE TABLE some_table (
  4.   id INTEGER NOT NULL,
  5.   name VARCHAR(30) NOT NULL,
  6.   created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
  7.   PRIMARY KEY (id)
  8. )

When using Annotated types in this way, the configuration of the type may also be affected on a per-attribute basis. For the types in the above example that feature explcit use of mapped_column.nullable, we can apply the Optional[] generic modifier to any of our types so that the field is optional or not at the Python level, which will be independent of the NULL / NOT NULL setting that takes place in the database:

  1. from typing_extensions import Annotated
  3. import datetime
  4. from typing import Optional
  6. from sqlalchemy.orm import DeclarativeBase
  8. timestamp = Annotated[
  9.     datetime.datetime,
  10.     mapped_column(nullable=False),
  11. ]
  14. class Base(DeclarativeBase):
  15.     pass
  18. class SomeClass(Base):
  20.     # ...
  22.     # pep-484 type will be Optional, but column will be
  23.     # NOT NULL
  24.     created_at: Mapped[Optional[timestamp]]

The mapped_column() construct is also reconciled with an explicitly passed mapped_column() construct, whose arguments will take precedence over those of the Annotated construct. Below we add a ForeignKey constraint to our integer primary key and also use an alternate server default for the created_at column:

  1. import datetime
  3. from typing_extensions import Annotated
  5. from sqlalchemy import ForeignKey
  6. from sqlalchemy import func
  7. from sqlalchemy.orm import DeclarativeBase
  8. from sqlalchemy.orm import Mapped
  9. from sqlalchemy.orm import mapped_column
  10. from sqlalchemy.schema import CreateTable
  12. intpk = Annotated[int, mapped_column(primary_key=True)]
  13. timestamp = Annotated[
  14.     datetime.datetime,
  15.     mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),
  16. ]
  19. class Base(DeclarativeBase):
  20.     pass
  23. class Parent(Base):
  24.     __tablename__ = "parent"
  26.     id: Mapped[intpk]
  29. class SomeClass(Base):
  30.     __tablename__ = "some_table"
  32.     # add ForeignKey to mapped_column(Integer, primary_key=True)
  33.     id: Mapped[intpk] = mapped_column(ForeignKey("parent.id"))
  35.     # change server default from CURRENT_TIMESTAMP to UTC_TIMESTAMP
  36.     created_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())

The CREATE TABLE statement illustrates these per-attribute settings, adding a FOREIGN KEY constraint as well as substituting UTC_TIMESTAMP for CURRENT_TIMESTAMP:

  1. >>> from sqlalchemy.schema import CreateTable
  2. >>> print(CreateTable(SomeClass.__table__))
  3. CREATE TABLE some_table (
  4.   id INTEGER NOT NULL,
  5.   created_at DATETIME DEFAULT UTC_TIMESTAMP() NOT NULL,
  6.   PRIMARY KEY (id),
  7.   FOREIGN KEY(id) REFERENCES parent (id)
  8. )

Note

The feature of mapped_column() just described, where a fully constructed set of column arguments may be indicated using PEP 593 Annotated objects that contain a “template” mapped_column() object to be copied into the attribute, is currently not implemented for other ORM constructs such as relationship() and composite(). While this functionality is in theory possible, for the moment attempting to use Annotated to indicate further arguments for relationship() and similar will raise a NotImplementedError exception at runtime, but may be implemented in future releases.

Dataclass features in mapped_column()

The mapped_column() construct integrates with SQLAlchemy’s “native dataclasses” feature, discussed at Declarative Dataclass Mapping. See that section for current background on additional directives supported by mapped_column().

Accessing Table and Metadata

A declaratively mapped class will always include an attribute called __table__; when the above configuration using __tablename__ is complete, the declarative process makes the Table available via the __table__ attribute:

  1. # access the Table
  2. user_table = User.__table__

The above table is ultimately the same one that corresponds to the Mapper.local_table attribute, which we can see through the runtime inspection system:

  1. from sqlalchemy import inspect
  3. user_table = inspect(User).local_table

The MetaData collection associated with both the declarative registry as well as the base class is frequently necessary in order to run DDL operations such as CREATE, as well as in use with migration tools such as Alembic. This object is available via the .metadata attribute of registry as well as the declarative base class. Below, for a small script we may wish to emit a CREATE for all tables against a SQLite database:

  1. engine = create_engine("sqlite://")
  3. Base.metadata.create_all(engine)

Declarative Table Configuration

When using Declarative Table configuration with the __tablename__ declarative class attribute, additional arguments to be supplied to the Table constructor should be provided using the __table_args__ declarative class attribute.

This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor. The attribute can be specified in one of two forms. One is as a dictionary:

  1. class MyClass(Base):
  2.     __tablename__ = "sometable"
  3.     __table_args__ = {"mysql_engine": "InnoDB"}

The other, a tuple, where each argument is positional (usually constraints):

  1. class MyClass(Base):
  2.     __tablename__ = "sometable"
  3.     __table_args__ = (
  4.         ForeignKeyConstraint(["id"], ["remote_table.id"]),
  5.         UniqueConstraint("foo"),
  6.     )

Keyword arguments can be specified with the above form by specifying the last argument as a dictionary:

  1. class MyClass(Base):
  2.     __tablename__ = "sometable"
  3.     __table_args__ = (
  4.         ForeignKeyConstraint(["id"], ["remote_table.id"]),
  5.         UniqueConstraint("foo"),
  6.         {"autoload": True},
  7.     )

A class may also specify the __table_args__ declarative attribute, as well as the __tablename__ attribute, in a dynamic style using the declared_attr() method decorator. See Composing Mapped Hierarchies with Mixins for background.

Explicit Schema Name with Declarative Table

The schema name for a Table as documented at Specifying the Schema Name is applied to an individual Table using the Table.schema argument. When using Declarative tables, this option is passed like any other to the __table_args__ dictionary:

  1. from sqlalchemy.orm import DeclarativeBase
  4. class Base(DeclarativeBase):
  5.     pass
  8. class MyClass(Base):
  9.     __tablename__ = "sometable"
  10.     __table_args__ = {"schema": "some_schema"}

The schema name can also be applied to all Table objects globally by using the MetaData.schema parameter documented at Specifying a Default Schema Name with MetaData. The MetaData object may be constructed separately and associated with a DeclarativeBase subclass by assigning to the metadata attribute directly:

  1. from sqlalchemy import MetaData
  2. from sqlalchemy.orm import DeclarativeBase
  4. metadata_obj = MetaData(schema="some_schema")
  7. class Base(DeclarativeBase):
  8.     metadata = metadata_obj
  11. class MyClass(Base):
  12.     # will use "some_schema" by default
  13.     __tablename__ = "sometable"

See also

Specifying the Schema Name - in the Describing Databases with MetaData documentation.

Setting Load and Persistence Options for Declarative Mapped Columns

The mapped_column() construct accepts additional ORM-specific arguments that affect how the generated Column is mapped, affecting its load and persistence-time behavior. Options that are commonly used include:

  • deferred column loading - The mapped_column.deferred boolean establishes the Column using deferred column loading by default. In the example below, the User.bio column will not be loaded by default, but only when accessed:

    1. class User(Base):
    2.     __tablename__ = "user"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     name: Mapped[str]
    6.     bio: Mapped[str] = mapped_column(Text, deferred=True)

    See also

    Limiting which Columns Load with Column Deferral - full description of deferred column loading

  • active history - The mapped_column.active_history ensures that upon change of value for the attribute, the previous value will have been loaded and made part of the AttributeState.history collection when inspecting the history of the attribute. This may incur additional SQL statements:

    1. class User(Base):
    2.     __tablename__ = "user"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     important_identifier: Mapped[str] = mapped_column(active_history=True)

See the docstring for mapped_column() for a list of supported parameters.

See also

Applying Load, Persistence and Mapping Options for Mapped Table Columns - describes using column_property() and deferred() for use with Imperative Table configuration

Naming Declarative Mapped Columns Explicitly

All of the examples thus far feature the mapped_column() construct linked to an ORM mapped attribute, where the Python attribute name given to the mapped_column() is also that of the column as we see in CREATE TABLE statements as well as queries. The name for a column as expressed in SQL may be indicated by passing the string positional argument mapped_column.__name as the first positional argument. In the example below, the User class is mapped with alternate names given to the columns themselves:

  1. class User(Base):
  2.     __tablename__ = "user"
  4.     id: Mapped[int] = mapped_column("user_id", primary_key=True)
  5.     name: Mapped[str] = mapped_column("user_name")

Where above User.id resolves to a column named user_id and User.name resolves to a column named user_name. We may write a select() statement using our Python attribute names and will see the SQL names generated:

  1. >>> from sqlalchemy import select
  2. >>> print(select(User.id, User.name).where(User.name == "x"))
  3. SELECT "user".user_id, "user".user_name
  4. FROM "user"
  5. WHERE "user".user_name = :user_name_1

See also

Alternate Attribute Names for Mapping Table Columns - applies to Imperative Table

Appending additional columns to an existing Declarative mapped class

A declarative table configuration allows the addition of new Column objects to an existing mapping after the Table metadata has already been generated.

For a declarative class that is declared using a declarative base class, the underlying metaclass DeclarativeMeta includes a __setattr__() method that will intercept additional mapped_column() or Core Column objects and add them to both the Table using Table.append_column() as well as to the existing Mapper using Mapper.add_property():

  1. MyClass.some_new_column = mapped_column(String)

Using core Column:

  1. MyClass.some_new_column = Column(String)

All arguments are supported including an alternate name, such as MyClass.some_new_column = mapped_column("some_name", String). However, the SQL type must be passed to the mapped_column() or Column object explicitly, as in the above examples where the String type is passed. There’s no capability for the Mapped annotation type to take part in the operation.

Additional Column objects may also be added to a mapping in the specific circumstance of using single table inheritance, where additional columns are present on mapped subclasses that have no Table of their own. This is illustrated in the section Single Table Inheritance.

Note

Assignment of mapped properties to an already mapped class will only function correctly if the “declarative base” class is used, meaning the user-defined subclass of DeclarativeBase or the dynamically generated class returned by declarative_base() or registry.generate_base(). This “base” class includes a Python metaclass which implements a special __setattr__() method that intercepts these operations.

Runtime assignment of class-mapped attributes to a mapped class will not work if the class is mapped using decorators like registry.mapped() or imperative functions like registry.map_imperatively().

Declarative with Imperative Table (a.k.a. Hybrid Declarative)

Declarative mappings may also be provided with a pre-existing Table object, or otherwise a Table or other arbitrary FromClause construct (such as a Join or Subquery) that is constructed separately.

This is referred to as a “hybrid declarative” mapping, as the class is mapped using the declarative style for everything involving the mapper configuration, however the mapped Table object is produced separately and passed to the declarative process directly:

  1. from sqlalchemy import Column, ForeignKey, Integer, String
  2. from sqlalchemy.orm import DeclarativeBase
  5. class Base(DeclarativeBase):
  6.     pass
  9. # construct a Table directly.  The Base.metadata collection is
  10. # usually a good choice for MetaData but any MetaData
  11. # collection may be used.
  13. user_table = Table(
  14.     "user",
  15.     Base.metadata,
  16.     Column("id", Integer, primary_key=True),
  17.     Column("name", String),
  18.     Column("fullname", String),
  19.     Column("nickname", String),
  20. )
  23. # construct the User class using this table.
  24. class User(Base):
  25.     __table__ = user_table

Above, a Table object is constructed using the approach described at Describing Databases with MetaData. It can then be applied directly to a class that is declaratively mapped. The __tablename__ and __table_args__ declarative class attributes are not used in this form. The above configuration is often more readable as an inline definition:

  1. class User(Base):
  2.     __table__ = Table(
  3.         "user",
  4.         Base.metadata,
  5.         Column("id", Integer, primary_key=True),
  6.         Column("name", String),
  7.         Column("fullname", String),
  8.         Column("nickname", String),
  9.     )

A natural effect of the above style is that the __table__ attribute is itself defined within the class definition block. As such it may be immediately referred towards within subsequent attributes, such as the example below which illustrates referring to the type column in a polymorphic mapper configuration:

  1. class Person(Base):
  2.     __table__ = Table(
  3.         "person",
  4.         Base.metadata,
  5.         Column("id", Integer, primary_key=True),
  6.         Column("name", String(50)),
  7.         Column("type", String(50)),
  8.     )
  10.     __mapper_args__ = {
  11.         "polymorphic_on": __table__.c.type,
  12.         "polymorhpic_identity": "person",
  13.     }

The “imperative table” form is also used when a non-Table construct, such as a Join or Subquery object, is to be mapped. An example below:

  1. from sqlalchemy import func, select
  3. subq = (
  4.     select(
  5.         func.count(orders.c.id).label("order_count"),
  6.         func.max(orders.c.price).label("highest_order"),
  7.         orders.c.customer_id,
  8.     )
  9.     .group_by(orders.c.customer_id)
  10.     .subquery()
  11. )
  13. customer_select = (
  14.     select(customers, subq)
  15.     .join_from(customers, subq, customers.c.id == subq.c.customer_id)
  16.     .subquery()
  17. )
  20. class Customer(Base):
  21.     __table__ = customer_select

For background on mapping to non-Table constructs see the sections Mapping a Class against Multiple Tables and Mapping a Class against Arbitrary Subqueries.

The “imperative table” form is of particular use when the class itself is using an alternative form of attribute declaration, such as Python dataclasses. See the section Applying ORM Mappings to an existing dataclass for detail.

See also

Describing Databases with MetaData

Applying ORM Mappings to an existing dataclass

Alternate Attribute Names for Mapping Table Columns

The section Naming Declarative Mapped Columns Explicitly illustrated how to use mapped_column() to provide a specific name for the generated Column object separate from the attribute name under which it is mapped.

When using Imperative Table configuration, we already have Column objects present. To map these to alternate names we may assign the Column to the desired attributes directly:

  1. user_table = Table(
  2.     "user",
  3.     Base.metadata,
  4.     Column("user_id", Integer, primary_key=True),
  5.     Column("user_name", String),
  6. )
  9. class User(Base):
  10.     __table__ = user_table
  12.     id = user_table.c.user_id
  13.     name = user_table.c.user_name

The User mapping above will refer to the "user_id" and "user_name" columns via the User.id and User.name attributes, in the same way as demonstrated at Naming Declarative Mapped Columns Explicitly.

One caveat to the above mapping is that the direct inline link to Column will not be typed correctly when using PEP 484 typing tools. A strategy to resolve this is to apply the Column objects within the column_property() function; while the Mapper already generates this property object for its internal use automatically, by naming it in the class declaration, typing tools will be able to match the attribute to the Mapped annotation:

  1. from sqlalchemy.orm import column_property
  2. from sqlalchemy.orm import Mapped
  5. class User(Base):
  6.     __table__ = user_table
  8.     id: Mapped[int] = column_property(user_table.c.user_id)
  9.     name: Mapped[str] = column_property(user_table.c.user_name)

See also

Naming Declarative Mapped Columns Explicitly - applies to Declarative Table

Applying Load, Persistence and Mapping Options for Mapped Table Columns

The section Setting Load and Persistence Options for Declarative Mapped Columns reviewed how to set load and persistence options when using the mapped_column() construct with Declarative Table configuration. When using Imperative Table configuration, we already have existing Column objects that are mapped. In order to map these Column objects along with additional parameters that are specific to the ORM mapping, we may use the column_property() and deferred() constructs in order to associate additional parameters with the column. Options include:

  • deferred column loading - The deferred() function is shorthand for invoking column_property() with the column_property.deferred parameter set to True; this construct establishes the Column using deferred column loading by default. In the example below, the User.bio column will not be loaded by default, but only when accessed:

    ``` from sqlalchemy.orm import deferred

    user_table = Table(

    1. "user",
    2. Base.metadata,
    3. Column("id", Integer, primary_key=True),
    4. Column("name", String),
    5. Column("bio", Text),

    )

  1. class User(Base):
  2.     __table__ = user_table
  4.     bio = deferred(user_table.c.bio)
  5. ```

See also

Limiting which Columns Load with Column Deferral - full description of deferred column loading

  • active history - The column_property.active_history ensures that upon change of value for the attribute, the previous value will have been loaded and made part of the AttributeState.history collection when inspecting the history of the attribute. This may incur additional SQL statements:

    ``` from sqlalchemy.orm import deferred

    user_table = Table(

    1. "user",
    2. Base.metadata,
    3. Column("id", Integer, primary_key=True),
    4. Column("important_identifier", String),

    )

  1. class User(Base):
  2.     __table__ = user_table
  4.     important_identifier = column_property(
  5.         user_table.c.important_identifier, active_history=True
  6.     )
  7. ```

See also

The column_property() construct is also important for cases where classes are mapped to alternative FROM clauses such as joins and selects. More background on these cases is at:

For Declarative Table configuration with mapped_column(), most options are available directly; see the section Setting Load and Persistence Options for Declarative Mapped Columns for examples.

Mapping Declaratively with Reflected Tables

There are several patterns available which provide for producing mapped classes against a series of Table objects that were introspected from the database, using the reflection process described at Reflecting Database Objects.

A simple way to map a class to a table reflected from the database is to use a declarative hybrid mapping, passing the Table.autoload_with parameter to the constructor for Table:

  1. from sqlalchemy import create_engine
  2. from sqlalchemy import Table
  3. from sqlalchemy.orm import DeclarativeBase
  5. engine = create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")
  8. class Base(DeclarativeBase):
  9.     pass
  12. class MyClass(Base):
  13.     __table__ = Table(
  14.         "mytable",
  15.         Base.metadata,
  16.         autoload_with=engine,
  17.     )

A variant on the above pattern that scales for many tables is to use the MetaData.reflect() method to reflect a full set of Table objects at once, then refer to them from the MetaData:

  1. from sqlalchemy import create_engine
  2. from sqlalchemy import Table
  3. from sqlalchemy.orm import DeclarativeBase
  5. engine = create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")
  8. class Base(DeclarativeBase):
  9.     pass
  12. Base.metadata.reflect(engine)
  15. class MyClass(Base):
  16.     __table__ = Base.metadata.tables["mytable"]

One caveat to the approach of using __table__ is that the mapped classes cannot be declared until the tables have been reflected, which requires the database connectivity source to be present while the application classes are being declared; it’s typical that classes are declared as the modules of an application are being imported, but database connectivity isn’t available until the application starts running code so that it can consume configuration information and create an engine. There are currently two approaches to working around this, described in the next two sections.

Using DeferredReflection

To accommodate the use case of declaring mapped classes where reflection of table metadata can occur afterwards, a simple extension called the DeferredReflection mixin is available, which alters the declarative mapping process to be delayed until a special class-level DeferredReflection.prepare() method is called, which will perform the reflection process against a target database, and will integrate the results with the declarative table mapping process, that is, classes which use the __tablename__ attribute:

  1. from sqlalchemy.ext.declarative import DeferredReflection
  2. from sqlalchemy.orm import DeclarativeBase
  5. class Base(DeclarativeBase):
  6.     pass
  9. class Reflected(DeferredReflection):
  10.     __abstract__ = True
  13. class Foo(Reflected, Base):
  14.     __tablename__ = "foo"
  15.     bars = relationship("Bar")
  18. class Bar(Reflected, Base):
  19.     __tablename__ = "bar"
  21.     foo_id = mapped_column(Integer, ForeignKey("foo.id"))

Above, we create a mixin class Reflected that will serve as a base for classes in our declarative hierarchy that should become mapped when the Reflected.prepare method is called. The above mapping is not complete until we do so, given an Engine:

  1. engine = create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")
  2. Reflected.prepare(engine)

The purpose of the Reflected class is to define the scope at which classes should be reflectively mapped. The plugin will search among the subclass tree of the target against which .prepare() is called and reflect all tables which are named by declared classes; tables in the target database that are not part of mappings and are not related to the target tables via foreign key constraint will not be reflected.

Using Automap

A more automated solution to mapping against an existing database where table reflection is to be used is to use the Automap extension. This extension will generate entire mapped classes from a database schema, including relationships between classes based on observed foreign key constraints. While it includes hooks for customization, such as hooks that allow custom class naming and relationship naming schemes, automap is oriented towards an expedient zero-configuration style of working. If an application wishes to have a fully explicit model that makes use of table reflection, the DeferredReflection class may be preferable for its less automated approach.

See also

Automap

Automating Column Naming Schemes from Reflected Tables

When using any of the previous reflection techniques, we have the option to change the naming scheme by which columns are mapped. The Column object includes a parameter Column.key which is a string name that determines under what name this Column will be present in the Table.c collection, independently of the SQL name of the column. This key is also used by Mapper as the attribute name under which the Column will be mapped, if not supplied through other means such as that illustrated at Alternate Attribute Names for Mapping Table Columns.

When working with table reflection, we can intercept the parameters that will be used for Column as they are received using the DDLEvents.column_reflect() event and apply whatever changes we need, including the .key attribute but also things like datatypes.

The event hook is most easily associated with the MetaData object that’s in use as illustrated below:

  1. from sqlalchemy import event
  2. from sqlalchemy.orm import DeclarativeBase
  5. class Base(DeclarativeBase):
  6.     pass
  9. @event.listens_for(Base.metadata, "column_reflect")
  10. def column_reflect(inspector, table, column_info):
  11.     # set column.key = "attr_<lower_case_name>"
  12.     column_info["key"] = "attr_%s" % column_info["name"].lower()

With the above event, the reflection of Column objects will be intercepted with our event that adds a new “.key” element, such as in a mapping as below:

  1. class MyClass(Base):
  2.     __table__ = Table("some_table", Base.metadata, autoload_with=some_engine)

The approach also works with both the DeferredReflection base class as well as with the Automap extension. For automap specifically, see the section Intercepting Column Definitions for background.

See also

Mapping Declaratively with Reflected Tables

DDLEvents.column_reflect()

Intercepting Column Definitions - in the Automap documentation

Mapping to an Explicit Set of Primary Key Columns

The Mapper construct in order to successfully map a table always requires that at least one column be identified as the “primary key” for that selectable. This is so that when an ORM object is loaded or persisted, it can be placed in the identity map with an appropriate identity key.

In those cases where the a reflected table to be mapped does not include a primary key constraint, as well as in the general case for mapping against arbitrary selectables where primary key columns might not be present, the Mapper.primary_key parameter is provided so that any set of columns may be configured as the “primary key” for the table, as far as ORM mapping is concerned.

Given the following example of an Imperative Table mapping against an existing Table object where the table does not have any declared primary key (as may occur in reflection scenarios), we may map such a table as in the following example:

  1. from sqlalchemy import Column
  2. from sqlalchemy import MetaData
  3. from sqlalchemy import String
  4. from sqlalchemy import Table
  5. from sqlalchemy import UniqueConstraint
  6. from sqlalchemy.orm import DeclarativeBase
  9. metadata = MetaData()
  10. group_users = Table(
  11.     "group_users",
  12.     metadata,
  13.     Column("user_id", String(40), nullable=False),
  14.     Column("group_id", String(40), nullable=False),
  15.     UniqueConstraint("user_id", "group_id"),
  16. )
  19. class Base(DeclarativeBase):
  20.     pass
  23. class GroupUsers(Base):
  24.     __table__ = group_users
  25.     __mapper_args__ = {"primary_key": [group_users.c.user_id, group_users.c.group_id]}

Above, the group_users table is an association table of some kind with string columns user_id and group_id, but no primary key is set up; instead, there is only a UniqueConstraint establishing that the two columns represent a unique key. The Mapper does not automatically inspect unique constraints for primary keys; instead, we make use of the Mapper.primary_key parameter, passing a collection of [group_users.c.user_id, group_users.c.group_id], indicating that these two columns should be used in order to construct the identity key for instances of the GroupUsers class.

Mapping a Subset of Table Columns

Sometimes table reflection may provide a Table with many columns that are not important for our needs and may be safely ignored. For such a table that has lots of columns that don’t need to be referenced in the application, the Mapper.include_properties or Mapper.exclude_properties parameters can indicate a subset of columns to be mapped, where other columns from the target Table will not be considered by the ORM in any way. Example:

  1. class User(Base):
  2.     __table__ = user_table
  3.     __mapper_args__ = {"include_properties": ["user_id", "user_name"]}

In the above example, the User class will map to the user_table table, only including the user_id and user_name columns - the rest are not referenced.

Similarly:

  1. class Address(Base):
  2.     __table__ = address_table
  3.     __mapper_args__ = {"exclude_properties": ["street", "city", "state", "zip"]}

will map the Address class to the address_table table, including all columns present except street, city, state, and zip.

As indicated in the two examples, columns may be referred towards either by string name or by referring to the Column object directly. Referring to the object directly may be useful for explicitness as well as to resolve ambiguities when mapping to multi-table constructs that might have repeated names:

  1. class User(Base):
  2.     __table__ = user_table
  3.     __mapper_args__ = {
  4.         "include_properties": [user_table.c.user_id, user_table.c.user_name]
  5.     }

When columns are not included in a mapping, these columns will not be referenced in any SELECT statements emitted when executing select() or legacy Query objects, nor will there be any mapped attribute on the mapped class which represents the column; assigning an attribute of that name will have no effect beyond that of a normal Python attribute assignment.

However, it is important to note that schema level column defaults WILL still be in effect for those Column objects that include them, even though they may be excluded from the ORM mapping.

“Schema level column defaults” refers to the defaults described at Column INSERT/UPDATE Defaults including those configured by the Column.default, Column.onupdate, Column.server_default and Column.server_onupdate parameters. These constructs continue to have normal effects because in the case of Column.default and Column.onupdate, the Column object is still present on the underlying Table, thus allowing the default functions to take place when the ORM emits an INSERT or UPDATE, and in the case of Column.server_default and Column.server_onupdate, the relational database itself emits these defaults as a server side behavior.