Mapping Python Classes

    Both styles may be used interchangeably, as the end result of each is exactly the same - a user-defined class that has a configured against a selectable unit, typically represented by a 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 construct that represents a collection of related mappings.

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

    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 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:

    1. from sqlalchemy import Column, Integer, String, ForeignKey
    2. from sqlalchemy.orm import declarative_base
    4. # declarative base class
    5. Base = declarative_base()
    7. # an example mapping using the base
    8. class User(Base):
    9.     __tablename__ = 'user'
    11.     id = Column(Integer, primary_key=True)
    12.     name = Column(String)
    13.     fullname = Column(String)
    14.     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 object that maintains a collection of related mapped classes. The declarative_base() function is in fact shorthand for first creating the registry with the constructor, and then generating a base class using the registry.generate_base() method:

    1. from sqlalchemy.orm import registry
    3. # equivalent to Base = declarative_base()
    5. mapper_registry = registry()
    6. Base = mapper_registry.generate_base()

    The is used directly in order to access a variety of mapping styles to suit different use cases:

    Documentation for Declarative mapping continues at Mapping Classes with Declarative.

    See also

    SQLAlchemy includes a Mypy plugin that automatically accommodates for the dynamically generated Base class delivered by SQLAlchemy functions like . This plugin works along with a new set of typing stubs published at sqlalchemy2-stubs.

    When this plugin is not in use, or when using other tools which may not know how to interpret this class, the declarative base class may be produced in a fully explicit fashion using the DeclarativeMeta directly as follows:

    1. from sqlalchemy.orm import registry
    2. from sqlalchemy.orm.decl_api import DeclarativeMeta
    4. mapper_registry = registry()
    6. class Base(metaclass=DeclarativeMeta):
    7.     __abstract__ = True
    9.     # these are supplied by the sqlalchemy2-stubs, so may be omitted
    10.     # when they are installed
    11.     registry = mapper_registry
    12.     metadata = mapper_registry.metadata

    The above Base is equivalent to one created using the registry.generate_base() method and will be fully understood by type analysis tools without the use of plugins.

    See also

    - background on the Mypy plugin which applies the above structure automatically when running Mypy.

    Declarative Mapping using a Decorator (no declarative base)

    As an alternative to using the “declarative base” class is to apply declarative mapping to a class explicitly, using either an imperative technique similar to that of a “classical” mapping, or more succinctly by using a decorator. The function is a class decorator that can be applied to any Python class with no hierarchy in place. The Python class otherwise is configured in declarative style normally:

    1. from sqlalchemy import Column, Integer, String, Text, ForeignKey
    3. from sqlalchemy.orm import registry
    4. from sqlalchemy.orm import relationship
    6. mapper_registry = registry()
    8. @mapper_registry.mapped
    9. class User:
    10.     __tablename__ = 'user'
    12.     id = Column(Integer, primary_key=True)
    13.     name = Column(String)
    15.     addresses = relationship("Address", back_populates="user")
    17. @mapper_registry.mapped
    18. class Address:
    19.     __tablename__ = 'address'
    21.     id = Column(Integer, primary_key=True)
    22.     user_id = Column(ForeignKey("user.id"))
    23.     email_address = Column(String)
    25.     user = relationship("User", back_populates="addresses")

    Above, the same registry that we’d use to generate a declarative base class via its method may also apply a declarative-style mapping to a class without using a base. When using the above style, the mapping of a particular class will only proceed if the decorator is applied to that class directly. For inheritance mappings, the decorator should be applied to each subclass:

    1. from sqlalchemy.orm import registry
    2. mapper_registry = registry()
    4. @mapper_registry.mapped
    5. class Person:
    6.     __tablename__ = "person"
    8.     person_id = Column(Integer, primary_key=True)
    9.     type = Column(String, nullable=False)
    11.     __mapper_args__ = {
    13.         "polymorphic_on": type,
    14.         "polymorphic_identity": "person"
    15.     }
    18. @mapper_registry.mapped
    19. class Employee(Person):
    20.     __tablename__ = "employee"
    22.     person_id = Column(ForeignKey("person.person_id"), primary_key=True)
    24.     __mapper_args__ = {
    25.         "polymorphic_identity": "employee"
    26.     }

    Both the “declarative table” and “imperative table” styles of declarative mapping may be used with the above mapping style.

    The decorator form of mapping is particularly useful when combining a SQLAlchemy declarative mapping with other forms of class declaration, notably the Python dataclasses module. See the next section.

    Declarative Mapping with Dataclasses and Attrs

    The class decorator allows the declarative mapping of a class to occur after the class has been fully constructed, allowing the class to be processed by other class decorators first. The @dataclass and @attr.s decorators may therefore be applied first before the ORM mapping process proceeds via the registry.mapped() decorator or via the method discussed in a later section.

    Mapping with @dataclass or @attr.s may be used in a straightforward way with Declarative with Imperative Table (a.k.a. Hybrid Declarative) style, where the the , which means that it is defined separately and associated with the class via the __table__. For dataclasses specifically, Declarative Table is also supported.

    New in version 1.4.0b2: Added support for full declarative mapping when using dataclasses.

    When attributes are defined using dataclasses, the @dataclass decorator consumes them but leaves them in place on the class. SQLAlchemy’s mapping process, when it encounters an attribute that normally is to be mapped to a , checks explicitly if the attribute is part of a Dataclasses setup, and if so will replace the class-bound dataclass attribute with its usual mapped properties. The __init__ method created by @dataclass is left intact. In contrast, the @attr.s decorator actually removes its own class-bound attributes after the decorator runs, so that SQLAlchemy’s mapping process takes over these attributes without any issue.

    New in version 1.4: Added support for direct mapping of Python dataclasses, where the Mapper will now detect attributes that are specific to the @dataclasses module and replace them at mapping time, rather than skipping them as is the default behavior for any class attribute that’s not part of the mapping.

    Example One - Dataclasses with Imperative Table

    An example of a mapping using @dataclass using Declarative with Imperative Table (a.k.a. Hybrid Declarative) is as follows:

    1. from __future__ import annotations
    3. from dataclasses import field
    4. from typing import List
    5. from typing import Optional
    7. from sqlalchemy import Column
    8. from sqlalchemy import ForeignKey
    9. from sqlalchemy import Integer
    10. from sqlalchemy import String
    11. from sqlalchemy import Table
    12. from sqlalchemy.orm import registry
    13. from sqlalchemy.orm import relationship
    15. mapper_registry = registry()
    18. @mapper_registry.mapped
    19. @dataclass
    20. class User:
    21.     __table__ = Table(
    22.         "user",
    23.         mapper_registry.metadata,
    24.         Column("id", Integer, primary_key=True),
    25.         Column("name", String(50)),
    26.         Column("fullname", String(50)),
    28.     )
    29.     id: int = field(init=False)
    30.     name: Optional[str] = None
    31.     fullname: Optional[str] = None
    32.     nickname: Optional[str] = None
    33.     addresses: List[Address] = field(default_factory=list)
    35.     __mapper_args__ = {   # type: ignore
    36.         "properties" : {
    37.             "addresses": relationship("Address")
    38.         }
    39.     }
    41. @mapper_registry.mapped
    42. @dataclass
    43. class Address:
    44.     __table__ = Table(
    45.         "address",
    46.         mapper_registry.metadata,
    47.         Column("id", Integer, primary_key=True),
    48.         Column("user_id", Integer, ForeignKey("user.id")),
    49.         Column("email_address", String(50)),
    50.     )
    51.     id: int = field(init=False)
    52.     user_id: int = field(init=False)
    53.     email_address: Optional[str] = None

    In the above example, the User.id, Address.id, and Address.user_id attributes are defined as field(init=False). This means that parameters for these won’t be added to __init__() methods, but will still be able to set them after getting their values during flush from autoincrement or other default value generator. To allow them to be specified in the constructor explicitly, they would instead be given a default value of None.

    For a relationship() to be declared separately, it needs to be specified directly within the dictionary passed to the mapper(). An alternative to this approach is in the next example.

    Example Two - Dataclasses with Declarative Table

    The fully declarative approach requires that Column objects are declared as class attributes, which when using dataclasses would conflict with the dataclass-level attributes. An approach to combine these together is to make use of the metadata attribute on the dataclass.field object, where SQLAlchemy-specific mapping information may be supplied. Declarative supports extraction of these parameters when the class specifies the attribute __sa_dataclass_metadata_key__. This also provides a more succinct method of indicating the association:

    1. from __future__ import annotations
    3. from dataclasses import dataclass
    4. from dataclasses import field
    5. from typing import List
    7. from sqlalchemy import Column
    8. from sqlalchemy import ForeignKey
    9. from sqlalchemy import Integer
    10. from sqlalchemy import String
    11. from sqlalchemy.orm import registry
    12. from sqlalchemy.orm import relationship
    14. mapper_registry = registry()
    17. @mapper_registry.mapped
    18. @dataclass
    19. class User:
    20.     __tablename__ = "user"
    22.     __sa_dataclass_metadata_key__ = "sa"
    23.     id: int = field(
    24.         init=False, metadata={"sa": Column(Integer, primary_key=True)}
    25.     )
    26.     name: str = field(default=None, metadata={"sa": Column(String(50))})
    27.     fullname: str = field(default=None, metadata={"sa": Column(String(50))})
    28.     nickname: str = field(default=None, metadata={"sa": Column(String(12))})
    29.     addresses: List[Address] = field(
    30.         default_factory=list, metadata={"sa": relationship("Address")}
    31.     )
    34. @mapper_registry.mapped
    35. @dataclass
    36. class Address:
    37.     __tablename__ = "address"
    38.     __sa_dataclass_metadata_key__ = "sa"
    39.     id: int = field(
    40.         init=False, metadata={"sa": Column(Integer, primary_key=True)}
    41.     )
    42.     user_id: int = field(
    43.         init=False, metadata={"sa": Column(ForeignKey("user.id"))}
    44.     )
    45.     email_address: str = field(
    46.         default=None, metadata={"sa": Column(String(50))}
    47.     )

    Example Three - attrs with Imperative Table

    A mapping using @attr.s, in conjunction with imperative table:

    @dataclass and mappings may also be used with classical mappings, i.e. with the registry.map_imperatively() function. See the section for a similar example.

    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. The “map imperative” style has historically been achieved using the function directly, however this function now expects that a sqlalchemy.orm.registry() is present.

    Deprecated since version 1.4: Using the function directly to achieve a classical mapping directly is deprecated. The registry.map_imperatively() method retains the identical functionality while also allowing for string-based resolution of other mapped classes from within the registry.

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

    1. from sqlalchemy import Table, Column, Integer, String, ForeignKey
    2. from sqlalchemy.orm import registry
    4. mapper_registry = registry()
    6. user_table = Table(
    7.     'user',
    8.     mapper_registry.metadata,
    9.     Column('id', Integer, primary_key=True),
    10.     Column('name', String(50)),
    11.     Column('fullname', String(50)),
    12.     Column('nickname', String(12))
    13. )
    15.     pass
    17. 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 object, mapped to a class called Address, then linked to User via relationship():

    1. address = Table('address', metadata,
    2.             Column('id', Integer, primary_key=True),
    3.             Column('user_id', Integer, ForeignKey('user.id')),
    4.             Column('email_address', String(50))
    5.             )
    7. mapper(User, user, properties={
    8.     'addresses' : relationship(Address, backref='user', order_by=address.c.id)
    9. })
    11. mapper(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 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 . 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.

    As described in the section , 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 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 and Mapper configuration imperatively to the function rather than having them defined on the class itself as declarative class variables:

    1. from __future__ import annotations
    3. from dataclasses import dataclass
    4. from dataclasses import field
    5. from typing import List
    7. from sqlalchemy import Column
    8. from sqlalchemy import ForeignKey
    10. from sqlalchemy import MetaData
    11. from sqlalchemy import String
    12. from sqlalchemy import Table
    13. from sqlalchemy.orm import registry
    14. from sqlalchemy.orm import relationship
    16. mapper_registry = registry()
    18. @dataclass
    19. class User:
    20.     id: int = field(init=False)
    21.     name: str = None
    22.     fullname: str = None
    23.     nickname: str = None
    24.     addresses: List[Address] = field(default_factory=list)
    26. @dataclass
    27. class Address:
    28.     id: int = field(init=False)
    29.     user_id: int = field(init=False)
    30.     email_address: str = None
    32. metadata = MetaData()
    34. user = Table(
    35.     'user',
    36.     metadata,
    37.     Column('id', Integer, primary_key=True),
    38.     Column('name', String(50)),
    39.     Column('fullname', String(50)),
    40.     Column('nickname', String(12)),
    41. )
    43. address = Table(
    44.     'address',
    45.     metadata,
    46.     Column('id', Integer, primary_key=True),
    47.     Column('user_id', Integer, ForeignKey('user.id')),
    48.     Column('email_address', String(50)),
    49. )
    51. mapper_registry.map_imperatively(User, user, properties={
    52.     'addresses': relationship(Address, backref='user', order_by=address.c.id),
    53. })
    55. mapper_registry.map_imperatively(Address, address)

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

    There are four general classes of configuration information that the mapper() function 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 object for the class. 2

    When mapping with the 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 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 object, the most common alternative objects being the Subquery and 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 objects presented, or it is established via the __table__ attribute. These two styles of configuration are presented at Declarative Table and .

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

    In contrast to the “one mapper per class” requirement of a mapped class, the Table or other 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 or other FromClause in any way.

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

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

    Other mapper configuration parameters

    These flags are documented at .

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

    When mapping with the imperative style, keyword arguments are passed to the to method which passes them along to the mapper() function.

    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 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.

    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.:

    1. from sqlalchemy.orm import declarative_base
    3. Base = declarative_base()
    5. class User(Base):
    6.     __tablename__ = 'user'
    8.     id = Column(...)
    9.     name = Column(...)
    10.     fullname = Column(...)

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

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

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

    The constructor also applies to imperative mappings:

    1. from sqlalchemy.orm import registry
    3. mapper_registry = registry()
    5. user_table = Table(
    6.     'user',
    7.     mapper_registry.metadata,
    8.     Column('id', Integer, primary_key=True),
    9.     Column('name', String(50))
    10. )
    12. class User:
    13.     pass
    15. 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 .

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

    A class that is mapped using 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:

      1. mapper = User.__mapper__

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

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

      1. table = User.__table__

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

      1. 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 attribute:

      1. table = inspect(User).selectable

    Mapper Inspection Features

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

    1. >>> from sqlalchemy import inspect
    2. >>> insp = inspect(User)

    Detailed information is available including :

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

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

    1. >>> list(insp.columns)
    2. [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>)]
    3. >>> insp.columns.name
    4. 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:

    1. >>> insp.all_orm_descriptors
    2. <sqlalchemy.util._collections.ImmutableProperties object at 0x1040e2c68>
    3. >>> insp.all_orm_descriptors.keys()

    As well as :

    See also

    Runtime Inspection API