CODEWITHSUNDEEP
pythoninheritancesqlalchemy
Philipp der Rautenberg
Philipp der Rautenberg

Reputation: 2272

How can a SQLAlchemy Class inherit properly despite having a tricky ForeignKey-relationship?

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

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

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:

The inheritance enables the following usage:

u = EmailaddressUser(name="Testuser", emailaddress="[email protected]")

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

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="[email protected]",
      alias_emailaddress="[email protected]"
      )

--> 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="[email protected]")
  session.add(u)
  session.commit()
  # --> INSERT INTO emailaddresses (emailaddress, emailtype) VALUES (?, ?)
  # --> ('[email protected]', 'user')
  # 'emailaddress' is inserted correctly

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

Upvotes: 0

Views: 2595

Answers (1)

Adam Morris
Adam Morris

Reputation: 8545

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

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)

Upvotes: 2

Related Questions