添加一个对象关联两个现有对象

Question

我有两个表，分别叫做 testInstance 和 bugzilla，它们通过第三个表 bzCheck 关联起来，结构大致是这样的：

class Instance(Base):
    __tablename__ = "testInstance"

    id = Column(Integer, primary_key=True)

    bz_checks = relation(BZCheck, backref="instance")

class BZCheck(Base):
    __tablename__ = "bzCheck"

    instance_id = Column(Integer, ForeignKey("testInstance.id"), primary_key=True)
    bz_id = Column(Integer, ForeignKey("bugzilla.id"), primary_key=True)
    status = Column(String, nullable=False)

    bug = relation(Bugzilla, backref="checks")

class Bugzilla(Base):
    __tablename__ = "bugzilla"

    id = Column(Integer, primary_key=True)

后端使用的是 PostgreSQL 服务器；我正在使用 SQLalchemy 0.5

如果我创建了 Instance、Bugzilla 和 BZCheck 对象，然后执行以下操作：

bzcheck.bug = bugzilla
instance.bz_checks.append(bzcheck)

然后添加并提交它们；一切都正常。

但是现在，假设我已经有了一个现有的实例和一个现有的 bugzilla，想要把它们关联起来：

instance = session.query(Instance).filter(Instance.id == 31).one()
bugzilla = session.query(Bugzilla).filter(Bugzilla.id == 19876).one()
check = BZCheck(status="OK")
check.bug = bugzilla
instance.bz_checks.append(check)

结果失败了：

In [6]: instance.bz_checks.append(check)
2012-01-09 18:43:50,713 INFO sqlalchemy.engine.base.Engine.0x...3bd0 select nextval('"bzCheck_instance_id_seq"')
2012-01-09 18:43:50,713 INFO sqlalchemy.engine.base.Engine.0x...3bd0 None
2012-01-09 18:43:50,713 INFO sqlalchemy.engine.base.Engine.0x...3bd0 ROLLBACK

它试图从一个不存在的序列中获取一个新的 ID，而不是使用外键 "testInstance.id"... 我不明白为什么会这样。我在提交对象后尝试修改它们时也遇到过类似的问题；我应该是漏掉了什么基本的东西，但到底是什么呢？

Answer 1

你这里缺少的部分是堆栈跟踪。总是要查看堆栈跟踪——这里关键的是它是自动刷新（autoflush），是因为访问了 instance.bz_checks：

Traceback (most recent call last):
  File "test.py", line 44, in <module>
    instance.bz_checks.append(check)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 168, in __get__
    return self.impl.get(instance_state(instance),dict_)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 453, in get
    value = self.callable_(state, passive)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line 563, in _load_for_state
    result = q.all()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 1983, in all
    return list(self)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2092, in __iter__
    self.session._autoflush()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 973, in _autoflush
    self.flush()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1547, in flush
    self._flush(objects)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1616, in _flush
    flush_context.execute()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 328, in execute
    rec.execute(self)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 472, in execute
    uow
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2291, in _save_obj
    execute(statement, params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1538, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1646, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1639, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column "instance_id" violates not-null constraint
 'INSERT INTO "bzCheck" (bz_id, status) VALUES (%(bz_id)s, %(status)s) RETURNING "bzCheck".instance_id' {'status': 'OK', 'bz_id': 19876}

你可以看到这一点，因为这行代码是：

instance.bz_checks.append(check)

然后是自动刷新：

self.session._autoflush()
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 973, in _autoflush

有三种解决方案：

a. 暂时禁用自动刷新（可以参考 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush）

b. 确保在访问任何集合之前，BZCheck 关联对象总是以其所需的完整状态创建：

BZState(bug=bugzilla, instance=instance)

（对于关联对象来说，这通常是个好主意——它们代表两个点之间的关联，所以最好在创建时就包含这个状态）

c. 更改级联规则，以便 check.bug = somebug 的操作不会立即将 check 放入会话中。你可以通过 cascade_backrefs 来做到这一点，详细说明见 http://www.sqlalchemy.org/docs/orm/session.html#controlling-cascade-on-backrefs。（但你需要使用 0.6 或 0.7 版本）