如何在复杂的外键关系中正确继承SQLAlchemy类？

Question

我实现了一个叫做“连接表继承”的功能，具体内容可以参考这个问题：SQLAlchemy 继承。

现在有这样的情况：我想要有一个

• 用户，他有一个 emailaddress（电子邮件地址）
• 别名，它将一个别名 emailaddress 指定给另一个电子邮件地址

为了确保 emailaddress 是唯一的，我的想法是让这两个类都继承自 Emailaddress，使用 连接表继承。这个例子实现了以下几个类：

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

这种继承方式使得使用起来更方便：

u = EmailaddressUser(name="Testuser", emailaddress="testuser@test.com")

=> 我不需要事先创建一个 Emailaddress 的实例，这样使用起来更简单。

不过，EmailaddressAlias 的情况就不一样了，尽管它唯一的不同是第二个属性是指向同一个 emailaddress 的外键。因此，我需要指定 inherit_condition。但是：

  a = EmailaddressAlias (
      real_emailaddress="testuser@test.com",
      alias_emailaddress="tu@test.com"
      )

--> 在将其添加到数据库时会抛出一个完整性错误。完整的例子可以在这里查看：

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="testuser@test.com")
  session.add(u)
  session.commit()
  # --> INSERT INTO emailaddresses (emailaddress, emailtype) VALUES (?, ?)
  # --> ('testuser@test.com', 'user')
  # 'emailaddress' is inserted correctly

  # add alias (throws an IntegrityError):
  a = EmailaddressAlias (
      real_emailaddress="testuser@test.com",
      alias_emailaddress="tu@test.com"
      )
  session.add(a)
  session.commit()
  # --> INSERT INTO emailaddresses (emailtype) VALUES (?)' ('alias',)
  # 'emailaddress' is missing! => IntegrityError

Answer 1

看起来可能有个问题，就是 EmailaddressAlias 是从 EmailAddress 继承的，而不是从 Base 继承的。

我不是这方面的专家，也没有通过 mapper_args 来做过这个，但我发现使用 relationship() 来设置外键效果挺好的。例如：

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)