SQLAlchemy 2.1 Documentation
SQLAlchemy ORM
- ORM Quick Start
- ORM Mapped Class Configuration
- ORM Mapped Class Overview¶
- Mapping Classes with Declarative
- 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
ORM Mapped Class Overview¶
Overview of ORM class mapping configuration.
For readers new to the SQLAlchemy ORM and/or new to Python in general, it’s recommended to browse through the ORM Quick Start and preferably to work through the SQLAlchemy Unified Tutorial, where ORM configuration is first introduced at Using ORM Declarative Forms to Define Table Metadata.
ORM Mapping Styles¶
SQLAlchemy features two distinct styles of mapper configuration, which then feature further sub-options for how they are set up. The variability in mapper styles is present to suit a varied list of developer preferences, including the degree of abstraction of a user-defined class from how it is to be mapped to relational schema tables and columns, what kinds of class hierarchies are in use, including whether or not custom metaclass schemes are present, and finally if there are other class-instrumentation approaches present such as if Python dataclasses are in use simultaneously.
In modern SQLAlchemy, the difference between these styles is mostly
superficial; when a particular SQLAlchemy configurational style is used to
express the intent to map a class, the internal process of mapping the class
proceeds in mostly the same way for each, where the end result is always a
user-defined class that has a Mapper
configured against a
selectable unit, typically represented by a Table
object, and
the class itself has been instrumented to include behaviors linked to
relational operations both at the level of the class as well as on instances of
that class. As the process is basically the same in all cases, classes mapped
from different styles are always fully interoperable with each other.
The protocol MappedClassProtocol
can be used to indicate a mapped
class when using type checkers such as mypy.
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.
Regardless of what style of mapping used, all ORM mappings as of SQLAlchemy 1.4
originate from a single object known as registry
, which is a
registry of mapped classes. Using this registry, a set of mapper configurations
can be finalized as a group, and classes within a particular registry may refer
to each other by name within the configurational process.
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.
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 DeclarativeBase
superclass. The resulting base class,
when subclassed will apply the declarative mapping process to all subclasses
that derive from it, relative to a particular registry
that
is local to the new base by default. The example below illustrates
the use of a declarative base which is then used in a declarative table mapping:
from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
# declarative base class
class Base(DeclarativeBase):
pass
# an example mapping using the base
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
fullname: Mapped[str] = mapped_column(String(30))
nickname: Mapped[Optional[str]]
Above, the DeclarativeBase
class is used to generate a new
base class (within SQLAlchemy’s documentation it’s typically referred to
as Base
, however can have any desired name) from
which new classes to be mapped may inherit from, as above a new mapped
class User
is constructed.
Changed in version 2.0: The DeclarativeBase
superclass supersedes
the use of the declarative_base()
function and
registry.generate_base()
methods; the superclass approach
integrates with PEP 484 tools without the use of plugins.
See ORM Declarative Models for migration notes.
The base class refers to a registry
object that maintains a
collection of related mapped classes. as well as to a MetaData
object that retains a collection of Table
objects to which
the classes are mapped.
The major Declarative mapping styles are further detailed in the following sections:
Using a Declarative Base Class - declarative mapping using a base class.
Declarative Mapping using a Decorator (no declarative base) - declarative mapping using a decorator, rather than a base class.
Within the scope of a Declarative mapped class, there are also two varieties
of how the Table
metadata may be declared. These include:
Declarative Table with mapped_column() - table columns are declared inline within the mapped class using the
mapped_column()
directive (or in legacy form, using theColumn
object directly). Themapped_column()
directive may also be optionally combined with type annotations using theMapped
class which can provide some details about the mapped columns directly. The column directives, in combination with the__tablename__
and optional__table_args__
class level directives will allow the Declarative mapping process to construct aTable
object to be mapped.Declarative with Imperative Table (a.k.a. Hybrid Declarative) - Instead of specifying table name and attributes separately, an explicitly constructed
Table
object is associated with a class that is otherwise mapped declaratively. This style of mapping is a hybrid of “declarative” and “imperative” mapping, and applies to techniques such as mapping classes to reflectedTable
objects, as well as mapping classes to existing Core constructs such as joins and subqueries.
Documentation for Declarative mapping continues at Mapping Classes with Declarative.
Imperative Mapping¶
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.
Tip
The imperative mapping form is a lesser-used form of mapping that originates from the very first releases of SQLAlchemy in 2006. It’s essentially a means of bypassing the Declarative system to provide a more “barebones” system of mapping, and does not offer modern features such as PEP 484 support. As such, most documentation examples use Declarative forms, and it’s recommended that new users start with Declarative Table configuration.
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, after establishing
a registry
instance. Normally, a single instance of
registry
shared for all mapped classes that are related to each other:
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)
Note that classes which are mapped with the Imperative approach are fully
interchangeable with those mapped with the Declarative approach. 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.
Mapped Class Essential Components¶
With all mapping forms, the mapping of the class can be configured in many ways
by passing construction arguments that ultimately become part of the Mapper
object via its constructor. The parameters that are delivered to
Mapper
originate from the given mapping form, including
parameters passed to registry.map_imperatively()
for an Imperative
mapping, or when using the Declarative system, from a combination
of the table columns, SQL expressions and
relationships being mapped along with that of attributes such as
__mapper_args__.
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 with mapped_column() 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
parameter
to registry.map_imperatively()
, which will pass it along to the
Mapper.properties
parameter.
Other mapper configuration parameters¶
When mapping with the declarative mapping
style, additional mapper configuration arguments are configured via the
__mapper_args__
class attribute. Examples of use are available
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.
The full range of parameters accepted are documented at Mapper
.
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 DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
fullname: Mapped[str]
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")
Tip
The Declarative Dataclass Mapping feature provides an alternate
means of generating a default __init__()
method by using
Python dataclasses, and allows for a highly configurable constructor
form.
Warning
The __init__()
method of the class is called only when the object is
constructed in Python code, and not when an object is loaded or refreshed
from the database. See the next section Maintaining Non-Mapped State Across Loads
for a primer on how to invoke special logic when objects are loaded.
A class that includes an explicit __init__()
method will maintain
that method, and no default constructor will be applied.
To change the default constructor used, a user-defined Python callable may be
provided to the registry.constructor
parameter which will be
used as the default constructor.
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 Mapping,
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.
Maintaining Non-Mapped State Across Loads¶
The __init__()
method of the mapped class is invoked when the object
is constructed directly in Python code:
u1 = User(name="some name", fullname="some fullname")
However, when an object is loaded using the ORM Session
,
the __init__()
method is not called:
u1 = session.scalars(select(User).where(User.name == "some name")).first()
The reason for this is that when loaded from the database, the operation
used to construct the object, in the above example the User
, is more
analogous to deserialization, such as unpickling, rather than initial
construction. The majority of the object’s important state is not being
assembled for the first time, it’s being re-loaded from database rows.
Therefore to maintain state within the object that is not part of the data that’s stored to the database, such that this state is present when objects are loaded as well as constructed, there are two general approaches detailed below.
Use Python descriptors like
@property
, rather than state, to dynamically compute attributes as needed.For simple attributes, this is the simplest approach and the least error prone. For example if an object
Point
withPoint.x
andPoint.y
wanted an attribute with the sum of these attributes:class Point(Base): __tablename__ = "point" id: Mapped[int] = mapped_column(primary_key=True) x: Mapped[int] y: Mapped[int] @property def x_plus_y(self): return self.x + self.y
An advantage of using dynamic descriptors is that the value is computed every time, meaning it maintains the correct value as the underlying attributes (
x
andy
in this case) might change.Other forms of the above pattern include Python standard library cached_property decorator (which is cached, and not re-computed each time), as well as SQLAlchemy’s
hybrid_property
decorator which allows for attributes that can work for SQL querying as well.Establish state on-load using
InstanceEvents.load()
, and optionally supplemental methodsInstanceEvents.refresh()
andInstanceEvents.refresh_flush()
.These are event hooks that are invoked whenever the object is loaded from the database, or when it is refreshed after being expired. Typically only the
InstanceEvents.load()
is needed, since non-mapped local object state is not affected by expiration operations. To revise thePoint
example above looks like:from sqlalchemy import event class Point(Base): __tablename__ = "point" id: Mapped[int] = mapped_column(primary_key=True) x: Mapped[int] y: Mapped[int] def __init__(self, x, y, **kw): super().__init__(x=x, y=y, **kw) self.x_plus_y = x + y @event.listens_for(Point, "load") def receive_load(target, context): target.x_plus_y = target.x + target.y
If using the refresh events as well, the event hooks can be stacked on top of one callable if needed, as:
@event.listens_for(Point, "load") @event.listens_for(Point, "refresh") @event.listens_for(Point, "refresh_flush") def receive_load(target, context, attrs=None): target.x_plus_y = target.x + target.y
Above, the
attrs
attribute will be present for therefresh
andrefresh_flush
events and indicate a list of attribute names that are being refreshed.
Runtime Introspection of Mapped classes, Instances 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 theMapper
that is associated with the class:mapper = User.__mapper__
This
Mapper
is also what’s returned when using theinspect()
function against the mapped class:from sqlalchemy import inspect mapper = inspect(User)
The
__table__
attribute will refer to theTable
, or more generically to theFromClause
object, to which the class is mapped:table = User.__table__
This
FromClause
is also what’s returned when using theMapper.local_table
attribute of theMapper
: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 beNone
. To retrieve the “selectable” that is actually selected from during a query for this class, this is available via theMapper.selectable
attribute:table = inspect(User).selectable
Inspection of Mapper objects¶
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
Inspection of Mapped Instances¶
The inspect()
function also provides information about instances
of a mapped class. When applied to an instance of a mapped class, rather
than the class itself, the object returned is known as InstanceState
,
which will provide links to not only the Mapper
in use by the
class, but also a detailed interface that provides information on the state
of individual attributes within the instance including their current value
and how this relates to what their database-loaded value is.
Given an instance of the User
class loaded from the database:
>>> u1 = session.scalars(select(User)).first()
The inspect()
function will return to us an InstanceState
object:
>>> insp = inspect(u1)
>>> insp
<sqlalchemy.orm.state.InstanceState object at 0x7f07e5fec2e0>
With this object we can see elements such as the Mapper
:
>>> insp.mapper
<Mapper at 0x7f07e614ef50; User>
The Session
to which the object is attached, if any:
>>> insp.session
<sqlalchemy.orm.session.Session object at 0x7f07e614f160>
Information about the current persistence state for the object:
>>> insp.persistent
True
>>> insp.pending
False
Attribute state information such as attributes that have not been loaded or
lazy loaded (assume addresses
refers to a relationship()
on the mapped class to a related class):
>>> insp.unloaded
{'addresses'}
Information regarding the current in-Python status of attributes, such as attributes that have not been modified since the last flush:
>>> insp.unmodified
{'nickname', 'name', 'fullname', 'id'}
as well as specific history on modifications to attributes since the last flush:
>>> insp.attrs.nickname.value
'nickname'
>>> u1.nickname = "new nickname"
>>> insp.attrs.nickname.history
History(added=['new nickname'], unchanged=(), deleted=['nickname'])
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 09 Oct 2024 01:02:03 PM EDT