postgresql sqlalchemy.orm.exc.ObjectDeletedError:示例已被删除，或者它的行不存在

我有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()

但这不是个好办法。
我用python=3.11.3，sqlalchemy=1.4.48

您的代码在我的环境中运行没有任何问题（python=3.11.2和sqlalchemy=2.0.10）。
无论如何，有一个想法可以尝试：假设你似乎不需要一个完整的模型StrategyMMResult模型，你能用一个关联表的定义来替换它吗？

# 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)

_table_strategy_mm_result = Table(
    "strategy_mm_result",
    Base.metadata,
    Column("strategy_id", ForeignKey("strategies.id")),
    Column("mm_result_id", ForeignKey("money_managements_results.id")),
)

**编辑-1：**这是整个工作脚本：

## Imports

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, Table, __version__ as sa_version
from sqlalchemy.orm import (
    Session,
    as_declarative,
    declared_attr,
    registry,
    relationship,
)

## Configuration

engine = create_engine("sqlite:///:memory:", echo=True)

mapper_registry = registry()
Base = mapper_registry.generate_base()

@as_declarative()
class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    # id = Column(Integer, primary_key=True)

# ## Model definitions
class MoneyManagementsResult(Base):
    __tablename__ = "money_managements_results"

    id = Column(Integer, primary_key=True)

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

class Strategy(Base):
    __tablename__ = "strategies"

    id = Column(Integer, primary_key=True)

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

_table_strategy_mm_result = Table(
    "strategy_mm_result",
    Base.metadata,
    Column("strategy_id", ForeignKey("strategies.id")),
    Column("mm_result_id", ForeignKey("money_managements_results.id")),
)

def _main():
    with Session(engine) as session:
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)
        print("=" * 80)

        # data
        money_management_result = MoneyManagementsResult()
        session.add(money_management_result)
        # session.commit()  # optional: works in both cases

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

if __name__ == "__main__":
    _main()

postgresql sqlalchemy.orm.exc.ObjectDeletedError:示例已被删除，或者它的行不存在

1条答案

相关问题

热门标签

最新问答