SQL炼金术中的外键和继承 [英] Foreign Keys and inheritance in SQL Alchemy

问题描述

我很难用外键引用在SQLAlchemy中进行继承.

I'm having a hard time using foreign key references for inheritance in SQLAlchemy.

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

I have a drives table that looks like this with id as the primary key:

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

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

I also have another table called smart that looks like this with <ts, drive> as a primary key and drive is a foreign key referencing drives.id:

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

我具有以下表示上述表格的类定义.

I have the following class definitions to represent the above tables.

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，其中根据与drive相对应的模型对smart.value进行不同的解释.

I would like to create two derived classes ModelASmart or ModelBSmart where smart.value is interpreted differently based on the model corresponding to the 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

我的问题:如何将另一个表(drives)中的列(model)用作主表smart中的鉴别符?

My question: How do I refer to a column (model) from another table (drives) as a discriminator in the main table smart?

推荐答案

您可以使用Smart，但需要具有相关的子查询:

You could use a column_property attribute to make the model "local" to Smart, at the expense of having a correlated subquery:

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

column属性将始终包含在查询中，这意味着相关的子查询将可能确定查询性能.

The column property will always be included in your queries, which means that the correlated subquery will probably determine query performance.

SQLAlchemy还具有其他通过关系引入属性的方法，例如关联代理和混合属性，但不能用作polymorphic_on区分符.还有另一种更具异国情调的可能性是在smart和drives表之间的联接上映射Smart .

SQLAlchemy has other means of introducing attributes over relationships as well, such as association proxies and hybrid properties, but those cannot be used as a polymorphic_on discriminator. Yet another, a bit more exotic, possibility would be to map Smart over a join between smart and drives table.

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

Another option would be to forgo using inheritance, and use a plain hybrid property on Smart instead:

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())

这使用，以将查找dict映射到SQL CASE表达式.查询，例如:

This uses the "shorthand" format of case() to map the lookup dict to an SQL CASE expression. A query such as:

session.query(Smart, Smart.actual_value)

将使用相关的子查询在系数之间进行选择，但是还有一个使用紧急加载的选项:

will use the correlated subquery to choose between coefficients, but there is another option as well using eager loading:

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

这样，将相关的Drives实例加载到同一查询中，因此，在访问实例时，hybrid属性不需要执行获取操作:

This way the related Drives instance is loaded in the same query, so the hybrid property does not need to perform a fetch when accessed on an instance:

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

这篇关于SQL炼金术中的外键和继承的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！