title: SQLAlchemy and saving states of finite state machine during automatic state transitions
body: <p>I try to write code that will send (produce) and get (consume) some requests/responses using external service. Idea is to control all states (waiting in queue, producing, consuming, error, status check, verification and etc), map them to database and save to understand states of processes. Problem is in <strong>commiting automatic transit changes of states</strong> occured internally by itself.</p>
  <p>I'm using sqlalchemy orm for mapping my model class with postgres database.</p>
  <p>Example of model:</p>
  <pre class="lang-py prettyprint-override"><code>class RunProcess(Base):
      __tablename__ = &quot;process&quot;
  
      process_id = Column(String, primary_key=True, nullable=False)
      volume = Column(Integer)
      time_start = Column(DateTime)
      state = Column(String(40))  
      time_end = Column(DateTime)
  
      def __init__(self, *args, **kwargs):
          super().__init__(*args, **kwargs)
          self.process_id = str(uuid.uuid4())
  
      def __repr__(self):
          return f'id: {self.process_id}, start time: {self.time_start}, ' \
                 f'end time {self.time_end}'
  </code></pre>
  <p>I inherit from this model class to construct context class of state pattern in python:</p>
  <pre class="lang-py prettyprint-override"><code>class Run(RunProcess):
  
      _state = None
  
      def __init__(self, data: list,
                   *args, **kwargs) -&gt; None: 
          self.data = data
          local_time = time.time()
          time_start = datetime.datetime.fromtimestamp(local_time)
  
          super().__init__(time_start=time_start,
                           *args, **kwargs)
  
          self.transition_to_state(WaitingInQueue())
  
      def transition_to_state(self, state: State, *args, **kwargs) -&gt; None:      
          self._state = state
          self._state.context = self
  
          # State name
          self.state = state.__class__.__name__
  
  
      def request(self, *args, **kwargs):
          self._state.handle()
      
      def __str__(self):
          return f&quot;State: {self._state}&quot;
  </code></pre>
  <p>And my defined states:</p>
  <pre class="lang-py prettyprint-override"><code># &lt;&lt;Interface&gt;&gt;
  class State(ABC):
  
      @property
      def context(self) -&gt; Run:
          return self._context
  
      @context.setter
      def context(self, context: Run) -&gt; None:
          self._context = context
  
      @abstractmethod
      def handle(self, *args, **kwargs) -&gt; None:
          pass
  
      def __repr__(self) -&gt; str:
          return str(self.__class__.__name__)
  
  
  # Derived classes for states:
  
  # WaitingInQueue is initial state, 
  # it's assigned as soon as created instance of Run class (run = Run([])).
  # 
  # And after making request() it transit to state Producing 
  # that will automatically start handle().
  class WaitingInQueue(State):
      def handle(self, *args, **kwargs) -&gt; None:
          print(&quot;Wait&quot;)
  
          # ... Do some business logic ...
  
          self._context.transition_to_state(Producing())
              
          self._context._state.handle()
  
  # Producing is second state, it's assigned as soon as you 
  # use run.request() in WaitingInQueue state.
  # 
  # It's started itself and automatically transit it state to 
  # Consuming without waiting from instance request() usage
  class Producing(State):
      def handle(self, *args, **kwargs) -&gt; None:
          print('Produce', self._context.data)
          time.sleep(60)
          self._produce()
  
      def _produce(self, *args, **kwargs):
          # ... Here some business logic and make automatic transition after it ...
  
          self._context.transition_to_state(Consuming())
  
  class Consuming(State):
      pass
  </code></pre>
  <p>The main limitation is in <strong>idle in transaction only 15 minutes</strong>.</p>
  <p>Producing and consuming can be very long, that's why creating <code>session = Session()</code> without context or controlling commit and rollback manually <strong>inside my Run class instance</strong> can lead to error in some time (<em>IdleInTransactionSessionTimeout</em>). Moreover, if I do so, my instance become not available after inner commit, e.g.:</p>
  <p>I add <em>Session</em>:</p>
  <pre class="lang-py prettyprint-override"><code>class Run(RunProcess):
      def __init__(self, data: list, Session: Session, # here
                   *args, **kwargs) -&gt; None:
          
          self.data = data
          self.Session = Session # and here
          ...
  
      def transition_to_state(self, state: State, *args, **kwargs) -&gt; None:
          ...
  
          with self.Session() as session:
              session.add(self)
              session.commit()
          ...
  </code></pre>
  <p>And when I try to use such construction:</p>
  <pre class="lang-py prettyprint-override"><code>&gt;&gt;&gt; from run import Run
  &gt;&gt;&gt; from models.database import Session
  
  # Make instance with predefined Session from factory
  &gt;&gt;&gt; run = Run([1,2,3], Session=Session)
  
  # Trying to get __repr__ from RunProcess that was inherited from
  &gt;&gt;&gt; run
  Traceback (most recent call last):
    File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/smev3/database.py&quot;, line 202, in __repr__
      return f'id: {self.process_id}, start time: {self.time_start}, ' \
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/.venv/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py&quot;, line 481, in __get__
      return self.impl.get(state, dict_)
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/.venv/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py&quot;, line 941, in get
      value = self._fire_loader_callables(state, key, passive)
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/.venv/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py&quot;, line 972, in _fire_loader_callables
      return state._load_expired(state, passive)
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/.venv/lib/python3.8/site-packages/sqlalchemy/orm/state.py&quot;, line 710, in _load_expired
      self.manager.expired_attribute_loader(self, toload, passive)
    File &quot;/home/ubuntu/d.studnikov/work/Projects/data_collection/smev/src/smev/.venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py&quot;, line 1369, in load_scalar_attributes
      raise orm_exc.DetachedInstanceError(
  sqlalchemy.orm.exc.DetachedInstanceError: Instance &lt;Run at 0x7fd6c9f6b9a0&gt; is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)
  
  # If we access to __str__ of our Run class it's ok
  &gt;&gt;&gt; print(run)
  State: WaitingInQueue
  </code></pre>
  <p>I know that it is a bad practice due to breaking <a href="https://docs.sqlalchemy.org/en/14/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it" rel="nofollow noreferrer">separate and external principle</a>. However, I don't see any other solutions for committing inexplicit inner state changes. Furthermore, I don't have an opportunity to get attributes of my instance that were mapped by sqlalchemy orm and bounded to session... Of course, I can instance <code>session = Session.object_session(run)</code>, make <code>scoped_session()</code> or <code>expunge()</code> and get access to all bounded attributes outside of instance, but working with two different session scopes inside and outside of instance is bad decision I think.</p>
  <p>On the other hand, if I apply <code>with Session() as session</code> context and won't pass it to signature, I'll not be able to commit changes in state that were <strong>occured automatically by instance itself</strong> , e.g.:</p>
  <pre class="lang-py prettyprint-override"><code>&gt;&gt;&gt; from run import Run
  &gt;&gt;&gt; from models.database import Session
  
  # This is instance of Run class without Session inside
  &gt;&gt;&gt; run = Run([1,2,3])
  &gt;&gt;&gt; with Session() as session:
  ...     session.add(run)
  ...     session.commit()
  ...     print(run, repr(run))
  ...     run.get_smev()
  ...     print(run, repr(run))
  ...     session.commit()
  ... 
  
  # Output:
  State: WaitingInQueue 
  id: 3d358344-4c9d-417c-bca6-2f56310c67ee, start time: 2022-09-06 14:30:38.984317, end time None
  Wait
  Produce [1, 2, 3]
  State: Consuming 
  id: 3d358344-4c9d-417c-bca6-2f56310c67ee, start time: 2022-09-06 14:30:38.984317, end time 2022-09-06 14:33:30.49744
  </code></pre>
  <p>So here in database we will see committed <strong>only WaitingInQueue state and Consuming</strong> states, but <strong>Producing</strong> state won't be reflected and appeared in DB as <code>run</code> instance automatically transit to Consuming state.</p>
  <p>I've read many answers on sqlalchemy <code>Session</code> issues, but I've not found solution to the problem. What should I do to commit <code>Producing</code> state, when it changes to other state itself, and prevent idle in transaction? Is there any way to use <code>Session</code> inside class, self committing changes and working with instance outside like with ordinary object in python?</p>