SQLAlchemy：具有复合主键的关系表

SQLAlchemy：具有复合主键的关系表

请参见下面的工作代码。关键点是我在评论中提到的那些：

• 适当的复合

  ForeignKey

  小号

• relationship

  使用FK正确配置

码：

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 schemabase.metadata.create_all(engine)# create entitiesw_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 functionsdef get_workflow(id):    return session.query(Workflow).get(id)def get_action(name):    return session.query(Action).filter_by(name=name).one()# test another OKa_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