How does the SQLAlchemy class inherit properly, despite the complexity of the ForeignKey relationship?

Question

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

There is the following situation: I would like to have

• User who has emailaddress
• An alias that assigns an alias - emailaddress another email address

To keep emailaddress unique, the idea would be for both classes to inherit from emailaddress , using combined table inheritance. The following classes are implemented in the examples:

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

Inheritance allows you to use the following:

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

=> I do not need to instantiate emailaddress in advance, which makes it easier to use.

Unfortunately, the same does not work for EmailaddressAlias , although the only difference is the second attribute, which is ForeignKey for the same emailaddress attribute. Therefore, I need to specify the inherit_condition property. BUT:

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

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

Answer 1

It seems like a problem might arise that EmailaddressAlias ​​is inheriting from EmailAddress and not from Base.

I am not an expert on this, and did not do this through mapper_args, but I found using relationship () to configure foreign keys work 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)