尽管存在棘手的ForeignKey关系，但SQLAlchemy类如何正确继承? [英] How can a SQLAlchemy Class inherit properly despite having a tricky ForeignKey-relationship?

问题描述

我按照问题 SQLAlchemy继承所述实现了联接表继承.

I implemented joined table inheritance as described in question SQLAlchemy Inheritance.

有以下情况:我想要一个

There is the following situation: I would like to have a

具有emailaddress 的
• 用户
• 别名，它为另一个电子邮件地址分配别名-emailaddress

• User which has an emailaddress
• Alias which assigns an alias - emailaddress to another email address

为了保持emailaddress的唯一性，想法是让两个类都使用联接表继承从Emailaddress继承.这些示例实现了以下类:

In order to keep the emailaddress unique, the idea would be to let both classes inherit from Emailaddress using joined table inheritance. The examples implements the following classes:

• Emailaddress
• EmailaddressUser(Emailaddress)
• EmailaddressAlias(Emailaddress)

• Emailaddress
• EmailaddressUser(Emailaddress)
• EmailaddressAlias(Emailaddress)

继承可用于以下用途:

u = EmailaddressUser(name="Testuser", emailaddress="[e-mail]")

=>我不需要事先实例化Emailaddress-这有助于使用.

=> I don't need to instantiate an Emailaddress beforehand - which facilitates the usage.

不幸的是，对于EmailaddressAlias来说，相同的东西不起作用，尽管唯一的区别是第二个属性，它是相同属性emailaddress的外键.因此，我需要指定 inherit_condition .但是:

Unfortunately the same thing doesn't work for EmailaddressAlias, although the only difference is the second attribute which is a ForeignKey to the same attribute emailaddress. Therefore I need to specify the inherit_condition. BUT:

  a = EmailaddressAlias (
      real_emailaddress="[e-mail]",
      alias_emailaddress="[e-mail]"
      )

->将其添加到数据库时引发IntegrityError.在此处查看完整的示例:

--> Throws an IntegrityError when adding it to the database. See the full example here:

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Emailaddress(Base):
  __tablename__ = 'emailaddresses'
  emailaddress = sa.Column(sa.String, primary_key=True)
  emailtype = sa.Column(sa.String, nullable=False)
  __mapper_args__ = {'polymorphic_on': emailtype}


class EmailaddressUser(Emailaddress):
  __tablename__ = 'emailaddress_users'
  __mapper_args__ = {'polymorphic_identity': 'user'}
  emailaddress = sa.Column(
      sa.String,
      sa.ForeignKey('emailaddresses.emailaddress'),
      primary_key=True)
  name = sa.Column(sa.String, nullable=False)


class EmailaddressAlias(Emailaddress):
  __tablename__ = 'emailaddresses_alias'
  alias_emailaddress = sa.Column(
      sa.String,
      sa.ForeignKey('emailaddresses.emailaddress'),
      primary_key=True)
  real_emailaddress = sa.Column(
      sa.ForeignKey('emailaddresses.emailaddress'),
      nullable=False)
  __mapper_args__ = {
      'polymorphic_identity': 'alias',
      'inherit_condition':Emailaddress.emailaddress==alias_emailaddress}


if __name__ == '__main__':
  engine = sa.create_engine ('sqlite:///email.sqlite', echo=True)
  Base.metadata.bind = engine
  Base.metadata.create_all ()
  Session = orm.sessionmaker (engine)
  session = Session ()
  # add user (works):
  u = EmailaddressUser(name="Testuser", emailaddress="[e-mail]")
  session.add(u)
  session.commit()
  # --> INSERT INTO emailaddresses (emailaddress, emailtype) VALUES (?, ?)
  # --> ('[e-mail]', 'user')
  # 'emailaddress' is inserted correctly

  # add alias (throws an IntegrityError):
  a = EmailaddressAlias (
      real_emailaddress="[e-mail]",
      alias_emailaddress="[e-mail]"
      )
  session.add(a)
  session.commit()
  # --> INSERT INTO emailaddresses (emailtype) VALUES (?)' ('alias',)
  # 'emailaddress' is missing! => IntegrityError

推荐答案

似乎有一个问题，可能是EmailaddressAlias是从EmailAddress而不是Base继承的.

Seems like there might be a problem be that EmailaddressAlias is inheriting from EmailAddress and not from Base.

我不是专家，也没有通过mapper_args完成此操作，但是我发现使用 relationship()来设置外键效果很好.例如:

I'm not an expert on this, nor have I done this through the mapper_args, but I find using relationship() to set up the foreign keys works quite well. For example:

from sqlalchemy.orm import relationship
class EmailaddressAlias(Base):
...
    alias_emailaddress_fk = sa.Column(sa.String, sa.ForeignKey('emailaddresses.emailaddress'))
    alias_emailaddress = relationship(EmailAddress, primaryjoin=alias_emaladdress_fk==EmailAddress.emailaddress)
    real_emailaddress_fk = sa.Column(sa.String, ForeignKey('emailaddresses.emailaddress'))
    real_emailaddress = relationship(EmailAddress,primaryjoin=real_emailaddress_fk==EmailAddress.emailaddress)

这篇关于尽管存在棘手的ForeignKey关系，但SQLAlchemy类如何正确继承?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！