sqlAlchemy, concrete inheritance, but parent has foreignKey

Question

i have 3 classes:

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

Manager inherits from Employee. User table is not related to the 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, which is what I want.

But,

The above breaks if we introduce a ForeignKey in the parent class:

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

the error is:

sqlalchemy.exc.InvalidRequestError:

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

so far, I didn't understand the mixin docs (link)

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

Answer 1

You can use mixin like this:

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