SqlAlchemy, specific inheritance, but parent has foreignKey

Question

i has 3 classes:

• User
• Employee <- not required in DB
• Manager

The manager inherits from Employee. The user table is not related to inheritance.

So far so good:

class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) email = Column(String(255)) class Employee(AbstractConcreteBase, Base): name = Column(String(30)) class Manager(Employee): __tablename__ = 'manager' employee_id = Column(Integer, primary_key=True) dept = Column(String(30)) __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True} 

It creates User and Manager, and that is what I want.

But,

The above breaks, if you introduce the parent class ForeignKey:

 class Employee(AbstractConcreteBase, Base): name = Column(String(30)) user_id = Column(Integer, ForeignKey('user.id')) 

error:

sqlalchemy.exc.InvalidRequestError:

 Columns with foreign keys to other columns must be declared as @declared_attr callables on declarative mixin classes. 

so far I have not understood mixin docs (link)

What do I need to include a foreign key in my base class (Employee, in this case)?

Answer 1

You can use mixin as follows:

 from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) email = Column(String(255)) class Employee(object): name = Column(String(30)) @declared_attr def user_id(cls): return Column(Integer, ForeignKey('user.id')) class Manager(Base, Employee): __tablename__ = 'manager' employee_id = Column(Integer, primary_key=True) dept = Column(String(30)) __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True} 

ref: Mixing in columns