sqlalchemy - Define a relationship() that is only true sometimes -

i'm working database schema has relationship isn't true, , i'm not sure how describe sqlalchemy's orm.

all primary keys in database stored blob type, , 16 byte binary strings.

i have table called attribute, , table has column called data_type. there number of built in data_types, not defined explicitly in database. so, maybe data_type of 00 means string, , 01 means float, etc (those hex values). highest value built in data types 12 (18 in decimal).

however, rows in attribute, value of attribute stored in row must exist in pre-defined list of values. in case, data_type referrs lookup.lookup_id. actual data type attribute can retrieved lookup.data_type.

i'd able call attribue.data_type , 'string' or 'number'. i'd need define {0x00: 'string', 0x01: 'number'} mapping somewhere, how can tell sqlalchemy want lookup.data_type if value of attribute.data_type greater 18?

there couple of ways this.

1. the simplest, far, put predefined data types table lookup. "need define the... mapping somewhere", , table place any.

2. assuming can't that, next simplest thing create python property on class attribute. problem can't query against it. you'll want reassign column data_type maps _data_type:

   data_type_dict = {0x00: 'string',                   0x01: 'number,                   ...}  class attribute(base):      __tablename__ = 'attribute'       _data_type = column('data_type')      ...      @property     def data_type(self):         dt = data_type_dict.get(self._data_type, none)         if dt none:             s = session.object_session(self)             lookup = s.query(lookup).filter_by(id=self._data_type).one()             dt = lookup.data_type         return dt 

3. if want queryable, is, if want able session.query(attribute).filter_by(data_type='string'), need map data_type database can handle, i.e., sql statement. in raw sql case expression:

   from sqlalchemy.sql.expression import select, case  class attribute(base):      ...      data_type = column_property(select([attribute, lookup])\                                 .where(attribute.data_type==lookup.lookup_id)\                                 .where(case([(attribute.data_type==0x00, 'string'),                                              (attribute.data_type==0x01, 'number'),                                              ...],                                             else_=lookup.data_type)) 

   i'm not 100% last part work; may need explicitly join tables attribute , lookup specify it's outer join, though think sqlalchemy default. downside of approach going try join table lookup, though query using sql, sort of have that.

4. the final option use polymorphism, , map 2 cases (data_type greater/less 18) 2 different subclasses:

   class attribute(base):     __tablename__ = 'attribute'      _data_type = column('data_type')     _lookup = column_property(attribute.data_type > 18)     __mapper_args__ = {'polymorphic_on': _lookup}  class fixedattribute(attribute):      __mapper_args__ = {'polymorphic_identity': 0}      data_type = column_property(select([attribute.data_type])\                                 .where(case([(attribute.data_type==0x00, 'string'),                                              (attribute.data_type==0x01, 'number'),                                              ...])))  class lookupattribute(attribute):      __mapper_args__ = {'polymorphic_identity': 1}      data_type = column_property(select([lookup.data_type],                                    whereclause=attribute.data_type==lookup.lookup_id)) 

   you might have replace 'polymorphic_on': _lookup explicit attribute.data_type > 18, depending on when columnproperty gets bound.

as can see, these messy. #1 if it's @ possible.