How to set up an user defined type in Sqlalchemy

Question

currently I am trying to retrieve data from a given database with Sqlalchemy. Until now, this worked fine for me. However, now I have to read out this table

create table DISPLAYOPTIONS (
  DOID integer constraint DO_PK primary key 
        USING INDEX (create index DO_PK_IX on DISPLAYOPTIONS(DOID) ),
  OPT dispopt
);

where dispopt is a custom type defined as

CREATE OR REPLACE TYPE DISPOPT AS OBJECT (
  LABEL_X varchar2(50),
  LABEL_Y varchar2(50),
  LABEL_Z varchar2(50)
);

How do you define custom types in Sqlalchemy? I found in the documentation http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#creating-new-types Is this the right starting point? Do you know some examples close to the problem I face?

Thank you very much and best regards,

---

What I have tried now: I found this on Stackoverflow: How do I make compound columns with SQLAlchemy declarative? linking to the documentation: http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#composite-column-types

and set up:

class DISPOPT(object):
    def __init__(self, LABEL_X, LABEL_Y, LABEL_Z):
        self.LABEL_X = LABEL_X
        self.LABEL_Y = LABEL_Y
        self.LABEL_Z = LABEL_Z 

    def __composite_values__(self):
        return self.LABEL_X, self.LABEL_Y, self.LABEL_Z

class DISPLAYOPTIONS(Base):
    __tablename__ = "DISPLAYOPTIONS"

    DOID = Column(Integer, primary_key = True)

    OPT = composite(
        DISPOPT,
        Column('LABEL_X', String(50)),
    Column('LABEL_Y', String(50)),
    Column('LABEL_Z', String(50)) 
)

But still I am receiving error messages from the database.

Answer 1

The solution is to follow

http://docs.sqlalchemy.org/en/rel_0_9/core/custom_types.html#creating-new-types

or the book "Essential SQLAlchemy" page 65 using the UserDefinedType class in sqlalchemy.