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

YON · Accepted Answer · 2015-09-02 03:38:18Z

39

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

answered Sep 2, 2015 at 3:38

YON

1,7473 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PartialOrder Over a year ago

Why is this not accepted answer? @declared_attr is the right way to go.

ivansabik Over a year ago

How would you set the user_id in this case? manager = Manager() then manager.user_id = 2 won't work.. do you absolutely need to use synonyms and getter/setter?

T.H. · Accepted Answer · 2021-11-09 04:06:20Z

Mixin class is where you can put shared features in (such as common columns, common table options ...etc) then let other concrete (sub)classes reuse the code in the mixin by inheriting it. Mixin class itself is NOT ORM-mapped class and will NOT generate a database table after migration.

In latest version of SQLAlchemy (1.4.26 , as of writing this answer), if your mixin class doesn't include foreign-key constriant, then it does not need declared_attr for declaring common field or inherit Base class.

The code would look like :

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    email = Column(String(255))

class EmployeeMixin: # Mixin class
    name = Column(String(30))

class Manager(Base, EmployeeMixin):
    # will copy `name` column from EmployeeMixin to this class once migrated
    __tablename__ = 'manager'
    employee_id = Column(Integer, primary_key=True)
    dept = Column(String(30))
    __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True}

Collectives™ on Stack Overflow

sqlAlchemy, concrete inheritance, but parent has foreignKey

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related