SQLAlchemy 1.4 Documentation
SQLAlchemy ORM
- ORM Quick Start
- Object Relational Tutorial (1.x API)
- ORM Mapped Class Configuration
- ORM Mapped Class Overview
- Mapping Classes with Declarative
- Integration with dataclasses and attrs¶
- Mapping Columns and Expressions
- Mapping Class Inheritance Hierarchies
- Non-Traditional Mappings
- Configuring a Version Counter
- Class Mapping API
- Relationship Configuration
- Querying Data, Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Integration with dataclasses and attrs¶
SQLAlchemy 1.4 has limited support for ORM mappings that are established against classes that have already been pre-instrumented using either Python’s built-in dataclasses library or the attrs third party integration library.
Tip
SQLAlchemy 2.0 will include a new dataclass integration feature which
allows for a particular class to be mapped and converted into a Python
dataclass simultaneously, with full support for SQLAlchemy’s declarative
syntax. Within the scope of the 1.4 release, the @dataclass
decorator
is used separately as documented in this section.
Applying ORM Mappings to an existing dataclass¶
The dataclasses module, added in Python 3.7, provides a @dataclass
class
decorator to automatically generate boilerplate definitions of common object
methods including __init__()
, __repr()__
, and other methods. SQLAlchemy
supports the application of ORM mappings to a class after it has been processed
with the @dataclass
decorator, by using either the
registry.mapped()
class decorator, or the
registry.map_imperatively()
method to apply ORM mappings to the
class using Imperative.
New in version 1.4: Added support for direct mapping of Python dataclasses
To map an existing dataclass, SQLAlchemy’s “inline” declarative directives cannot be used directly; ORM directives are assigned using one of three techniques:
Using “Declarative with Imperative Table”, the table / column to be mapped is defined using a
Table
object assigned to the__table__
attribute of the class; relationships are defined within__mapper_args__
dictionary. The class is mapped using theregistry.mapped()
decorator. An example is below at Mapping dataclasses using Declarative With Imperative Table.Using full “Declarative”, the Declarative-interpreted directives such as
Column
,relationship()
are added to the.metadata
dictionary of thedataclasses.field()
construct, where they are consumed by the declarative process. The class is again mapped using theregistry.mapped()
decorator. See the example below at Mapping dataclasses using Declarative Mapping.An “Imperative” mapping can be applied to an existing dataclass using the
registry.map_imperatively()
method to produce the mapping in exactly the same way as described at Imperative Mapping. This is illustrated below at Mapping dataclasses using Imperative Mapping.
The general process by which SQLAlchemy applies mappings to a dataclass
is the same as that of an ordinary class, but also includes that
SQLAlchemy will detect class-level attributes that were part of the
dataclasses declaration process and replace them at runtime with
the usual SQLAlchemy ORM mapped attributes. The __init__
method that
would have been generated by dataclasses is left intact, as is the same
for all the other methods that dataclasses generates such as
__eq__()
, __repr__()
, etc.
Mapping dataclasses using Declarative With Imperative Table¶
An example of a mapping using @dataclass
using
Declarative with Imperative Table (a.k.a. Hybrid Declarative) is below. A complete
Table
object is constructed explicitly and assigned to the
__table__
attribute. Instance fields are defined using normal dataclass
syntaxes. Additional MapperProperty
definitions such as relationship()
, are placed in the
__mapper_args__ class-level
dictionary underneath the properties
key, corresponding to the
mapper.properties
parameter:
from __future__ import annotations
from dataclasses import dataclass, field
from typing import List, Optional
from sqlalchemy import Column, ForeignKey, Integer, String, Table
from sqlalchemy.orm import registry, relationship
mapper_registry = registry()
@mapper_registry.mapped
@dataclass
class 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)),
)
id: int = field(init=False)
name: Optional[str] = None
fullname: Optional[str] = None
nickname: Optional[str] = None
addresses: List[Address] = field(default_factory=list)
__mapper_args__ = { # type: ignore
"properties": {
"addresses": relationship("Address"),
}
}
@mapper_registry.mapped
@dataclass
class Address:
__table__ = Table(
"address",
mapper_registry.metadata,
Column("id", Integer, primary_key=True),
Column("user_id", Integer, ForeignKey("user.id")),
Column("email_address", String(50)),
)
id: int = field(init=False)
user_id: int = field(init=False)
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
Session
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 mapper.properties
dictionary
which itself is specified within the __mapper_args__
dictionary, so that it
is passed to the mapper()
construction function. An alternative to this
approach is in the next example.
Mapping dataclasses using Declarative Mapping¶
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 relationship()
association:
from __future__ import annotations
from dataclasses import dataclass, field
from typing import List
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import registry, relationship
mapper_registry = registry()
@mapper_registry.mapped
@dataclass
class User:
__tablename__ = "user"
__sa_dataclass_metadata_key__ = "sa"
id: int = field(init=False, metadata={"sa": Column(Integer, primary_key=True)})
name: str = field(default=None, metadata={"sa": Column(String(50))})
fullname: str = field(default=None, metadata={"sa": Column(String(50))})
nickname: str = field(default=None, metadata={"sa": Column(String(12))})
addresses: List[Address] = field(
default_factory=list, metadata={"sa": relationship("Address")}
)
@mapper_registry.mapped
@dataclass
class Address:
__tablename__ = "address"
__sa_dataclass_metadata_key__ = "sa"
id: int = field(init=False, metadata={"sa": Column(Integer, primary_key=True)})
user_id: int = field(init=False, metadata={"sa": Column(ForeignKey("user.id"))})
email_address: str = field(default=None, metadata={"sa": Column(String(50))})
Mapping dataclasses using Imperative Mapping¶
As described previously, a class which is set up as a dataclass using the
@dataclass
decorator can then be further decorated using the
registry.mapped()
decorator in order to apply declarative-style
mapping to the class. As an alternative to using the
registry.mapped()
decorator, we may also pass the class through the
registry.map_imperatively()
method instead, so that we may pass all
Table
and mapper()
configuration imperatively to
the function rather than having them defined on the class itself as 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)
Using Declarative Mixins with Dataclasses¶
In the section Composing Mapped Hierarchies with Mixins, Declarative Mixin classes
are introduced. One requirement of declarative mixins is that certain
constructs that can’t be easily duplicated must be given as callables,
using the declared_attr
decorator, such as in the
example at Mixing in Relationships:
class RefTargetMixin:
@declared_attr
def target_id(cls):
return Column("target_id", ForeignKey("target.id"))
@declared_attr
def target(cls):
return relationship("Target")
This form is supported within the Dataclasses field()
object by using
a lambda to indicate the SQLAlchemy construct inside the field()
.
Using declared_attr()
to surround the lambda is optional.
If we wanted to produce our User
class above where the ORM fields
came from a mixin that is itself a dataclass, the form would be:
@dataclass
class UserMixin:
__tablename__ = "user"
__sa_dataclass_metadata_key__ = "sa"
id: int = field(init=False, metadata={"sa": Column(Integer, primary_key=True)})
addresses: List[Address] = field(
default_factory=list, metadata={"sa": lambda: relationship("Address")}
)
@dataclass
class AddressMixin:
__tablename__ = "address"
__sa_dataclass_metadata_key__ = "sa"
id: int = field(init=False, metadata={"sa": Column(Integer, primary_key=True)})
user_id: int = field(
init=False, metadata={"sa": lambda: Column(ForeignKey("user.id"))}
)
email_address: str = field(default=None, metadata={"sa": Column(String(50))})
@mapper_registry.mapped
class User(UserMixin):
pass
@mapper_registry.mapped
class Address(AddressMixin):
pass
New in version 1.4.2: Added support for “declared attr” style mixin attributes,
namely relationship()
constructs as well as Column
objects with foreign key declarations, to be used within “Dataclasses
with Declarative Table” style mappings.
Applying ORM mappings to an existing attrs class¶
The attrs library is a popular third party library that provides similar features as dataclasses, with many additional features provided not found in ordinary dataclasses.
A class augmented with attrs uses the @define
decorator. This decorator
initiates a process to scan the class for attributes that define the class’
behavior, which are then used to generate methods, documentation, and
annotations.
The SQLAlchemy ORM supports mapping an attrs class using Declarative with Imperative Table or Imperative mapping. The general form of these two styles is fully equivalent to the Mapping dataclasses using Declarative Mapping and Mapping dataclasses using Declarative With Imperative Table mapping forms used with dataclasses, where the inline attribute directives used by dataclasses or attrs are unchanged, and SQLAlchemy’s table-oriented instrumentation is applied at runtime.
The @define
decorator of attrs by default replaces the annotated class
with a new __slots__ based class, which is not supported. When using the old
style annotation @attr.s
or using define(slots=False)
, the class
does not get replaced. Furthermore attrs removes its own class-bound attributes
after the decorator runs, so that SQLAlchemy’s mapping process takes over these
attributes without any issue. Both decorators, @attr.s
and @define(slots=False)
work with SQLAlchemy.
Mapping attrs with Declarative “Imperative Table”¶
In the “Declarative with Imperative Table” style, a Table
object is declared inline with the declarative class. The
@define
decorator is applied to the class first, then the
registry.mapped()
decorator second:
from __future__ import annotations
from typing import List
from attrs import define
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()
@mapper_registry.mapped
@define(slots=False)
class 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)),
)
id: int
name: str
fullname: str
nickname: str
addresses: List[Address]
__mapper_args__ = { # type: ignore
"properties": {
"addresses": relationship("Address"),
}
}
@mapper_registry.mapped
@define(slots=False)
class Address:
__table__ = Table(
"address",
mapper_registry.metadata,
Column("id", Integer, primary_key=True),
Column("user_id", Integer, ForeignKey("user.id")),
Column("email_address", String(50)),
)
id: int
user_id: int
email_address: Optional[str]
Note
The attrs
slots=True
option, which enables __slots__
on
a mapped class, cannot be used with SQLAlchemy mappings without fully
implementing alternative
attribute instrumentation, as mapped
classes normally rely upon direct access to __dict__
for state storage.
Behavior is undefined when this option is present.
Mapping attrs with Imperative Mapping¶
Just as is the case with dataclasses, we can make use of
registry.map_imperatively()
to map an existing attrs
class
as well:
from __future__ import annotations
from typing import List
from attrs import define
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()
@define(slots=False)
class User:
id: int
name: str
fullname: str
nickname: str
addresses: List[Address]
@define(slots=False)
class Address:
id: int
user_id: int
email_address: Optional[str]
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)
The above form is equivalent to the previous example using Declarative with Imperative 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: Wed 30 Oct 2024 02:18:58 PM EDT