Composite Column Types

Sets of columns can be associated with a single user-defined datatype. The ORMprovides a single attribute which represents the group of columns using theclass you provide.

A simple example represents pairs of columns as a Point object.Point represents such a pair as .x and .y:

  1. class Point(object):
  2.     def __init__(self, x, y):
  3.         self.x = x
  4.         self.y = y
  5.  
  6.     def __composite_values__(self):
  7.         return self.x, self.y
  8.  
  9.     def __repr__(self):
  10.         return "Point(x=%r, y=%r)" % (self.x, self.y)
  11.  
  12.     def __eq__(self, other):
  13.         return isinstance(other, Point) and \
  14.             other.x == self.x and \
  15.             other.y == self.y
  16.  
  17.     def __ne__(self, other):
  18.         return not self.__eq__(other)

The requirements for the custom datatype class are that it have a constructorwhich accepts positional arguments corresponding to its column format, andalso provides a method composite_values() which returns the state ofthe object as a list or tuple, in order of its column-based attributes. Italso should supply adequate eq() and ne() methods which testthe equality of two instances.

We will create a mapping to a table vertices, which represents two pointsas x1/y1 and x2/y2. These are created normally as Columnobjects. Then, the composite() function is used to assign newattributes that will represent sets of columns via the Point class:

  1. from sqlalchemy import Column, Integer
  2. from sqlalchemy.orm import composite
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base()
  6.  
  7. class Vertex(Base):
  8.     __tablename__ = 'vertices'
  9.  
  10.     id = Column(Integer, primary_key=True)
  11.     x1 = Column(Integer)
  12.     y1 = Column(Integer)
  13.     x2 = Column(Integer)
  14.     y2 = Column(Integer)
  15.  
  16.     start = composite(Point, x1, y1)
  17.     end = composite(Point, x2, y2)

A classical mapping above would define each composite()against the existing table:

  1. mapper(Vertex, vertices_table, properties={
  2.     'start':composite(Point, vertices_table.c.x1, vertices_table.c.y1),
  3.     'end':composite(Point, vertices_table.c.x2, vertices_table.c.y2),
  4. })

We can now persist and use Vertex instances, as well as query for them,using the .start and .end attributes against ad-hoc Point instances:

  1. >>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
  2. >>> session.add(v)
  3. >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
  4. sql>>> print(q.first().start)
  5. BEGIN (implicit)
  6. INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?)
  7. (3, 4, 5, 6)
  8. SELECT vertices.id AS vertices_id,
  9.         vertices.x1 AS vertices_x1,
  10.         vertices.y1 AS vertices_y1,
  11.         vertices.x2 AS vertices_x2,
  12.         vertices.y2 AS vertices_y2
  13. FROM vertices
  14. WHERE vertices.x1 = ? AND vertices.y1 = ?
  15.  LIMIT ? OFFSET ?
  16. (3, 4, 1, 0)
  17. Point(x=3, y=4)
  • sqlalchemy.orm.composite(class__, attrs, *kwargs_)
  • Return a composite column-based property for use with a Mapper.

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

The MapperProperty returned by composite()is the CompositeProperty.

  • Parameters

    • class_ – The “composite type” class, or any classmethod or callable whichwill produce a new instance of the composite object given thecolumn values in order.

    • *cols – List of Column objects to be mapped.

    • active_history=False – When True, indicates that the “previous” value for ascalar attribute should be loaded when replaced, if notalready loaded. See the same flag on column_property().

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

    • deferred – When True, the column property is “deferred”, meaning that it doesnot load immediately, and is instead loaded when the attribute isfirst accessed on an instance. See alsodeferred().

    • comparator_factory – a class which extendsCompositeProperty.Comparator which provides custom SQLclause generation for comparison operations.

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

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

    • extension –

an AttributeExtension instance,or list of extensions, which will be prepended to the list ofattribute listeners for the resulting descriptor placed on theclass.

Deprecated since version 0.7: AttributeExtension is deprecated in favor of the AttributeEvents listener interface. The composite.extension parameter will be removed in a future release.

Tracking In-Place Mutations on Composites

In-place changes to an existing composite value arenot tracked automatically. Instead, the composite class needs to provideevents to its parent object explicitly. This task is largely automatedvia the usage of the MutableComposite mixin, which uses eventsto 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 allcorresponding columns equated to one another. This can be changed usingthe comparator_factory argument to composite(), where wespecify a custom CompositeProperty.Comparator classto define existing or new operations.Below we illustrate the “greater than” operator, implementingthe same expression that the base “greater than” does:

  1. from sqlalchemy.orm.properties import CompositeProperty
  2. from sqlalchemy import sql
  3.  
  4. class PointComparator(CompositeProperty.Comparator):
  5.     def __gt__(self, other):
  6.         """redefine the 'greater than' operation"""
  7.  
  8.         return sql.and_(*[a>b for a, b in
  9.                           zip(self.__clause_element__().clauses,
  10.                               other.__composite_values__())])
  11.  
  12. class Vertex(Base):
  13.     ___tablename__ = 'vertices'
  14.  
  15.     id = Column(Integer, primary_key=True)
  16.     x1 = Column(Integer)
  17.     y1 = Column(Integer)
  18.     x2 = Column(Integer)
  19.     y2 = Column(Integer)
  20.  
  21.     start = composite(Point, x1, y1,
  22.                         comparator_factory=PointComparator)
  23.     end = composite(Point, x2, y2,
  24.                         comparator_factory=PointComparator)

Nesting Composites

Composite objects can be defined to work in simple nested schemes, byredefining behaviors within the composite class to work as desired, thenmapping the composite class to the full length of individual columns normally.Typically, it is convenient to define separate constructors for user-defineduse and generate-from-row use. Below we reorganize the Vertex class toitself be a composite object, which is then mapped to a class HasVertex:

  1. from sqlalchemy.orm import composite
  2.  
  3. class Point(object):
  4.     def __init__(self, x, y):
  5.         self.x = x
  6.         self.y = y
  7.  
  8.     def __composite_values__(self):
  9.         return self.x, self.y
  10.  
  11.     def __repr__(self):
  12.         return "Point(x=%r, y=%r)" % (self.x, self.y)
  13.  
  14.     def __eq__(self, other):
  15.         return isinstance(other, Point) and \
  16.             other.x == self.x and \
  17.             other.y == self.y
  18.  
  19.     def __ne__(self, other):
  20.         return not self.__eq__(other)
  21.  
  22. class Vertex(object):
  23.     def __init__(self, start, end):
  24.         self.start = start
  25.         self.end = end
  26.  
  27.     @classmethod
  28.     def _generate(self, x1, y1, x2, y2):
  29.         """generate a Vertex from a row"""
  30.         return Vertex(
  31.             Point(x1, y1),
  32.             Point(x2, y2)
  33.         )
  34.  
  35.     def __composite_values__(self):
  36.         return \
  37.             self.start.__composite_values__() + \
  38.             self.end.__composite_values__()
  39.  
  40. class HasVertex(Base):
  41.     __tablename__ = 'has_vertex'
  42.     id = Column(Integer, primary_key=True)
  43.     x1 = Column(Integer)
  44.     y1 = Column(Integer)
  45.     x2 = Column(Integer)
  46.     y2 = Column(Integer)
  47.  
  48.     vertex = composite(Vertex._generate, x1, y1, x2, y2)

We can then use the above mapping as:

  1. hv = HasVertex(vertex=Vertex(Point(1, 2), Point(3, 4)))
  2.  
  3. s.add(hv)
  4. s.commit()
  5.  
  6. hv = s.query(HasVertex).filter(
  7.     HasVertex.vertex == Vertex(Point(1, 2), Point(3, 4))).first()
  8. print(hv.vertex.start)
  9. print(hv.vertex.end)