I habe 3 tables for M2M relation
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)
I want create money_management_result and add strategy to strategies.
money_management_result = MoneyManagementsResult(...)
session.add(money_management_result)
session.commit()
money_management_result.strategies.append(strategy)
session.commit()
I get error sqlalchemy.orm.exc.ObjectDeletedError: Instance '<MoneyManagementsResult at 0x7fd05cb3bd10>' has been deleted, or its row is otherwise not present.
Traceback
/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)
After session.flush() I get
money_managements_result.id == None
After session.refresh(money_managements_result) I get
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>'
I solve it with
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()
But it’s a bad solution.
I use python=3.11.3, sqlalchemy=1.4.48
2
Answers
The code that you have runs without any problems in my environment (python=3.11.2 and sqlalchemy=2.0.10).
One idea to try in any event: given you do not seem to need a whole blown model
StrategyMMResultmodel, could you replace it with just a definition for the association table:The error you’re encountering is likely due to the fact that you’re trying to access the
idattribute of theMoneyManagementsResultobject after it has been committed to the database. However, SQLAlchemy does not automatically update the in-memory objects with the values generated by the database upon commit.Try this one:
By using ‘session.flush()’ instead of ‘session.commit()’, you ensure that the changes are synchronized with the database, and the id attribute of the ‘money_management_result’ object is updated with the generated value.
After flushing, you can safely access ‘money_management_result.id’ without encountering the ‘ObjectDeletedError’.
Note that ‘session.flush()’ does not end the transaction like ‘session.commit()’ does. So, you will still need to call ‘session.commit()’ after appending the strategy to ‘money_management_result.strategies’ to persist all changes to the database.