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')
Base.metadata.create_all(engine)
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)

def get_workflow(id):
    return session.query(Workflow).get(id)

def get_action(name):
    return session.query(Action).filter_by(name=name).one()
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)
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)
SQLAlchemy: таблица отношений с составным первичным ключом