SQLAlchemy: tabla de relaciones con clave primaria compuesta

Tengo un conjunto de tablas que se parecen a:

workflows = Table('workflows', Base.metadata, Column('id', Integer, primary_key=True), ) actions = Table('actions', Base.metadata, Column('name', String, primary_key=True), Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True), ) action_dependencies = Table('action_dependencies', Base.metadata, Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True), Column('parent_action', String, ForeignKey(actions.c.name), primary_key=True), Column('child_action', String, ForeignKey(actions.c.name), primary_key=True), ) 

Mis clases de ORM parecen:

 class Workflow(Base): __table__ = workflows actions = relationship("Action", order_by="Action.name", backref="workflow") class Action(Base): __table__ = actions children = relationship("Action", secondary=action_dependencies, primaryjoin=actions.c.name == action_dependencies.c.parent_action, secondaryjoin=actions.c.name == action_dependencies.c.child_action, backref="parents" ) 

Entonces, en mi sistema, cada acción se identifica de forma única mediante una combinación de un ID de flujo de trabajo y su nombre. Me gustaría que cada acción tenga atributos de parents e children que se refieran a las acciones de padres e hijos. Cada acción puede tener múltiples padres e hijos.

El problema ocurre cuando tengo una función como:

 def set_parents(session, workflow_id, action_name, parents): action = session.query(db.Action).filter(db.Action.workflow_id == workflow.id).filter(db.Action.name == action_name).one() for parent_name in parents: parent = session.query(db.Action).filter(db.Action.workflow_id == workflow.id).filter(db.Action.name == parent_name).one() action.parents.append(parent) session.commit() 

Me sale un error como:

 IntegrityError: (IntegrityError) action_dependencies.workflow_id may not be NULL u'INSERT INTO action_dependencies (parent_action, child_action) VALUES (?, ?)' (u'directory_creator', u'packing') 

¿Cómo obtengo la relación para configurar el workflow_id correctamente?

Ver código de trabajo a continuación. Los puntos clave son aquellos que mencioné en los comentarios:

  • apropiado compuesto ForeignKey s
  • Configuración correcta de la relationship utilizando los FKs.

Código:

 workflows = Table('workflows', Base.metadata, Column('id', Integer, primary_key=True), ) actions = Table('actions', Base.metadata, Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True), Column('name', String, primary_key=True), ) action_dependencies = Table('action_dependencies', Base.metadata, Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True), Column('parent_action', String, ForeignKey(actions.c.name), primary_key=True), Column('child_action', String, ForeignKey(actions.c.name), primary_key=True), ForeignKeyConstraint(['workflow_id', 'parent_action'], ['actions.workflow_id', 'actions.name']), ForeignKeyConstraint(['workflow_id', 'child_action'], ['actions.workflow_id', 'actions.name']), ) class Workflow(Base): __table__ = workflows actions = relationship("Action", order_by="Action.name", backref="workflow") class Action(Base): __table__ = actions children = relationship("Action", secondary=action_dependencies, primaryjoin=and_(actions.c.name == action_dependencies.c.parent_action, actions.c.workflow_id == action_dependencies.c.workflow_id), secondaryjoin=and_(actions.c.name == action_dependencies.c.child_action, actions.c.workflow_id == action_dependencies.c.workflow_id), backref="parents" ) # create db schema Base.metadata.create_all(engine) # create entities w_1 = Workflow() w_2 = Workflow() a_11 = Action(name="ac-11", workflow=w_1) a_12 = Action(name="ac-12", workflow=w_1) a_21 = Action(name="ac-21", workflow=w_2) a_22 = Action(name="ac-22", workflow=w_2) session.add(w_1) session.add(w_2) a_22.parents.append(a_21) session.commit() session.expunge_all() print '-'*80 # helper functions def get_workflow(id): return session.query(Workflow).get(id) def get_action(name): return session.query(Action).filter_by(name=name).one() # test another OK a_11 = get_action("ac-11") a_12 = get_action("ac-12") a_11.children.append(a_12) session.commit() session.expunge_all() print '-'*80 # test KO (THIS SHOULD FAIL VIOLATING FK-constraint) a_11 = get_action("ac-11") a_22 = get_action("ac-22") a_11.children.append(a_22) session.commit() session.expunge_all() print '-'*80 

No creo que sea correcto tener la clave principal una clave externa. ¿Cómo funciona?

Pero para hacer una restricción compuesta, una clave que es “única en conjunto”, use esto en la definición de tabla:

 UniqueConstraint(u"name", u"workflow_id"), 

Pero si realmente quieres que sea la clave principal, también puedes usar esto:

 PrimaryKeyConstraint(*columns, **kw) 

http://docs.sqlalchemy.org/en/latest/core/schema.html#sqlalchemy.schema.PrimaryKeyConstraint