首页 > python > SQL Alchemy中的外键和继承

SQL Alchemy中的外键和继承 (Foreign Keys and inheritance in SQL Alchemy)

问题

我在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} 

我想创建两个派生类ModelASmartModelBSmart其中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从另一个table(drives)中引用column ()作为主表中的鉴别器smart

解决方法

你可以使用一个column_property属性来制作model“本地” 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 = 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属性将始终包含在您的查询中,这意味着相关子查询可能会确定查询性能。

SQLAlchemy还有其他方法可以在关系上引入属性,例如关联代理和混合属性,但这些不能用作polymorphic_on鉴别器。另一个更奇特的可能性是映射Smartsmartdrives表之间的连接


另一种选择是放弃使用继承,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())

这将使用“的简写”格式case(),以查找映射dict到SQL CASE表达式。查询如:

session.query(Smart, Smart.actual_value)

将使用相关子查询在系数之间进行选择,但还有另一个选项使用预先加载:

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

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

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

问题

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

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 |

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} 

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

My question: How do I refer to a column (model) from another table (drives) as a discriminator in the main table 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

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

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.


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

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

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
相似信息