SQLAlchemy polymorphic on multiple identities for a class

I know this has long been answered, but this is another option.

The polymorphic_on can also take a case according to the docs

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.polymorphic_on

So each class still only has 1 polymorphic_identity but in the polymorphic_on you can map multiple values to the same identity.

In your case it could look like:

 class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        "polymorphic_on":case(
            [
                (type == "manager", "manager"),
                (type == "ceo", "manager"),
            ],
            else_="employee"
         )
    }


class Manager(Employee):
    manager_name = Column(String(30))

    company_id = Column(ForeignKey('company.id'))
    company = relationship("Company", back_populates="managers")

    __mapper_args__ = {
        'polymorphic_identity': 'manager', 
    }

I have not tried with in or SQLAlchemy's .in_

That code won't work because the polymorphic_identity of a class is used as a dictionary key. So for the same reason as [] in {} raises a TypeError, so too does your code.

The usual way to do something like this would be to create a CEO class that was a subclass of Manager which would then be able to have it's own polymorphic_identity as well as Mapper. But all that aside, you can create a second polymorphic_identity for a given class (but it's kinda hacky).

Each class that inherits from Base has reference to the same polymorphic_map, which is just a dict:

from sqlalchemy.orm import class_mapper
emp_mapper = class_mapper(Employee)
mgr_mapper = class_mapper(Manager)
print(type(emp_mapper.polymorphic_map))  # <class 'dict'>
print(emp_mapper.polymorphic_map is mgr_mapper.polymorphic_map)  # True

The polymorphic_map maps a polymorphic_identity to a Mapper so that when a given row is fetched from the database, the value of the column assigned as the polymorphic_identity column can be used to get the class that should be used to represent that data. For example, after only defining your Employee class, the polymorphic_map looks like this: {'employee': <Mapper at 0x1b1eafcac50; Employee>}. And after defining the Manager class with 'manager' as the polymorphic_identity, it looks like this: {'employee': <Mapper at 0x25d10b19cf8; Employee>, 'manager': <Mapper at 0x25d0fdbd4e0; Manager>}

I'll create some test data (I had to remove all references to company table - see MCVE):

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
s = Session()
engineer = Employee(name='engineer')
manager1 = Manager(name='manager1')
manager2 = Manager(name='manager2')
s.add_all([engineer, manager1, manager2])
s.commit()
print(s.query(Employee).all())  # [<__main__.Employee object at 0x000001E5E54A0550>, <__main__.Manager object at 0x000001E5E54A0CF8>, <__main__.Manager object at 0x000001E5E54A0D68>]
print(s.query(Manager).all())  # [<__main__.Manager object at 0x000001E5E54A0CF8>, <__main__.Manager object at 0x000001E5E54A0D68>]

Now, lets promote a manager to CEO:

manager1.type = 'ceo'
s.commit()

This emits a warning:

SAWarning: Flushing object <Manager at 0x1e5e54a0cf8> with incompatible polymorphic identity 'ceo'; the object may not refresh and/or load correctly (this warning may be suppressed after 10 occurrences)

But we'll ignore that and try to query the employee table:

print(s.query(Employee).all())

Raises:

Traceback (most recent call last):
  File "C:\Users\peter_000\.virtualenvs\test-_0Fb_hDQ\lib\site-packages\sqlalchemy\orm\loading.py", line 721, in configure_subclass_mapper
    sub_mapper = mapper.polymorphic_map[discriminator]
KeyError: 'ceo'

So the type column for manager1 now has a value that isn't present in the polymorphic_map and we get a key error. As we are only ever interested in a CEO being represented as a Manager then we can just manually put an entry into the polymorphic_map associating the key 'ceo' with the Mapper for the Manager class. E.g:

mgr_mapper.polymorphic_map['ceo'] = mgr_mapper

Now lets query the employee table again:

print(s.query(Employee).all())  #  [<__main__.Employee object at 0x0000020EE7320550>, <__main__.Manager object at 0x0000020EE7320CF8>, <__main__.Manager object at 0x0000020EE7320D68>]

Note that it now prints two manager objects again.

Disclaimer: Inside the Mapper for Manager it maintains a reference to the polymorphic_identity of the class (which is 'manager'), and so our ceo key in the polymorphic_map points to a mapper that references a polymorphic_identity of manager. I mention this as while all of this works fine in this example, I do not know whether this might cause bugs elsewhere in sqlalchemy. So, if you use something like this in production, make sure it's well tested.