SQLAlchemy Custom Type Which Contains Multiple Columns

Question

I would like to represent a datatype as a single column in my model, but really the data will be stored in multiple columns in the database. I cannot find any good resources on how to do this in SQLAlchemy.

I would like my model to look like this(this is a simplified example using geometry instead of my real problem which is harder to explain):

class 3DLine(DeclarativeBase):
 start_point = Column(my.custom.3DPoint)
 end_point = Column(my.custom.3DPoint)

This way I could assign an object with the (x, y, z) components of the point at once without setting them individually. If I had to separate each component, this could get ugly, especially if each class has several of these composite objects. I would combine the values into one encoded field except that I need to query each value separately at times.

I was able to find out how to make custom types using a single column in the documentation. But there's no indication that I can map a single type to multiple columns.

I suppose I could accomplish this by using a separate table, and each column would be a foreign key, but in my case I don't think it makes sense to have a one to one mapping for each point to a separate table, and this still does not give the ability to set the related values all at once.

Answer 1

Here is a minimal example based on documentation:

class 3DPoint(object):
 def __init__(self, x, y, z):
 self.x = x
 self.y = y
 self.z = z
 def __composite_values__(self):
 return (self.x, self.y, self.z)
class 3DLine(DeclarativeBase):
 start_point = sqlalchemy.orm.composite(
 3DPoint,
 Column('start_point_x', Integer, nullable=False),
 Column('start_point_y', Integer, nullable=False),
 Column('start_point_z', Integer, nullable=False),
 )

• Veky: This is great for one-time use, however that approach makes it impossible to factor it out as a separate type, in cases like the above when there are two composite fields of the same type (start_point and end_point) - there will be duplication of column names. Is it possible to utilize the automatic inference of column names that works for single-column fields, and just append "_x", "_y" and "_z" to it? (Meta: should I ask a separate question on SO about this?)