Working with Mapped Composite Column Types¶

With a mapping as illustrated in the top section, we can work with the Vertex class, where the .start and .end attributes will transparently refer to the columns referenced by the Point class, as well as with instances of the Vertex class, where the .start and .end attributes will refer to instances of the Point class. The x1, y1, x2, and y2 columns are handled transparently:

• Persisting Point objects

  We can create a Vertex object, assign Point objects as members, and they will be persisted as expected:

  >>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
  >>> session.add(v)
  >>> session.commit()
  BEGIN(implicit)
  INSERTINTOvertices(x1,y1,x2,y2)VALUES(?,?,?,?)
  [generatedin...](3,4,5,6)
  COMMIT
  

• Selecting Point objects as columns

  composite() will allow the Vertex.start and Vertex.end attributes to behave like a single SQL expression to as much an extent as possible when using the ORM Session (including the legacy Query object) to select Point objects:

  >>> stmt = select(Vertex.start, Vertex.end)
  >>> session.execute(stmt).all()
  SELECTvertices.x1,vertices.y1,vertices.x2,vertices.y2
  FROMvertices
  [...]()
  [(Point(x=3, y=4), Point(x=5, y=6))]

• Comparing Point objects in SQL expressions

  The Vertex.start and Vertex.end attributes may be used in WHERE criteria and similar, using ad-hoc Point objects for comparisons:

  >>> stmt = select(Vertex).where(Vertex.start == Point(3, 4)).where(Vertex.end < Point(7, 8))
  >>> session.scalars(stmt).all()
  SELECTvertices.id,vertices.x1,vertices.y1,vertices.x2,vertices.y2
  FROMvertices
  WHEREvertices.x1=?ANDvertices.y1=?ANDvertices.x2<?ANDvertices.y2<?
  [...](3,4,7,8)
  [Vertex(Point(x=3, y=4), Point(x=5, y=6))]

  Added in version 2.0: composite() constructs now support "ordering" comparisons such as <, >=, and similar, in addition to the already-present support for ==, !=.

  Tip

  The "ordering" comparison above using the "less than" operator (<) as well as the "equality" comparison using ==, when used to generate SQL expressions, are implemented by the Comparator class, and don’t make use of the comparison methods on the composite class itself, e.g. the __lt__() or __eq__() methods. From this it follows that the Point dataclass above also need not implement the dataclasses order=True parameter for the above SQL operations to work. The section Redefining Comparison Operations for Composites contains background on how to customize the comparison operations.

• Updating Point objects on Vertex Instances

  By default, the Point object must be replaced by a new object for changes to be detected:

  >>> v1 = session.scalars(select(Vertex)).one()
  SELECTvertices.id,vertices.x1,vertices.y1,vertices.x2,vertices.y2
  FROMvertices
  [...]()
  >>> v1.end = Point(x=10, y=14)
  >>> session.commit()
  UPDATEverticesSETx2=?,y2=?WHEREvertices.id=?
  [...](10,14,1)
  COMMIT
  

  In order to allow in place changes on the composite object, the Mutation Tracking extension must be used. See the section Establishing Mutability on Composites for examples.

Returning None for a Composite¶

The composite attribute by default always returns an object when accessed, regardless of the values of its columns. In the example below, a new Vertex is created with no parameters; all column attributes x1, y1, x2, and y2 start out as None. A Point object with None values will be returned on access:

>>> v1 = Vertex()
>>> v1.start
Point(x=None, y=None)
>>> v1.end
Point(x=None, y=None)

This behavior is consistent with persistent objects and individual attribute queries as well:

>>> start = session.scalars(
...  select(Point.start).where(Point.x1 == None, Point.y1 == None)
... ).first()
>>> start
Point(x=None, y=None)

To support an optional Point field, we can make use of the composite.return_none_on parameter, which allows the behavior to be customized with a lambda; this parameter is set automatically if we declare our composite fields as optional:

classVertex(Base):
 __tablename__ = "vertices"
 id: Mapped[int] = mapped_column(primary_key=True)
 start: Mapped[Point | None] = composite(mapped_column("x1"), mapped_column("y1"))
 end: Mapped[Point | None] = composite(mapped_column("x2"), mapped_column("y2"))

Above, the composite.return_none_on parameter is set equivalently as:

composite(
 mapped_column("x1"),
 mapped_column("y1"),
 return_none_on=lambda *args: all(arg is None for arg in args),
)

With the above setting, a value of None is returned if the columns themselves are both None:

>>> v1 = Vertex()
>>> v1.start
None
>>> start = session.scalars(
...  select(Point.start).where(Point.x1 == None, Point.y1 == None)
... ).first()
>>> start
None

Other mapping forms for composites¶

The composite() construct may be passed the relevant columns using a mapped_column() construct, a Column, or the string name of an existing mapped column. The following examples illustrate an equivalent mapping as that of the main section above.

fromsqlalchemyimport Integer
fromsqlalchemy.ormimport mapped_column, composite
classVertex(Base):
 __tablename__ = "vertices"
 id = mapped_column(Integer, primary_key=True)
 x1 = mapped_column(Integer)
 y1 = mapped_column(Integer)
 x2 = mapped_column(Integer)
 y2 = mapped_column(Integer)
 start = composite(Point, x1, y1)
 end = composite(Point, x2, y2)

fromsqlalchemy.ormimport mapped_column, composite, Mapped
classVertex(Base):
 __tablename__ = "vertices"
 id: Mapped[int] = mapped_column(primary_key=True)
 x1: Mapped[int]
 y1: Mapped[int]
 x2: Mapped[int]
 y2: Mapped[int]
 start: Mapped[Point] = composite("x1", "y1")
 end: Mapped[Point] = composite("x2", "y2")

mapper_registry.map_imperatively(
 Vertex,
 vertices_table,
 properties={
 "start": composite(Point, vertices_table.c.x1, vertices_table.c.y1),
 "end": composite(Point, vertices_table.c.x2, vertices_table.c.y2),
 },
)

Using Legacy Non-Dataclasses¶

If not using a dataclass, the requirements for the custom datatype class are that it have a constructor which accepts positional arguments corresponding to its column format, and also provides a method __composite_values__() which returns the state of the object as a list or tuple, in order of its column-based attributes. It also should supply adequate __eq__() and __ne__() methods which test the equality of two instances.

To illustrate the equivalent Point class from the main section not using a dataclass:

classPoint:
 def__init__(self, x, y):
 self.x = x
 self.y = y
 def__composite_values__(self):
 return self.x, self.y
 def__repr__(self):
 return f"Point(x={self.x!r}, y={self.y!r})"
 def__eq__(self, other):
 return isinstance(other, Point) and other.x == self.x and other.y == self.y
 def__ne__(self, other):
 return not self.__eq__(other)

Usage with composite() then proceeds where the columns to be associated with the Point class must also be declared with explicit types, using one of the forms at Other mapping forms for composites.

Tracking In-Place Mutations on Composites¶

In-place changes to an existing composite value are not tracked automatically. Instead, the composite class needs to provide events to its parent object explicitly. This task is largely automated via the usage of the MutableComposite mixin, which uses events to associate each user-defined composite object with all parent associations. Please see the example in Establishing Mutability on Composites.

Redefining Comparison Operations for Composites¶

The "equals" comparison operation by default produces an AND of all corresponding columns equated to one another. This can be changed using the comparator_factory argument to composite(), where we specify a custom Comparator class to define existing or new operations. Below we illustrate the "greater than" operator, implementing the same expression that the base "greater than" does:

importdataclasses
fromsqlalchemy.ormimport composite
fromsqlalchemy.ormimport CompositeProperty
fromsqlalchemy.ormimport DeclarativeBase
fromsqlalchemy.ormimport Mapped
fromsqlalchemy.ormimport mapped_column
fromsqlalchemy.sqlimport and_
@dataclasses.dataclass
classPoint:
 x: int
 y: int
classPointComparator(CompositeProperty.Comparator):
 def__gt__(self, other):
"""redefine the 'greater than' operation"""
 return and_(
 *[
 a > b
 for a, b in zip(
 self.__clause_element__().clauses,
 dataclasses.astuple(other),
 )
 ]
 )
classBase(DeclarativeBase):
 pass
classVertex(Base):
 __tablename__ = "vertices"
 id: Mapped[int] = mapped_column(primary_key=True)
 start: Mapped[Point] = composite(
 mapped_column("x1"), mapped_column("y1"), comparator_factory=PointComparator
 )
 end: Mapped[Point] = composite(
 mapped_column("x2"), mapped_column("y2"), comparator_factory=PointComparator
 )

Since Point is a dataclass, we may make use of dataclasses.astuple() to get a tuple form of Point instances.

The custom comparator then returns the appropriate SQL expression:

>>> print(Vertex.start > Point(5, 6))
vertices.x1>:x1_1ANDvertices.y1>:y1_1

Nesting Composites¶

Composite objects can be defined to work in simple nested schemes, by redefining behaviors within the composite class to work as desired, then mapping the composite class to the full length of individual columns normally. This requires that additional methods to move between the "nested" and "flat" forms are defined.

Below we reorganize the Vertex class to itself be a composite object which refers to Point objects. Vertex and Point can be dataclasses, however we will add a custom construction method to Vertex that can be used to create new Vertex objects given four column values, which will will arbitrarily name _generate() and define as a classmethod so that we can make new Vertex objects by passing values to the Vertex._generate() method.

We will also implement the __composite_values__() method, which is a fixed name recognized by the composite() construct (introduced previously at Using Legacy Non-Dataclasses) that indicates a standard way of receiving the object as a flat tuple of column values, which in this case will supersede the usual dataclass-oriented methodology.

With our custom _generate() constructor and __composite_values__() serializer method, we can now move between a flat tuple of columns and Vertex objects that contain Point instances. The Vertex._generate method is passed as the first argument to the composite() construct as the source of new Vertex instances, and the __composite_values__() method will be used implicitly by composite().

For the purposes of the example, the Vertex composite is then mapped to a class called HasVertex, which is where the Table containing the four source columns ultimately resides:

from__future__import annotations
importdataclasses
fromtypingimport Any
fromtypingimport Tuple
fromsqlalchemy.ormimport composite
fromsqlalchemy.ormimport DeclarativeBase
fromsqlalchemy.ormimport Mapped
fromsqlalchemy.ormimport mapped_column
@dataclasses.dataclass
classPoint:
 x: int
 y: int
@dataclasses.dataclass
classVertex:
 start: Point
 end: Point
 @classmethod
 def_generate(cls, x1: int, y1: int, x2: int, y2: int) -> Vertex:
"""generate a Vertex from a row"""
 return Vertex(Point(x1, y1), Point(x2, y2))
 def__composite_values__(self) -> Tuple[Any, ...]:
"""generate a row from a Vertex"""
 return dataclasses.astuple(self.start) + dataclasses.astuple(self.end)
classBase(DeclarativeBase):
 pass
classHasVertex(Base):
 __tablename__ = "has_vertex"
 id: Mapped[int] = mapped_column(primary_key=True)
 x1: Mapped[int]
 y1: Mapped[int]
 x2: Mapped[int]
 y2: Mapped[int]
 vertex: Mapped[Vertex] = composite(Vertex._generate, "x1", "y1", "x2", "y2")

The above mapping can then be used in terms of HasVertex, Vertex, and Point:

hv = HasVertex(vertex=Vertex(Point(1, 2), Point(3, 4)))
session.add(hv)
session.commit()
stmt = select(HasVertex).where(HasVertex.vertex == Vertex(Point(1, 2), Point(3, 4)))
hv = session.scalars(stmt).first()
print(hv.vertex.start)
print(hv.vertex.end)

Composite API¶

function sqlalchemy.orm.composite(_class_or_attr:None|Type[_CC]|Callable[...,_CC]|_CompositeAttrType[Any]=None, /, *attrs:_CompositeAttrType[Any], group:str|None=None, deferred:bool=False, raiseload:bool=False, return_none_on:_NoArg|None|Callable[...,bool]=_NoArg.NO_ARG, comparator_factory:Type[Composite.Comparator[_T]]|None=None, active_history:bool=False, init:_NoArg|bool=_NoArg.NO_ARG, repr:_NoArg|bool=_NoArg.NO_ARG, default:Any|None=_NoArg.NO_ARG, default_factory:_NoArg|Callable[[],_T]=_NoArg.NO_ARG, compare:_NoArg|bool=_NoArg.NO_ARG, kw_only:_NoArg|bool=_NoArg.NO_ARG, hash:_NoArg|bool|None=_NoArg.NO_ARG, info:_InfoType|None=None, doc:str|None=None, dataclass_metadata:_NoArg|Mapping[Any,Any]|None=_NoArg.NO_ARG, **__kw:Any) → Composite [Any]¶

Return a composite column-based property for use with a Mapper.

See the mapping documentation section Composite Column Types for a full usage example.

The MapperProperty returned by composite() is the Composite.

Parameters:

• class_¶ – The "composite type" class, or any classmethod or callable which will produce a new instance of the composite object given the column values in order.

• *attrs¶ –

  List of elements to be mapped, which may include:

  • Column objects

  • mapped_column() constructs

  • string names of other attributes on the mapped class, which may be any other SQL or object-mapped attribute. This can for example allow a composite that refers to a many-to-one relationship

• active_history=False¶ – When True, indicates that the "previous" value for a scalar attribute should be loaded when replaced, if not already loaded. See the same flag on column_property().

• return_none_on=None¶ –

  A callable that will be evaluated when the composite object is to be constructed, which upon returning the boolean value True will instead bypass the construction and cause the resulting value to be None. This typically may be assigned a lambda that will evaluate to True when all the columns within the composite are themselves None, e.g.:

  composite(
   MyComposite, return_none_on=lambda *cols: all(x is None for x in cols)
  )

  The above lambda for composite.return_none_on is used automatically when using ORM Annotated Declarative along with an optional value within the Mapped annotation.

  Added in version 2.1.

• group¶ – A group name for this property when marked as deferred.

• deferred¶ – When True, the column property is "deferred", meaning that it does not load immediately, and is instead loaded when the attribute is first accessed on an instance. See also deferred().

• comparator_factory¶ – a class which extends Comparator which provides custom SQL clause generation for comparison operations.

• doc¶ – optional string that will be applied as the doc on the class-bound descriptor.

• info¶ – Optional data dictionary which will be populated into the MapperProperty.info attribute of this object.

• init¶ – Specific to Declarative Dataclass Mapping, specifies if the mapped attribute should be part of the __init__() method as generated by the dataclass process.

• repr¶ – Specific to Declarative Dataclass Mapping, specifies if the mapped attribute should be part of the __repr__() method as generated by the dataclass process.

• default_factory¶ – Specific to Declarative Dataclass Mapping, specifies a default-value generation function that will take place as part of the __init__() method as generated by the dataclass process.

• compare¶ –

  Specific to Declarative Dataclass Mapping, indicates if this field should be included in comparison operations when generating the __eq__() and __ne__() methods for the mapped class.

  Added in version 2.0.0b4.

• kw_only¶ – Specific to Declarative Dataclass Mapping, indicates if this field should be marked as keyword-only when generating the __init__().

• hash¶ –

  Specific to Declarative Dataclass Mapping, controls if this field is included when generating the __hash__() method for the mapped class.

  Added in version 2.0.36.

• dataclass_metadata¶ –

  Specific to Declarative Dataclass Mapping, supplies metadata to be attached to the generated dataclass field.

  Added in version 2.0.42.