Issue: [Question] Nested execution_context replacement in peewee 3
Author: petroprotsakh
State: closed
Created: 2019-04-30 20:22:04

Having existing codebase using peewee 2.x, I recently performed an upgrade to peewee 3.9.3 and ran into some troubles regarding connection context usage. According to [breaking changes doc](http://docs.peewee-orm.com/en/latest/peewee/changes.html#database), all `execution_context()` calls were replaced with database itself as a context manager. But it looks like new approach does not allow nested `with` statements, as several parts of code now fail with `OperationalError: Attempting to close database while transaction is open.`

Considering this simplified example with peewee 2.x:
```python
In [2]: from playhouse.pool import PooledSqliteDatabase

In [3]: db = PooledSqliteDatabase(':memory:')

In [4]: with db.execution_context():
   ...:     with db.execution_context():
   ...:         pass
   ...:
DEBUG:peewee.pool:No connection available in pool.
DEBUG:peewee.pool:Created new connection 140547264495376.
DEBUG:peewee:('BEGIN', None)
DEBUG:peewee.pool:No connection available in pool.
DEBUG:peewee.pool:Created new connection 140547375042608.
DEBUG:peewee.pool:Returning 140547375042608 to pool.
DEBUG:peewee.pool:Returning 140547264495376 to pool.
```

A new connection was instantiated for nested block, while in peewee 3 the first connection is reused, and then fails, trying to close it on inner `__exit__`, while still in outer transaction:

```python
In [2]: from playhouse.pool import PooledSqliteDatabase

In [3]: db = PooledSqliteDatabase(':memory:')

In [4]: with db:
   ...:     with db:
   ...:         pass
   ...:
DEBUG:peewee.pool:No connection available in pool.
DEBUG:peewee.pool:Created new connection 139878371475504.
DEBUG:peewee:('BEGIN', None)
DEBUG:peewee.pool:Returning 139878371475504 to pool.
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-4-34a6cd606493> in <module>
      1 with db:
      2     with db:
----> 3         pass
      4

peewee.py in __exit__(self, exc_type, exc_val, exc_tb)
   2795             top.__exit__(exc_type, exc_val, exc_tb)
   2796         finally:
-> 2797             self.close()
   2798
   2799     def connection_context(self):

peewee.py in close(self)
   2833                                      'before opening a connection.')
   2834             if self.in_transaction():
-> 2835                 raise OperationalError('Attempting to close database while '
   2836                                        'transaction is open.')
   2837             is_open = not self._state.closed

OperationalError: Attempting to close database while transaction is open.
```

My question is — are there any options to make it similar to the old one `execution_context` behavior?
The codebase is pretty large and it's hard to guarantee the absence of nested contexts now or in the future.
Introduced `connection_context` method does not help when both blocks need a transaction.
Should I nevertheless get rid of any nested context call chance, or maybe implement own connection management on app level — what's a proper way to deal with the subject from peewee's point of view? Was the old behavior (new connection for nested block) an incorrect one?

Thanks.

Oh, interesting, I hadn't thought of nested execution contexts but I can see how that might happen in a large enough codebase.

So when you use nested execution context, you expect a transaction to cover the outermost block, right?

Basically my thought was that inner `__exit__` commits it's transaction, but shouldn't try to close the connection. Don't know if it's possible without some sort of `execution_context_depth`.

Fixed. Behaves like nested `atomic()` blocks, with the additional behavior that the outermost block will close the connection upon exit.

Thanks much, that was quick!