--Il existe deux tables (= modèles) que vous souhaitez configurer avec l'héritage de table conjointe (employé, gestionnaire, ingénieur). --Omoto (Employee) a également des colonnes en commun avec d'autres tables, et je souhaite les définir dans DRY en incluant l'index (Human). --Par conséquent, la configuration est Human-> Employee-> (Manager, Engineer).
Si vous définissez un index pour la classe Human avec le décorateur @declared_attr, l'index ira à la classe inférieure.
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()
Une erreur se produit lors de la tentative d'indexation de la colonne de nom dans une table Manager qui ne définit pas de colonne de nom.
Traceback (most recent call last):
File "joint.py", line 27, in <module>
class Engineer(Employee):
(réduction…)
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'
Cela semble évident, mais je n'ai pas pu avaler la situation à partir du message d'erreur, et il a fallu du temps pour en préciser la cause, alors je vais le publier.
Redéfinissez simplement table_args dans la classe Manager.
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__ = {
}
Je veux écrire la définition de la table et la logique du modèle séparément.
J'ai essayé d'hériter dans l'ordre déclarative_base-> Classe qui spécifie le membre de la colonne (Manager) -> Classe de la couche d'application (SubManager), mais le type qui est la clé du polymorphisme n'est pas spécifié.
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()
production
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
Essayez donc de spécifier @declared_attr.
joint_ng2.py
class Manager(Employee):
(snip...)
@declared_attr
def __mapper_args__(cls):
return {
'polymorphic_identity':'manager',
}
Cela a fonctionné, mais on m'a prévenu que c'était une double définition.
production
/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
Changez simplement l'ordre d'héritage. Tant qu'il existe une correspondance biunivoque entre la couche d'application et le type dérivé d'héritage de table conjointe, il ne doit y avoir aucune différence de fonctionnalité.
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()
production
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
C'était pauvre. Ce serait une joie inattendue si cela aide quelqu'un.
Recommended Posts