SQLAlchemy:与复合主键的关系表 [英] SQLAlchemy: Relation table with composite primary key
问题描述
workflows =表('workflows',Base.metadata,
Column('id',Integer,primary_key = True),
)
$ b $ action = Table('actions',Base.metadata,
Column ,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 ('child_action',String,ForeignKey(actions.c.name),primary_key = True),
)
$ c
$ b我的ORM类看起来像:
$ $ $工作流程(Base):
__table__ =工作流程
动作=关系(Action,order_by =Action.name,backref =workflow )
类动作(基数):
__table__ =动作
儿童=关系(动作,
辅助动作依赖,
primaryjoin = actions.c.name == action_dependencies.c.parent_action,
secondaryjoin = actions.c.name == action_dependencies.c.child_action,
backref =parents
)
所以在我的系统中,每个动作都是由一个工作流ID和它的组合名称。我希望每个操作都有父母
和子元素
属性,这个属性引用了它的父子操作。每个动作可以有多个父母和孩子。
当我有一个函数如:
def set_parents(session,workflow_id,action_name,parents):
action = session.query(db.Action).filter(db.Action.workflow_id == workflow.id) (db.Action.name == action_name).one()
在父母中的parent_name:
parent = session.query(db.Action).filter(db.Action.workflow_id ==)workflow.id).filter(db.Action.name == parent_name).one()
action.parents.append(parent)
$ b $ session.commit()
我得到一个错误:
IntegrityError:(IntegrityError)action_dependencies.workflow_id不能为NULL u'INSERT INTO action_dependencies(parent_action,child_action)VALUES(?,?)'(u'directory_creator',u'packing')
如何获得正确设置workflow_id的关系?
见下面的工作代码。关键点是我在评论中提到的:
ForeignKey
s
关系
使用FK的配置
代码:
workflows =表('workflows',Base.metadata,
列('id' ,整型,primary_key =真),
)
动作=表('actions',Base.metadata,
列('workflow_id',Integer,ForeignKey(workflows.c ('name',String,primary_key = True),
)
action_dependencies =表('action_dependencies',Base.metadata ,
列('workflow_id',Integer,ForeignKey(workflows.c.id),primary_key = True),
Column('parent_action',String,ForeignKey(actions.c.name),primary_key =真),
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__ =工作流
动作=关系(Action,order_by =Action.name,backref =workflow)
动作(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_de pendencies.c.child_action,
actions.c.workflow_id == action_dependencies.c.workflow_id),
backref =parents
)
#create db schema
Base.metadata.create_all(引擎)
#创建实体
w_1 =工作流()
w_2 =工作流()
a_11 =动作=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()
$ b $ test另一个OK
a_11 = get_action(ac-11)
a_12 = get_action(ac-12)
a_11.children.append (a_12)
session.commit()
session.expunge_all()
打印' - '* 80
#test KO(这样做不应该违反FK约束)
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
I have a set of tables that look like:
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),
)
My ORM classes look like:
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"
)
So in my system, each action is uniquely identified by a combination of a workflow id and its name. I'd like each action to have parents
and children
attribute that refers its parent and child actions. Each action can have multiple parents and children.
The problem occurs when I have a function such as :
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()
I get an error like:
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')
How do I get the relationship to set the workflow_id correctly?
See below working code. The key points are those I mentioned in the comments:
- proper composite
ForeignKey
s - correct
relationship
configuration using the FKs
Code:
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
这篇关于SQLAlchemy:与复合主键的关系表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!