Release: 1.0.12 | Release Date: February 15, 2016

SQLAlchemy 1.0 Documentation

Composite Column Types

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

Changed in version 0.7: Composites have been simplified such that they no longer “conceal” the underlying column based attributes. Additionally, in-place mutation is no longer automatic; see the section below on enabling mutability to support tracking of in-place changes.

Changed in version 0.9: Composites will return their object-form, rather than as individual columns, when used in a column-oriented Query construct. See Composite attributes are now returned as their object form when queried on a per-attribute basis.

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

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def __composite_values__(self):
        return self.x, self.y

    def __repr__(self):
        return "Point(x=%r, y=%r)" % (self.x, self.y)

    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)

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.

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

from sqlalchemy import Column, Integer
from sqlalchemy.orm import composite
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Vertex(Base):
    __tablename__ = 'vertice'

    id = Column(Integer, primary_key=True)
    x1 = Column(Integer)
    y1 = Column(Integer)
    x2 = Column(Integer)
    y2 = Column(Integer)

    start = composite(Point, x1, y1)
    end = composite(Point, x2, y2)

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

mapper(Vertex, vertice_table, properties={
    'start':composite(Point, vertice_table.c.x1, vertice_table.c.y1),
    'end':composite(Point, vertice_table.c.x2, vertice_table.c.y2),
})

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:

>>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
>>> session.add(v)
>>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
sql>>> print q.first().start
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 a full usage example.

The MapperProperty returned by composite() is the CompositeProperty.

Parameters:
  • class_ – The “composite type” class.
  • *cols – List of Column objects to be mapped.
  • 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().

    Changed in version 0.7: This flag specifically becomes meaningful - previously it was a placeholder.

  • 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 CompositeProperty.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.

    New in version 0.8.

  • extension – an AttributeExtension instance, or list of extensions, which will be prepended to the list of attribute listeners for the resulting descriptor placed on the class. Deprecated. Please see AttributeEvents.

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.

Changed in version 0.7: In-place changes to an existing composite value are no longer tracked automatically; the functionality is superseded by the MutableComposite class.

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 CompositeProperty.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:

from sqlalchemy.orm.properties import CompositeProperty
from sqlalchemy import sql

class PointComparator(CompositeProperty.Comparator):
    def __gt__(self, other):
        """redefine the 'greater than' operation"""

        return sql.and_(*[a>b for a, b in
                          zip(self.__clause_element__().clauses,
                              other.__composite_values__())])

class Vertex(Base):
    ___tablename__ = 'vertice'

    id = Column(Integer, primary_key=True)
    x1 = Column(Integer)
    y1 = Column(Integer)
    x2 = Column(Integer)
    y2 = Column(Integer)

    start = composite(Point, x1, y1,
                        comparator_factory=PointComparator)
    end = composite(Point, x2, y2,
                        comparator_factory=PointComparator)