python - SQL Alchemy 中的外键和继承

Question

我在 SQLAlchemy 中使用外键引用进行继承时遇到了困难。

我有一个drives表格看起来像这样 id作为主键:

   Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+------------------------------------
 id            | integer               |           | not null | nextval('drives_id_seq'::regclass)
 model         | integer               |           | not null |

我还有另一个表，名为 smart看起来像这样 <ts, drive>作为主键和 drive是一个外键引用 drives.id :

   Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+------------------------------------
 drive         | integer                    |           | not null | nextval('drives_id_seq'::regclass)
 ts            | timestamp without timezone |           | not null |
 value         | integer                    |           |          |

我有以下类定义来表示上面的表。

class Drives(Base):
    __tablename__ = 'drives'
    id = Column('id', Integer, primary_key=True)
    model = Column('model', String)

class Smart(Base):
   ___tablename__ = 'smart'
   drive = Column('drive', Integer, ForeignKey=Drives.id)
   ts = Column('ts', TIMESTAMP)
   value = Column('value', Integer)
   drives = relationship('Drives')
   # I would like something like the following to work, but I get an AttributeError for `model`
   __mapper_args__ = {'primary_key': [ts, drive], 'polymorphic_on': drives.model} 

我想创建两个派生类 ModelASmart或ModelBSmart哪里smart.value根据 drive 对应的模型，有不同的解释.

class ModelASmart(Smart):
    __mapper_args__ = {'polymorphic_identity': 'ModelA', 'primary_key': [Smart.ts, Smart.drive]}
    @hybrid_property
    def actual_value(self):
        return self.value * 2

class ModelBSmart(Smart):
    __mapper_args__ = {'polymorphic_identity': 'ModelB', 'primary_key': [Smart.ts, Smart.drive]}
    @hybrid_property
    def actual_value(self):
        return self.value * 3

我的问题:如何引用另一个表( model )中的列( drives )作为主表 smart 中的鉴别器？

Answer 1

您可以使用column_property属性使模型“本地”到智能，但代价是具有相关子查询:

class Drives(Base):
    __tablename__ = 'drives'
    id = Column(Integer, primary_key=True)
    model = Column(String)

class Smart(Base):
   __tablename__ = 'smart'
   drive = Column(Integer, ForeignKey(Drives.id), primary_key=True)
   ts = Column(DateTime, primary_key=True)
   value = Column(Integer)
   drives = relationship(Drives)
   model = column_property(select([Drives.model]).where(Drives.id == drive))
   __mapper_args__ = {'polymorphic_on': model}

class ModelASmart(Smart):
    __mapper_args__ = {'polymorphic_identity': 'ModelA'}
    @hybrid_property
    def actual_value(self):
        return self.value * 2

class ModelBSmart(Smart):
    __mapper_args__ = {'polymorphic_identity': 'ModelB'}
    @hybrid_property
    def actual_value(self):
        return self.value * 3

列属性将始终包含在您的查询中，这意味着相关子查询可能会决定查询性能。

SQLAlchemy 还有其他方式在关系上引入属性，例如 association proxies和混合属性，但这些不能用作 polymorphic_on 鉴别器。另一种更奇特的可能性是 map Smart over a join在智能和驱动器表之间。

<小时/>

另一种选择是放弃使用继承，并在 Smart 上使用普通混合属性:

class Drives(Base):
    __tablename__ = 'drives'
    id = Column(Integer, primary_key=True)
    model = Column(String)

class Smart(Base):
   __tablename__ = 'smart'
   drive = Column(Integer, ForeignKey(Drives.id), primary_key=True)
   ts = Column(DateTime, primary_key=True)
   value = Column(Integer)
   drives = relationship(Drives)
   _model_coeff = {
       'ModelA': 2,
       'ModelB': 3,
   }
   @hybrid_property
   def actual_value(self):
       return self.value * self._model_coeff[self.drives.model]
   @actual_value.expression
   def actual_value(cls):
       return cls.value * case(
           cls._model_coeff,
           value=select([Drives.model]).
                 where(Drives.id == cls.drive).
                 as_scalar())

这使用 "shorthand" format case() 将查找 dict 映射到 SQL CASE 表达式。查询例如:

session.query(Smart, Smart.actual_value)

将使用相关子查询在系数之间进行选择，但还有另一种选择以及使用预先加载:

session.query(Smart).options(joinedload(Smart.drives, innerjoin=True))

这样，相关的 Drives 实例就会在同一查询中加载，因此混合属性在实例上访问时不需要执行提取:

# `s` is an instance from the previous eager loading query. This access
# will not fire additional queries.
s.actual_value