How to set up a custom type in Sqlalchemy
I am currently trying to get data from a given database using Sqlalchemy. This has worked great for me so far. However, now I have to read 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 correct starting point? Do you know a few examples that are close to the problem I am facing?
Thank you very much and best wishes,
What I have tried now: I found this on Stackoverflow: How do I create composite columns with a SQLAlchemy declaration? link to documentation: http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#composite-column-types
and configure:
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 getting error messages from the database.
source to share
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 a class UserDefinedType
in sqlalchemy.
source to share