sqlalchemy.orm.exc.ObjectDeletedError: Instance has been deleted, or its row is otherwise not present

小能豆

sqlalchemy.orm.exc.ObjectDeletedError: Instance has been deleted, or its row is otherwise not present

python

我有 3 个表用于 M2M 关系

class MoneyManagementsResult(Base):
    __tablename__ = "money_managements_results"

    strategies = relationship(
        "Strategy",
        secondary="strategy_mm_result",
        back_populates="mm_results"
    )


class Strategy(Base):
    __tablename__ = "strategies"

    mm_results = relationship(
        "MoneyManagementsResult",
        secondary="strategy_mm_result",
        back_populates="strategies"
    )


class StrategyMMResult(Base):
    __tablename__ = "strategy_mm_result"

    strategy_id = Column(Integer, ForeignKey("strategies.id"), primary_key=True)
    mm_result_id = Column(Integer, ForeignKey("money_managements_results.id"), primary_key=True)

我想创建money_management_result并添加strategy到strategies.

money_management_result = MoneyManagementsResult(...)
session.add(money_management_result)
session.commit()

money_management_result.strategies.append(strategy)
session.commit()

我收到错误sqlalchemy.orm.exc.ObjectDeletedError: Instance '<MoneyManagementsResult at 0x7fd05cb3bd10>' has been deleted, or its row is otherwise not present.

追溯

/modeling/stats_output/layer_choice.py:292: SAWarning: Column 'money_managements_results.id' is marked as a member of the primary key for table 'money_managements_results', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.
  session.commit()
---------------------------------------------------------------------------
ObjectDeletedError                        Traceback (most recent call last)
Cell In[2], line 1
----> 1 find_limit_results(1, 1, 4, 2.5, 1)

File /modeling/stats_output/layer_choice.py:293, in find_limit_results(sample_id, strategy_id, factor, leverage, money_management_id)
    291 session.add(money_managements_result)
    292 session.commit()
--> 293 print(money_managements_result.id)
    294 session.refresh(money_managements_result)
    295 # session.flush()

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:487, in InstrumentedAttribute.__get__(self, instance, owner)
    482 except AttributeError as err:
    483     util.raise_(
    484         orm_exc.UnmappedInstanceError(instance),
    485         replace_context=err,
    486     )
--> 487 return self.impl.get(state, dict_)

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:959, in AttributeImpl.get(self, state, dict_, passive)
    956 if not passive & CALLABLES_OK:
    957     return PASSIVE_NO_RESULT
--> 959 value = self._fire_loader_callables(state, key, passive)
    961 if value is PASSIVE_NO_RESULT or value is NO_VALUE:
    962     return value

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:990, in AttributeImpl._fire_loader_callables(self, state, key, passive)
    984 def _fire_loader_callables(self, state, key, passive):
    985     if (
    986         self.accepts_scalar_loader
    987         and self.load_on_unexpire
    988         and key in state.expired_attributes
    989     ):
--> 990         return state._load_expired(state, passive)
    991     elif key in state.callables:
    992         callable_ = state.callables[key]

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/state.py:712, in InstanceState._load_expired(self, state, passive)
    705 toload = self.expired_attributes.intersection(self.unmodified)
    706 toload = toload.difference(
    707     attr
    708     for attr in toload
    709     if not self.manager[attr].impl.load_on_unexpire
    710 )
--> 712 self.manager.expired_attribute_loader(self, toload, passive)
    714 # if the loader failed, or this
    715 # instance state didn't have an identity,
    716 # the attributes still might be in the callables
    717 # dict.  ensure they are removed.
    718 self.expired_attributes.clear()

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/loading.py:1465, in load_scalar_attributes(mapper, state, attribute_names, passive)
   1462 # if instance is pending, a refresh operation
   1463 # may not complete (even if PK attributes are assigned)
   1464 if has_key and result is None:
-> 1465     raise orm_exc.ObjectDeletedError(state)

ObjectDeletedError: Instance '<MoneyManagementsResult at 0xffff8b545610>' has been deleted, or its row is otherwise not present.

In [3]: find_limit_results(1, 1, 4, 2.5, 1)
---------------------------------------------------------------------------
ObjectDeletedError                        Traceback (most recent call last)
Cell In[3], line 1
----> 1 find_limit_results(1, 1, 4, 2.5, 1)

File /modeling/stats_output/layer_choice.py:293, in find_limit_results(sample_id, strategy_id, factor, leverage, money_management_id)
    291 session.add(money_managements_result)
    292 session.commit()
--> 293 print(money_managements_result.id)
    294 session.refresh(money_managements_result)
    295 # session.flush()

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:487, in InstrumentedAttribute.__get__(self, instance, owner)
    482 except AttributeError as err:
    483     util.raise_(
    484         orm_exc.UnmappedInstanceError(instance),
    485         replace_context=err,
    486     )
--> 487 return self.impl.get(state, dict_)

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:959, in AttributeImpl.get(self, state, dict_, passive)
    956 if not passive & CALLABLES_OK:
    957     return PASSIVE_NO_RESULT
--> 959 value = self._fire_loader_callables(state, key, passive)
    961 if value is PASSIVE_NO_RESULT or value is NO_VALUE:
    962     return value

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py:990, in AttributeImpl._fire_loader_callables(self, state, key, passive)
    984 def _fire_loader_callables(self, state, key, passive):
    985     if (
    986         self.accepts_scalar_loader
    987         and self.load_on_unexpire
    988         and key in state.expired_attributes
    989     ):
--> 990         return state._load_expired(state, passive)
    991     elif key in state.callables:
    992         callable_ = state.callables[key]

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/state.py:712, in InstanceState._load_expired(self, state, passive)
    705 toload = self.expired_attributes.intersection(self.unmodified)
    706 toload = toload.difference(
    707     attr
    708     for attr in toload
    709     if not self.manager[attr].impl.load_on_unexpire
    710 )
--> 712 self.manager.expired_attribute_loader(self, toload, passive)
    714 # if the loader failed, or this
    715 # instance state didn't have an identity,
    716 # the attributes still might be in the callables
    717 # dict.  ensure they are removed.
    718 self.expired_attributes.clear()

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/loading.py:1465, in load_scalar_attributes(mapper, state, attribute_names, passive)
   1462 # if instance is pending, a refresh operation
   1463 # may not complete (even if PK attributes are assigned)
   1464 if has_key and result is None:
-> 1465     raise orm_exc.ObjectDeletedError(state)

session.flush()我得到后

money_managements_result.id == None

session.refresh(money_managements_result)我得到后

InvalidRequestError                       Traceback (most recent call last)
Cell In[2], line 1
----> 1 find_limit_results(1, 1, 4, 2.5, 1)

File /modeling/stats_output/layer_choice.py:294, in find_limit_results(sample_id, strategy_id, factor, leverage, money_management_id)
    292 session.flush()
    293 print(money_managements_result.id)
--> 294 session.refresh(money_managements_result)
    295 # session.flush()
    296 money_managements_result.strategies.append(strategy)   # error

File /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py:2355, in Session.refresh(self, instance, attribute_names, with_for_update)
   2343 stmt = sql.select(object_mapper(instance))
   2344 if (
   2345     loading.load_on_ident(
   2346         self,
   (...)
   2353     is None
   2354 ):
-> 2355     raise sa_exc.InvalidRequestError(
   2356         "Could not refresh instance '%s'" % instance_str(instance)
   2357     )

InvalidRequestError: Could not refresh instance '<MoneyManagementsResult at 0xffff68089050>'

我解决它

session.add(money_managements_result)
session.commit()

mm_result_id = session.query(MoneyManagementsResult).order_by(MoneyManagementsResult.id.desc()).first().id

strategy_mm_result = StrategyMMResult(strategy_id=strategy.id, mm_result_id=mm_result_id)
session.add(strategy_mm_result)
session.commit()

但这是一个糟糕的解决方案..

阅读 264

2023-06-13

共1个答案

小能豆

看起来您遇到了一个关于删除对象的错误。解决这个问题的一种方法是确保在添加策略到money_management_result之前将其提交到数据库。

您可以尝试以下更改：

money_management_result = MoneyManagementsResult(...)
session.add(money_management_result)
session.commit()

# 确保 money_management_result 已经提交到数据库并获得了一个有效的 id

mm_result_id = money_management_result.id

strategy_mm_result = StrategyMMResult(strategy_id=strategy.id, mm_result_id=mm_result_id)
session.add(strategy_mm_result)
session.commit()

通过先提交money_management_result并获取其有效的 id，然后使用该 id 创建 StrategyMMResult 对象并将其添加到会话中，然后再次提交，您应该能够成功建立关联。

这种方法可确保正确处理对象之间的关系，并确保在建立多对多关系时避免出现错误。

请注意，上述代码是基于您提供的模型定义和数据访问代码进行的假设。确保在实际应用中适应您的代码结构和逻辑。

2023-06-13