I'm trying to implement Single Table Inheritance using SQLAlchemy. According to the official SQLAlchemy's documentation (here: official docs), the way to assign a single polymorphic identity, is as follows:
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':type
}
I want to assign multiple identities, for a single class. For example:
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':type
}
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', 'ceo'],
}
Notice the following line:
'polymorphic_identity': ['manager', 'ceo']
This line isn't possible (unfortunately).
I would like to know if there's any way of assigning multiple polymorphic identities to a single class. Unfortunately I couldn't find any example like that in the docs.
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.