Is multi-level polymorphism possible in SQLAlchemy?

Question

Is it possible to have layered polymorphism in SQLAlchemy? Here is an example:

class Entity(Base): __tablename__ = 'entities' id = Column(Integer, primary_key=True) created_at = Column(DateTime, default=datetime.utcnow, nullable=False) entity_type = Column(Unicode(20), nullable=False) __mapper_args__ = {'polymorphic_on': entity_type} class File(Entity): __tablename__ = 'files' id = Column(None, ForeignKey('entities.id'), primary_key=True) filepath = Column(Unicode(255), nullable=False) file_type = Column(Unicode(20), nullable=False) __mapper_args__ = {'polymorphic_identity': u'file', 'polymorphic_on': file_type) class Image(File): __mapper_args__ = {'polymorphic_identity': u'image'} __tablename__ = 'images' id = Column(None, ForeignKey('files.id'), primary_key=True) width = Column(Integer) height = Column(Integer) 

When I call Base.metadata.create_all() , SQLAlchemy raises the following error:

 IntegrityError: (IntegrityError) entities.entity_type may not be NULL`. 

This error disappears if I delete the Image model and the polymorphic_on key in File .

What gives?

Answer 1

Yes The problem with your code is that you are creating an Image file type, when you should aim at the head of the tree, making Image an Entity type.

Example:

 from sqlalchemy import (Table, Column, Integer, String, create_engine, MetaData, ForeignKey) from sqlalchemy.orm import mapper, create_session from sqlalchemy.ext.declarative import declarative_base e = create_engine('sqlite:////tmp/foo.db', echo=True) Base = declarative_base(bind=e) class Employee(Base): __tablename__ = 'employees' employee_id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(30), nullable=False) __mapper_args__ = {'polymorphic_on': type} def __init__(self, name): self.name = name class Manager(Employee): __tablename__ = 'managers' __mapper_args__ = {'polymorphic_identity': 'manager'} employee_id = Column(Integer, ForeignKey('employees.employee_id'), primary_key=True) manager_data = Column(String(50)) def __init__(self, name, manager_data): super(Manager, self).__init__(name) self.manager_data = manager_data class Owner(Manager): __tablename__ = 'owners' __mapper_args__ = {'polymorphic_identity': 'owner'} employee_id = Column(Integer, ForeignKey('managers.employee_id'), primary_key=True) owner_secret = Column(String(50)) def __init__(self, name, manager_data, owner_secret): super(Owner, self).__init__(name, manager_data) self.owner_secret = owner_secret Base.metadata.drop_all() Base.metadata.create_all() s = create_session(bind=e, autoflush=True, autocommit=False) o = Owner('nosklo', 'mgr001', 'ownerpwd') s.add(o) s.commit() 

Answer 2

Impossible (see SQL ALchemy doc ):

Currently, only one discriminator column can be set, usually in the base class in the hierarchy. Cascading polymorphic columns are not yet supported.

So you should follow @nosklo's suggestion to change your legacy pattern.