SQLAlchemy 2.0 Documentation
SQLAlchemy ORM
- ORM Quick Start
- ORM Mapped Class Configuration
- ORM Mapped Class Overview
- Mapping Classes with Declarative
- Declarative Mapping Styles
- Table Configuration with Declarative
- Mapper Configuration with Declarative¶
- Composing Mapped Hierarchies with Mixins
- Integration with dataclasses and attrs
- SQL Expressions as Mapped Attributes
- Changing Attribute Behavior
- Composite Column Types
- Mapping Class Inheritance Hierarchies
- Non-Traditional Mappings
- Configuring a Version Counter
- Class Mapping API
- Mapping SQL Expressions
- Relationship Configuration
- ORM Querying Guide
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Table Configuration with Declarative
- Next: Composing Mapped Hierarchies with Mixins
- Up: Home
- On this page:
Mapper Configuration with Declarative¶
The section Mapped Class Essential Components discusses the general
configurational elements of a Mapper
construct, which is the
structure that defines how a particular user defined class is mapped to a
database table or other SQL construct. The following sections describe
specific details about how the declarative system goes about constructing
the Mapper
.
Defining Mapped Properties with Declarative¶
The examples given at Table Configuration with Declarative
illustrate mappings against table-bound columns, using the mapped_column()
construct. There are several other varieties of ORM mapped constructs
that may be configured besides table-bound columns, the most common being the
relationship()
construct. Other kinds of properties include
SQL expressions that are defined using the column_property()
construct and multiple-column mappings using the composite()
construct.
While an imperative mapping makes use of
the properties dictionary to establish
all the mapped class attributes, in the declarative
mapping, these properties are all specified inline with the class definition,
which in the case of a declarative table mapping are inline with the
Column
objects that will be used to generate a
Table
object.
Working with the example mapping of User
and Address
, we may illustrate
a declarative table mapping that includes not just mapped_column()
objects but also relationships and SQL expressions:
from typing import List
from typing import Optional
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy.orm import column_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
firstname: Mapped[str] = mapped_column(String(50))
lastname: Mapped[str] = mapped_column(String(50))
fullname: Mapped[str] = column_property(firstname + " " + lastname)
addresses: Mapped[List["Address"]] = relationship(back_populates="user")
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
email_address: Mapped[str]
address_statistics: Mapped[Optional[str]] = mapped_column(Text, deferred=True)
user: Mapped["User"] = relationship(back_populates="addresses")
The above declarative table mapping features two tables, each with a
relationship()
referring to the other, as well as a simple
SQL expression mapped by column_property()
, and an additional
mapped_column()
that indicates loading should be on a
“deferred” basis as defined
by the mapped_column.deferred
keyword. More documentation
on these particular concepts may be found at Basic Relationship Patterns,
Using column_property, and Limiting which Columns Load with Column Deferral.
Properties may be specified with a declarative mapping as above using
“hybrid table” style as well; the Column
objects that
are directly part of a table move into the Table
definition
but everything else, including composed SQL expressions, would still be
inline with the class definition. Constructs that need to refer to a
Column
directly would reference it in terms of the
Table
object. To illustrate the above mapping using
hybrid table style:
# mapping attributes using declarative with imperative table
# i.e. __table__
from sqlalchemy import Column, ForeignKey, Integer, String, Table, Text
from sqlalchemy.orm import column_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import deferred
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
pass
class User(Base):
__table__ = Table(
"user",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("name", String),
Column("firstname", String(50)),
Column("lastname", String(50)),
)
fullname = column_property(__table__.c.firstname + " " + __table__.c.lastname)
addresses = relationship("Address", back_populates="user")
class Address(Base):
__table__ = Table(
"address",
Base.metadata,
Column("id", Integer, primary_key=True),
Column("user_id", ForeignKey("user.id")),
Column("email_address", String),
Column("address_statistics", Text),
)
address_statistics = deferred(__table__.c.address_statistics)
user = relationship("User", back_populates="addresses")
Things to note above:
The address
Table
contains a column calledaddress_statistics
, however we re-map this column under the same attribute name to be under the control of adeferred()
construct.With both declararative table and hybrid table mappings, when we define a
ForeignKey
construct, we always name the target table using the table name, and not the mapped class name.When we define
relationship()
constructs, as these constructs create a linkage between two mapped classes where one necessarily is defined before the other, we can refer to the remote class using its string name. This functionality also extends into the area of other arguments specified on therelationship()
such as the “primary join” and “order by” arguments. See the section Late-Evaluation of Relationship Arguments for details on this.
Mapper Configuration Options with Declarative¶
With all mapping forms, the mapping of the class is configured through
parameters that become part of the Mapper
object.
The function which ultimately receives these arguments is the
Mapper
function, and are delivered to it from one of
the front-facing mapping functions defined on the registry
object.
For the declarative form of mapping, mapper arguments are specified
using the __mapper_args__
declarative class variable, which is a dictionary
that is passed as keyword arguments to the Mapper
function.
Some examples:
Map Specific Primary Key Columns
The example below illustrates Declarative-level settings for the
Mapper.primary_key
parameter, which establishes
particular columns as part of what the ORM should consider to be a primary
key for the class, independently of schema-level primary key constraints:
class GroupUsers(Base):
__tablename__ = "group_users"
user_id = mapped_column(String(40))
group_id = mapped_column(String(40))
__mapper_args__ = {"primary_key": [user_id, group_id]}
See also
Mapping to an Explicit Set of Primary Key Columns - further background on ORM mapping of explicit columns as primary key columns
Version ID Column
The example below illustrates Declarative-level settings for the
Mapper.version_id_col
and
Mapper.version_id_generator
parameters, which configure
an ORM-maintained version counter that is updated and checked within the
unit of work flush process:
from datetime import datetime
class Widget(Base):
__tablename__ = "widgets"
id = mapped_column(Integer, primary_key=True)
timestamp = mapped_column(DateTime, nullable=False)
__mapper_args__ = {
"version_id_col": timestamp,
"version_id_generator": lambda v: datetime.now(),
}
See also
Configuring a Version Counter - background on the ORM version counter feature
Single Table Inheritance
The example below illustrates Declarative-level settings for the
Mapper.polymorphic_on
and
Mapper.polymorphic_identity
parameters, which are used when
configuring a single-table inheritance mapping:
class Person(Base):
__tablename__ = "person"
person_id = mapped_column(Integer, primary_key=True)
type = mapped_column(String, nullable=False)
__mapper_args__ = dict(
polymorphic_on=type,
polymorphic_identity="person",
)
class Employee(Person):
__mapper_args__ = dict(
polymorphic_identity="employee",
)
See also
Single Table Inheritance - background on the ORM single table inheritance mapping feature.
Constructing mapper arguments dynamically¶
The __mapper_args__
dictionary may be generated from a class-bound
descriptor method rather than from a fixed dictionary by making use of the
declared_attr()
construct. This is useful to create arguments
for mappers that are programmatically derived from the table configuration
or other aspects of the mapped class. A dynamic __mapper_args__
attribute will typically be useful when using a Declarative Mixin or
abstract base class.
For example, to omit from the mapping
any columns that have a special Column.info
value, a mixin
can use a __mapper_args__
method that scans for these columns from the
cls.__table__
attribute and passes them to the Mapper.exclude_properties
collection:
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
class ExcludeColsWFlag:
@declared_attr
def __mapper_args__(cls):
return {
"exclude_properties": [
column.key
for column in cls.__table__.c
if column.info.get("exclude", False)
]
}
class Base(DeclarativeBase):
pass
class SomeClass(ExcludeColsWFlag, Base):
__tablename__ = "some_table"
id = mapped_column(Integer, primary_key=True)
data = mapped_column(String)
not_needed = mapped_column(String, info={"exclude": True})
Above, the ExcludeColsWFlag
mixin provides a per-class __mapper_args__
hook that will scan for Column
objects that include the key/value
'exclude': True
passed to the Column.info
parameter, and then
add their string “key” name to the Mapper.exclude_properties
collection which will prevent the resulting Mapper
from considering
these columns for any SQL operations.
Other Declarative Mapping Directives¶
__declare_last__()
¶
The __declare_last__()
hook allows definition of
a class level function that is automatically called by the
MapperEvents.after_configured()
event, which occurs after mappings are
assumed to be completed and the ‘configure’ step has finished:
class MyClass(Base):
@classmethod
def __declare_last__(cls):
""" """
# do something with mappings
__declare_first__()
¶
Like __declare_last__()
, but is called at the beginning of mapper
configuration via the MapperEvents.before_configured()
event:
class MyClass(Base):
@classmethod
def __declare_first__(cls):
""" """
# do something before mappings are configured
metadata
¶
The MetaData
collection normally used to assign a new
Table
is the registry.metadata
attribute
associated with the registry
object in use. When using a
declarative base class such as that produced by the
DeclarativeBase
superclass, as well as legacy functions such as
declarative_base()
and registry.generate_base()
, this
MetaData
is also normally present as an attribute named
.metadata
that’s directly on the base class, and thus also on the mapped
class via inheritance. Declarative uses this attribute, when present, in order
to determine the target MetaData
collection, or if not
present, uses the MetaData
associated directly with the
registry
.
This attribute may also be assigned towards in order to affect the
MetaData
collection to be used on a per-mapped-hierarchy basis
for a single base and/or registry
. This takes effect whether a
declarative base class is used or if the registry.mapped()
decorator
is used directly, thus allowing patterns such as the metadata-per-abstract base
example in the next section, __abstract__. A similar pattern can
be illustrated using registry.mapped()
as follows:
reg = registry()
class BaseOne:
metadata = MetaData()
class BaseTwo:
metadata = MetaData()
@reg.mapped
class ClassOne:
__tablename__ = "t1" # will use reg.metadata
id = mapped_column(Integer, primary_key=True)
@reg.mapped
class ClassTwo(BaseOne):
__tablename__ = "t1" # will use BaseOne.metadata
id = mapped_column(Integer, primary_key=True)
@reg.mapped
class ClassThree(BaseTwo):
__tablename__ = "t1" # will use BaseTwo.metadata
id = mapped_column(Integer, primary_key=True)
See also
__abstract__
¶
__abstract__
causes declarative to skip the production
of a table or mapper for the class entirely. A class can be added within a
hierarchy in the same way as mixin (see Mixin and Custom Base Classes), allowing
subclasses to extend just from the special class:
class SomeAbstractBase(Base):
__abstract__ = True
def some_helpful_method(self):
""" """
@declared_attr
def __mapper_args__(cls):
return {"helpful mapper arguments": True}
class MyMappedClass(SomeAbstractBase):
pass
One possible use of __abstract__
is to use a distinct
MetaData
for different bases:
class Base(DeclarativeBase):
pass
class DefaultBase(Base):
__abstract__ = True
metadata = MetaData()
class OtherBase(Base):
__abstract__ = True
metadata = MetaData()
Above, classes which inherit from DefaultBase
will use one
MetaData
as the registry of tables, and those which inherit from
OtherBase
will use a different one. The tables themselves can then be
created perhaps within distinct databases:
DefaultBase.metadata.create_all(some_engine)
OtherBase.metadata.create_all(some_other_engine)
See also
Building Deeper Hierarchies with polymorphic_abstract - an alternative form of “abstract” mapped class that is appropriate for inheritance hierarchies.
__table_cls__
¶
Allows the callable / class used to generate a Table
to be customized.
This is a very open-ended hook that can allow special customizations
to a Table
that one generates here:
class MyMixin:
@classmethod
def __table_cls__(cls, name, metadata_obj, *arg, **kw):
return Table(f"my_{name}", metadata_obj, *arg, **kw)
The above mixin would cause all Table
objects generated to include
the prefix "my_"
, followed by the name normally specified using the
__tablename__
attribute.
__table_cls__
also supports the case of returning None
, which
causes the class to be considered as single-table inheritance vs. its subclass.
This may be useful in some customization schemes to determine that single-table
inheritance should take place based on the arguments for the table itself,
such as, define as single-inheritance if there is no primary key present:
class AutoTable:
@declared_attr
def __tablename__(cls):
return cls.__name__
@classmethod
def __table_cls__(cls, *arg, **kw):
for obj in arg[1:]:
if (isinstance(obj, Column) and obj.primary_key) or isinstance(
obj, PrimaryKeyConstraint
):
return Table(*arg, **kw)
return None
class Person(AutoTable, Base):
id = mapped_column(Integer, primary_key=True)
class Employee(Person):
employee_name = mapped_column(String)
The above Employee
class would be mapped as single-table inheritance
against Person
; the employee_name
column would be added as a member
of the Person
table.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Fri 08 Nov 2024 08:41:19 AM EST