SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Changing Attribute Behavior
- Next: Mapping Class Inheritance Hierarchies
- Up: Home
- On this page:
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.
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 vertices
, 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__ = 'vertices'
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, 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),
})
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)
BEGIN (implicit)
INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?)
(3, 4, 5, 6)
SELECT vertices.id AS vertices_id,
vertices.x1 AS vertices_x1,
vertices.y1 AS vertices_y1,
vertices.x2 AS vertices_x2,
vertices.y2 AS vertices_y2
FROM vertices
WHERE vertices.x1 = ? AND vertices.y1 = ?
LIMIT ? OFFSET ?
(3, 4, 1, 0)
Point(x=3, y=4)
Object Name | Description |
---|---|
composite(class_, *attrs, **kwargs) |
Return a composite column-based property for use with a Mapper. |
- function 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 bycomposite()
is theCompositeProperty
.- 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.
*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 oncolumn_property()
.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.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 since version 0.7:
AttributeExtension
is deprecated in favor of theAttributeEvents
listener interface. Thecomposite.extension
parameter will be removed in a future release.
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:
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__ = 'vertices'
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)
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.
Typically, it is convenient to define separate constructors for user-defined
use and generate-from-row use. Below we reorganize the Vertex
class to
itself be a composite object, which is then mapped to a class HasVertex
:
from sqlalchemy.orm import composite
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)
class Vertex(object):
def __init__(self, start, end):
self.start = start
self.end = end
@classmethod
def _generate(self, x1, y1, x2, y2):
"""generate a Vertex from a row"""
return Vertex(
Point(x1, y1),
Point(x2, y2)
)
def __composite_values__(self):
return \
self.start.__composite_values__() + \
self.end.__composite_values__()
class HasVertex(Base):
__tablename__ = 'has_vertex'
id = Column(Integer, primary_key=True)
x1 = Column(Integer)
y1 = Column(Integer)
x2 = Column(Integer)
y2 = Column(Integer)
vertex = composite(Vertex._generate, x1, y1, x2, y2)
We can then use the above mapping as:
hv = HasVertex(vertex=Vertex(Point(1, 2), Point(3, 4)))
s.add(hv)
s.commit()
hv = s.query(HasVertex).filter(
HasVertex.vertex == Vertex(Point(1, 2), Point(3, 4))).first()
print(hv.vertex.start)
print(hv.vertex.end)
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:10 PM