--There are two tables (= models) that you want to configure with Joint Table Inheritance (Employee, Manager, Engineer). --Omoto (Employee) also has columns in common with other tables, and I want to define them in DRY including indexes (Human). --Therefore, the configuration is Human-> Employee-> (Manager, Engineer).
If you define an index on the Human class with the @declared_attr decorator, the index will go to the bottom class.
joint_ng.py
from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Human(object):
age = Column(Integer)
name = Column(String(50))
@declared_attr
def __table_args__(cls):
return(Index('index_name', 'name'),)
class Employee(Human, Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'engineer',
}
__table_args__ = {
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'manager',
}
__table_args__ = {
}
engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')
if database_exists(engine.url):
drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
m = Manager(age=10, name = 'foo', manager_name='hoge')
session.add(m)
session.commit()
An error occurs when trying to index the name column in the Manager table that does not define the name column.
Traceback (most recent call last):
File "joint.py", line 27, in <module>
class Engineer(Employee):
(abridgement…)
File "/home/satosi/.pyenv/versions/3.6.1/lib/python3.5/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
return self._data[key]
KeyError: 'name'
Speaking of course, it seems that the situation cannot be swallowed from the error message, and it took time to narrow down the cause, so I will publish it.
Simply redefine table_args in the Manager class.
joint_ok.py
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'engineer',
}
__table_args__ = {
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'manager',
}
__table_args__ = {
}
I want to write the table definition and model logic separately.
I tried to inherit in the order of declarative_base-> class that specifies Column member (Manager)-> class of application layer (SubManager), but the type that is the key to polymorphism is not specified.
joint_ng2.py
import sys
from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'manager',
}
__table_args__ = {
}
def shout(self):
print('Oh')
class SubManager(Manager):
def shout(self):
print('Wah')
engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')
if database_exists(engine.url):
drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
m = SubManager(manager_name='hoge')
m.shout()
session.add(m)
session.commit()
query = session.query(Manager)
print(query)
manager = query.first()
assert manager.type is None
manager.shout()
output
Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee INNER JOIN manager ON employee.id = manager.id
Oh
So try specifying @declared_attr.
joint_ng2.py
class Manager(Employee):
(snip...)
@declared_attr
def __mapper_args__(cls):
return {
'polymorphic_identity':'manager',
}
It worked, but I was warned that it was a double definition.
output
/home/satosi/.pyenv/versions/3.6.1-mtxweb/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py:1034: SAWarning: Reassigning polymorphic association for identity 'manager' from <Mapper at 0x7fb0202ed978; Manager> to <Mapper at 0x7fb0202edbe0; SubManager>: Check for duplicate use of 'manager' as value for polymorphic_identity.
self, self.polymorphic_identity)
Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee INNER JOIN manager ON employee.id = manager.id
Wah
Simply change the order of inheritance. As long as there is a one-to-one correspondence between the app layer and the derived type of Joint Table Inheritance, there should be no difference in functionality.
joint_ok.py
from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class _Manager():
def shout(self):
print('Oh')
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
class Manager(Employee, _Manager):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ ={
'polymorphic_identity':'manager',
}
__table_args__ = {
}
def shout(self):
print('Wah')
engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')
if database_exists(engine.url):
drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
m = Manager(manager_name='hoge')
m.shout()
session.add(m)
session.commit()
query = session.query(Manager)
print(query)
manager = query.first()
assert manager.type is not None
manager.shout()
output
Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee INNER JOIN manager ON employee.id = manager.id
Wah
It was poor. It would be an unexpected joy if it helps someone.
Recommended Posts