Mapping Python Classes

SQLAlchemy historically features two distinct styles of mapper configuration. The original mapping API is commonly referred to as “classical” style, whereas the more automated style of mapping is known as “declarative” style. SQLAlchemy now refers to these two mapping styles as imperative mapping and declarative mapping.

Both styles may be used interchangeably, as the end result of each is exactly the same - a user-defined class that has a Mapper configured against a selectable unit, typically represented by a Table object.

Both imperative and declarative mapping begin with an ORM registry object, which maintains a set of classes that are mapped. This registry is present for all mappings.

Changed in version 1.4: Declarative and classical mapping are now referred to as “declarative” and “imperative” mapping, and are unified internally, all originating from the registry construct that represents a collection of related mappings.

The full suite of styles can be hierarchically organized as follows:

Declarative Mapping

The Declarative Mapping is the typical way that mappings are constructed in modern SQLAlchemy. The most common pattern is to first construct a base class using the declarative_base() function, which will apply the declarative mapping process to all subclasses that derive from it. Below features a declarative base which is then used in a declarative table mapping:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base

# declarative base class
Base = declarative_base()

# an example mapping using the base
class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

Above, the declarative_base() callable returns a new base class from which new classes to be mapped may inherit from, as above a new mapped class User is constructed.

The base class refers to a registry object that maintains a collection of related mapped classes. The declarative_base() function is in fact shorthand for first creating the registry with the registry constructor, and then generating a base class using the registry.generate_base() method:

from sqlalchemy.orm import registry

# equivalent to Base = declarative_base()

mapper_registry = registry()
Base = mapper_registry.generate_base()

The registry is used directly in order to access a variety of mapping styles to suit different use cases. The primary mapping styles offered by registry are further detailed in the following sections:

Documentation for Declarative mapping continues at Mapping Classes with Declarative.

See also

Imperative (a.k.a. Classical) Mappings

An imperative or classical mapping refers to the configuration of a mapped class using the registry.map_imperatively() method, where the target class does not include any declarative class attributes.

Changed in version 2.0: The registry.map_imperatively() method is now used to create classical mappings. The sqlalchemy.orm.mapper() standalone function is effectively removed.

In “classical” form, the table metadata is created separately with the Table construct, then associated with the User class via the registry.map_imperatively() method:

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import registry

mapper_registry = registry()

user_table = Table(
    'user',
    mapper_registry.metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('fullname', String(50)),
    Column('nickname', String(12))
)

class User:
    pass

mapper_registry.map_imperatively(User, user_table)

Information about mapped attributes, such as relationships to other classes, are provided via the properties dictionary. The example below illustrates a second Table object, mapped to a class called Address, then linked to User via relationship():

address = Table('address', metadata_obj,
            Column('id', Integer, primary_key=True),
            Column('user_id', Integer, ForeignKey('user.id')),
            Column('email_address', String(50))
            )

mapper_registry.map_imperatively(User, user, properties={
    'addresses' : relationship(Address, backref='user', order_by=address.c.id)
})

mapper_registry.map_imperatively(Address, address)

When using classical mappings, classes must be provided directly without the benefit of the “string lookup” system provided by Declarative. SQL expressions are typically specified in terms of the Table objects, i.e. address.c.id above for the Address relationship, and not Address.id, as Address may not yet be linked to table metadata, nor can we specify a string here.

Some examples in the documentation still use the classical approach, but note that the classical as well as Declarative approaches are fully interchangeable. Both systems ultimately create the same configuration, consisting of a Table, user-defined class, linked together with a Mapper object. When we talk about “the behavior of Mapper”, this includes when using the Declarative system as well - it’s still used, just behind the scenes.

Imperative Mapping with Dataclasses and Attrs

As described in the section Declarative Mapping with Dataclasses and Attrs, the @dataclass decorator and the attrs_ library both work as class decorators that are applied to a class first, before it is passed to SQLAlchemy for mapping. Just like we can use the registry.mapped() decorator in order to apply declarative-style mapping to the class, we can also pass it to the registry.map_imperatively() method so that we may pass all Table and Mapper configuration imperatively to the function rather than having them defined on the class itself as declarative class variables:

from __future__ import annotations

from dataclasses import dataclass
from dataclasses import field
from typing import List

from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import registry
from sqlalchemy.orm import relationship

mapper_registry = registry()

@dataclass
class User:
    id: int = field(init=False)
    name: str = None
    fullname: str = None
    nickname: str = None
    addresses: List[Address] = field(default_factory=list)

@dataclass
class Address:
    id: int = field(init=False)
    user_id: int = field(init=False)
    email_address: str = None

metadata_obj = MetaData()

user = Table(
    'user',
    metadata_obj,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('fullname', String(50)),
    Column('nickname', String(12)),
)

address = Table(
    'address',
    metadata_obj,
    Column('id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('email_address', String(50)),
)

mapper_registry.map_imperatively(User, user, properties={
    'addresses': relationship(Address, backref='user', order_by=address.c.id),
})

mapper_registry.map_imperatively(Address, address)

Mapper Configuration Overview

With all mapping forms, the mapping of the class can be configured in many ways by passing construction arguments that become part of the Mapper object. The construct which ultimately receives these arguments is the constructor to the Mapper class, and the arguments are delivered to it originating from one of the front-facing mapping functions defined on the registry object.

There are four general classes of configuration information that the Mapper class looks for:

The class to be mapped

This is a class that we construct in our application. There are generally no restrictions on the structure of this class. 1 When a Python class is mapped, there can only be one Mapper object for the class. 2

When mapping with the declarative mapping style, the class to be mapped is either a subclass of the declarative base class, or is handled by a decorator or function such as registry.mapped().

When mapping with the imperative style, the class is passed directly as the map_imperatively.class_ argument.

The table, or other from clause object

In the vast majority of common cases this is an instance of Table. For more advanced use cases, it may also refer to any kind of FromClause object, the most common alternative objects being the Subquery and Join object.

When mapping with the declarative mapping style, the subject table is either generated by the declarative system based on the __tablename__ attribute and the Column objects presented, or it is established via the __table__ attribute. These two styles of configuration are presented at Declarative Table and Declarative with Imperative Table (a.k.a. Hybrid Declarative).

When mapping with the imperative style, the subject table is passed positionally as the map_imperatively.local_table argument.

In contrast to the “one mapper per class” requirement of a mapped class, the Table or other FromClause object that is the subject of the mapping may be associated with any number of mappings. The Mapper applies modifications directly to the user-defined class, but does not modify the given Table or other FromClause in any way.

The properties dictionary

This is a dictionary of all of the attributes that will be associated with the mapped class. By default, the Mapper generates entries for this dictionary derived from the given Table, in the form of ColumnProperty objects which each refer to an individual Column of the mapped table. The properties dictionary will also contain all the other kinds of MapperProperty objects to be configured, most commonly instances generated by the relationship() construct.

When mapping with the declarative mapping style, the properties dictionary is generated by the declarative system by scanning the class to be mapped for appropriate attributes. See the section Defining Mapped Properties with Declarative for notes on this process.

When mapping with the imperative style, the properties dictionary is passed directly as the properties argument to registry.map_imperatively(), which will pass it along to the Mapper.properties parameter.

Other mapper configuration parameters

These flags are documented at Mapper.

When mapping with the declarative mapping style, additional mapper configuration arguments are configured via the __mapper_args__ class attribute, documented at Mapper Configuration Options with Declarative

When mapping with the imperative style, keyword arguments are passed to the to registry.map_imperatively() method which passes them along to the Mapper class.

1

When running under Python 2, a Python 2 “old style” class is the only kind of class that isn’t compatible. When running code on Python 2, all classes must extend from the Python object class. Under Python 3 this is always the case.

2

There is a legacy feature known as a “non primary mapper”, where additional Mapper objects may be associated with a class that’s already mapped, however they don’t apply instrumentation to the class. This feature is deprecated as of SQLAlchemy 1.3.

Mapped Class Behavior

Across all styles of mapping using the registry object, the following behaviors are common:

Default Constructor

The registry applies a default constructor, i.e. __init__ method, to all mapped classes that don’t explicitly have their own __init__ method. The behavior of this method is such that it provides a convenient keyword constructor that will accept as optional keyword arguments all the attributes that are named. E.g.:

from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(...)
    name = Column(...)
    fullname = Column(...)

An object of type User above will have a constructor which allows User objects to be created as:

u1 = User(name='some name', fullname='some fullname')

The above constructor may be customized by passing a Python callable to the registry.constructor parameter which provides the desired default __init__() behavior.

The constructor also applies to imperative mappings:

from sqlalchemy.orm import registry

mapper_registry = registry()

user_table = Table(
    'user',
    mapper_registry.metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50))
)

class User:
    pass

mapper_registry.map_imperatively(User, user_table)

The above class, mapped imperatively as described at Imperative (a.k.a. Classical) Mappings, will also feature the default constructor associated with the registry.

New in version 1.4: classical mappings now support a standard configuration-level constructor when they are mapped via the registry.map_imperatively() method.

Runtime Introspection of Mapped classes and Mappers

A class that is mapped using registry will also feature a few attributes that are common to all mappings:

  • The __mapper__ attribute will refer to the Mapper that is associated with the class:

    mapper = User.__mapper__

    This Mapper is also what’s returned when using the inspect() function against the mapped class:

    from sqlalchemy import inspect

mapper = inspect(User)

  • The __table__ attribute will refer to the Table, or more generically to the FromClause object, to which the class is mapped:

    table = User.__table__

    This FromClause is also what’s returned when using the Mapper.local_table attribute of the Mapper:

    table = inspect(User).local_table

    For a single-table inheritance mapping, where the class is a subclass that does not have a table of its own, the Mapper.local_table attribute as well as the .__table__ attribute will be None. To retrieve the “selectable” that is actually selected from during a query for this class, this is available via the Mapper.selectable attribute:

    table = inspect(User).selectable

Mapper Inspection Features

As illustrated in the previous section, the Mapper object is available from any mapped class, regardless of method, using the Runtime Inspection API system. Using the inspect() function, one can acquire the Mapper from a mapped class:

>>> from sqlalchemy import inspect
>>> insp = inspect(User)

Detailed information is available including Mapper.columns:

>>> insp.columns
<sqlalchemy.util._collections.OrderedProperties object at 0x102f407f8>

This is a namespace that can be viewed in a list format or via individual names:

>>> list(insp.columns)
[Column('id', Integer(), table=<user>, primary_key=True, nullable=False), Column('name', String(length=50), table=<user>), Column('fullname', String(length=50), table=<user>), Column('nickname', String(length=50), table=<user>)]
>>> insp.columns.name
Column('name', String(length=50), table=<user>)

Other namespaces include Mapper.all_orm_descriptors, which includes all mapped attributes as well as hybrids, association proxies:

>>> insp.all_orm_descriptors
<sqlalchemy.util._collections.ImmutableProperties object at 0x1040e2c68>
>>> insp.all_orm_descriptors.keys()
['fullname', 'nickname', 'name', 'id']

As well as Mapper.column_attrs:

>>> list(insp.column_attrs)
[<ColumnProperty at 0x10403fde0; id>, <ColumnProperty at 0x10403fce8; name>, <ColumnProperty at 0x1040e9050; fullname>, <ColumnProperty at 0x1040e9148; nickname>]
>>> insp.column_attrs.name
<ColumnProperty at 0x10403fce8; name>
>>> insp.column_attrs.name.expression
Column('name', String(length=50), table=<user>)

See also

Runtime Inspection API

Mapper

InstanceState