当外键约束是复合主键的一部分时，依赖规则试图清除 SQLAlchemy 中的主键 [英] Dependency rule tried to blank out primary key in SQLAlchemy, when foreign key constraint is part of composite primary key
问题描述
我有以下模型定义
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
name = Column(String(200))
class FooCycle(Base):
__tablename__ = 'foocycle'
foo_id = Column(
String(50),
ForeignKey('foo.id'),
primary_key=True
)
some_number = Column(
Integer,
primary_key=True,
)
foo = relationship("Foo", backref="cycles")
以及下面的测试用例
class HierarchicModelTest(unittest.TestCase):
def test_create_data_via_orm_save_twice(self):
# get_session is a convenience wrapper to access a scoped session object
s = get_session()
def create_foo():
foo = Foo(id="12345", name="fancy foo")
foo.cycles = [FooCycle(some_number=1)]
return foo
# initially create foo
foo = create_foo()
s.add(foo)
s.flush()
# recreating foo, using merge to update into database
foo = create_foo()
s.merge(foo)
# raises Exception: Dependency rule tried to blank-out primary key
# column 'foocycle.foo_id' on instance '<FooCycle at 0x32e6b10>'
s.flush()
测试失败，出现一个整洁的小堆栈跟踪和最终断言错误，告诉我依赖规则试图清除主键列 'foocycle.foo_id".我假设 SQLAlchemy 不能，或者不想计算 FooCycle 本身的 foo_id 值.我可以自己在 create_foo 中明确设置这个值:
The test fails with a neat little stack trace and the final assertion error, telling me that the "Dependency rule tried to blank-out primary key column 'foocycle.foo_id". I'm assuming SQLAlchemy cannot, or doesn't want to calculate the value for foo_id on FooCycle itself. I can explicitly set this value myself in create_foo:
def create_foo():
foo = Foo(id="12345", name="fancy foo")
foo.cycles = [FooCycle(some_number=1, foo_id="12345")]
return foo
但是，由于简洁、架构考虑和无可否认的个人自豪感，我不想这样做.有没有一种简单的方法可以让 SQLAlchemy 解决这个问题.我还没有完全理解依赖规则的目的.有关该问题的任何指示/信息?
But, due to conciseness, architectural considerations and admittedly personal pride I don't want to. Is there a simple way to get SQLAlchemy to resolve this issue. I haven't quite grasped the purpose of the dependency rule. Any pointers/information on that issue?
堆栈跟踪:
# Test 1 of 7:
# test_core.HierarchicModelTest.test_create_data_via_orm_save_twice
===============
HierarchicModelTest: test_create_data_via_orm_save_twice (tests.test_core.HierarchicModelTest)
Failed test "test_create_data_via_orm_save_twice (tests.test_core.HierarchicModelTest)"! Reason: Dependency rule tried to blank-out primary key column 'foocycle.foo_id' on instance '<FooCycle at 0x39cda10>'
Traceback (most recent call last):
File "/home/xxx/xxx/xxx/backend/tests/test_core.py", line 115, in test_create_data_via_orm_save_twice
s.flush()
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1879, in flush
self._flush(objects)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1997, in _flush
transaction.rollback(_capture_exception=True)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1961, in _flush
flush_context.execute()
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 370, in execute
rec.execute(self)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 479, in execute
self.dependency_processor.process_saves(uow, states)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 552, in process_saves
uowcommit, False)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 569, in _synchronize
sync.clear(dest, self.mapper, self.prop.synchronize_pairs)
File "/home/xxx/xxx/xxx/.env/lib/python2.7/site-packages/sqlalchemy/orm/sync.py", line 53, in clear
(r, orm_util.state_str(dest))
AssertionError: Dependency rule tried to blank-out primary key column 'foocycle.foo_id' on instance '<FooCycle at 0x39cda10>'
推荐答案
根据 van 的评论，我找到了一个解决方案.默认的关系级联是 "save-update, merge".我必须将其设置为 保存更新、合并、删除、删除孤立".
Based on the comment by van I was able to work out a solution. The default relationship cascade is "save-update, merge". I had to set this to "save-update, merge, delete, delete-orphan".
添加 delete 本身并没有改变行为，delete-orphan 是必要的.
Adding delete by itself did not change the behavior, delete-orphan was necessary.
仅添加 delete-orphan 使删除测试用例失败，并出现问题中提到的依赖规则"断言错误:
Adding only delete-orphan made a deletion testcase fail, with the "dependency rule" assertion error, mentioned in the question:
class HierarchicModelTest(unittest.TestCase):
def test_delete_parent_object(self):
foo = Foo(**foo_data).save()
self.assertEqual(Foo.query.count(), 1)
self.assertEqual(FooCycle.query.count(), 1)
s = get_session()
s.delete(foo)
s.flush()
self.assertEqual(Foo.query.count(), 0)
self.assertEqual(FooCycle.query.count(), 0)
--
File "/home/xxx/xxx/xxx/backend/tests/test_core.py", line 128, in test_delete_parent_object
s.flush()
[...]
AssertionError: Dependency rule tried to blank-out primary key column 'foocycle.foo_id' on instance '<FooCycle at 0x37a1710>'
来自 SQLAlchemy 文档:
From the SQLAlchemy docs:
delete-orphan 级联向 delete 级联添加行为，这样子对象在与父级解除关联时将被标记为删除，而不仅仅是当父节点被标记为删除时.
delete-orphancascade adds behavior to thedeletecascade, such that a child object will be marked for deletion when it is de-associated from the parent, not just when the parent is marked for deletion.
所以，FooCycle 模型的正确定义是
So, the correct definition of the FooCycle Model is
class FooCycle(Base):
__tablename__ = 'foocycle'
foo_id = Column(
String(50),
ForeignKey('foo.id'),
primary_key=True
)
some_number = Column(
Integer,
primary_key=True,
)
foo = relationship("Foo",
backref=backref("cycles",
cascade="save-update, merge, "
"delete, delete-orphan"))
这篇关于当外键约束是复合主键的一部分时，依赖规则试图清除 SQLAlchemy 中的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
