Issue: Does using `with db:` in a context manager open the database and start a transaction?
Author: mkmoisen
State: closed
Created: 2022-02-15 01:03:44

Not a bug, just question.

The `Database` class has the following code:

```
    def __enter__(self):
        if self.is_closed():
            self.connect()
        ctx = self.atomic()
        self._state.ctx.append(ctx)
        ctx.__enter__()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        ctx = self._state.ctx.pop()
        try:
            ctx.__exit__(exc_type, exc_val, exc_tb)
        finally:
            if not self._state.ctx:
                self.close()
```

Am I correct that this means the following:

    with db:
        foo = Foo.create(name='bar')
        baz = Bar.create(foo=foo)

Will open a connection, open a transaction, perform the inserts, commit the transaction, and close the connection?

----

This is the behavior that I am looking for most of the time.

However I suppose it would be nice on occasion to to have the autocommit feature work, in order to save 2 calls to the database (the begin, and the commit):

    with db:
        foo = Foo.create(name='bar')

Here, I just want to 1) open a connection, 2) auto-commit a a Foo in a single trip to the database and 3) close the connection, instead of the two additional calls to the database in order to open and close a transaction.

Is there a way to accomplish this? For example this might be a nice syntax:

    with db(autocommit=True):
        foo = Foo.create(name='bar')


Correct. Doc:

http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.__enter__

If you just want to do the open/close without the tx, you can:

http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.connection_context

Hi @coleifer , thank you. Is the following statement correct:

The following issues 3 round trips to the database:

    with db:
        Foo.create(name='bar')

Whereas the following issues just 1 round trip to the database:

    with db.connection_context():
        Foo.create(name='bar')



Not necessarily, no - depends on what database you're using. You have to establish a connection, first of all (though you can use the [pool](http://docs.peewee-orm.com/en/latest/peewee/database.html#connection-pooling) to save cost of setup/teardown). After that, both of them must send the INSERT, retrieve the new row primary-key and commit (at some point). The first example commits when the context manager exits. The second commits immediately after issuing the INSERT. So they're pretty much exactly the same - the difference is just where the commit happens.

Hi @coleifer ,

In SQLAlchemy, we can achieve a true autocommit with one-trip to the database, not three.

```
conn = engine.connect().execution_options(isolation_level='AUTOCOMMIT')
conn.execute(text('insert into bar values (2)'))
```

Here are the postgresql logs, showing only one `statement`:

```
2023-02-11 17:48:56.682 PST [64992] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2023-02-11 17:48:56.683 PST [64992] LOG:  statement: insert into bar values (2)
2023-02-11 17:48:56.683 PST [64992] DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 34260/1/0 (used)
```


With Peewee, there does not appear to be a way to perform a true autocommit with one trip to the database.

```
db.execute_sql('insert into bar values (3)')
```

Here are the postgresql logs, showing 3 `statement`s:

```
2023-02-11 17:50:27.616 PST [62684] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2023-02-11 17:50:27.616 PST [62684] LOG:  statement: BEGIN
2023-02-11 17:50:27.616 PST [62684] LOG:  duration: 0.129 ms
2023-02-11 17:50:27.616 PST [62684] LOG:  statement: insert into bar values (3)
2023-02-11 17:50:27.616 PST [62684] LOG:  duration: 0.141 ms
2023-02-11 17:50:27.617 PST [62684] LOG:  statement: COMMIT
2023-02-11 17:50:27.617 PST [62684] DEBUG:  CommitTransaction(1) name: unnamed; blockState: END; state: INPROGRESS, xid/subid/cid: 34261/1/1
```





Would you be open to supporting the one-trip autocommit? It should be simple, as the [DB-API](https://peps.python.org/pep-0249/#autocommit) specifies the 'autocommit' parameter. For example in psycopg2 you simply do:

```
conn = psycopg2.connect(...)
conn.autocommit = True
conn.execute('insert into bar values (1)')
```

There could be an API like:

    with db.autocommit():
        db.execute_sql('insert into bar values (2)')
        
And this `autocommit` could be implemented similar to your `manual_commit`, except that it adds a `autocommit = True`.
  
I would be happy to add a PR if you think this might be useful to add.

This is a good observation and an unfortunate knock-on effect of Peewee relying, under-the-hood, on the db-api 2.0 semantics of psycopg2. The db-api 2.0 spec creates an implicit transaction every time you query (except when disabled by setting the isolation level to autocommit), only committing under certain conditions. This is obnoxious, so Peewee provides autocommit-*like* semantics on top of the db-api 2.0 stuff.

Currently, setting the isolation level to autocommit can be done like this:

```
from psycopg2 import extensions

db = PostgresqlDatabase(..., isolation_level=extensions.ISOLATION_LEVEL_AUTOCOMMIT)
```

The database will now run in autocommit-mode for "top-level" queries, but the behavior is not intuitive because Peewee still assumes psycopg2 is following the "db-api rules" -- these imply we still need to issue commits for all top-level queries to simulate autocommit.

In order to get "true" autocommit with psycopg2, while retaining support for the `atomic()` / etc transaction-management controls, it is necessary to implement explicit `begin()` / `commit()` / `rollback()` to provide "autocommit-aware" versions of these methods.

It may be worth investigating switching our psycopg2 transaction management to being fully managed by Peewee, as we currently do (in-part) for sqlite. I've got an experimental patch for this here if you want to try it out: 69678447d843939721ac9f2a8e0179841349858e - it treats psycopg2 (and sqlite) as being in autocommit-mode.

There may be some obscure bugs for users who were coloring outside the lines w/psycopg2 and Peewee to accomplish similar things, so this would likely be a big change even if the "typical" uses wouldn't be affected. I'll probably need to switch the default `execute_sql()` stuff back to assuming db-api, so as not to screw things up for any users of 3rd-party drivers... Otherwise, I think the changes are OK.

The implication of this would be, then, that the following code would not (explicitly) begin or commit at any point and each query would run the same as if you had run it in `psql`, that is, in autocommit-mode.

```
db = PostgresqlDatabase('my_db')
class MyModel(db.Model):
    value = IntegerField()
db.create_tables([MyModel])
MyModel.create(value=1)
assert len(MyModel) == 1
```

While the decorators and everything else continue to work as well, e.g.:

```
with db.atomic() as txn:
    MyModel.create(value=2)
    txn.rollback()
assert len(MyModel) == 1
```

Just a note - I did not observe any meaningful timing differences when running the test suite between the previous behavior and the new behavior. I've added some additional changes to switch our pymysql/mysql-connector to autocommit as well: cc730660bb4aec06eb97d18be258897e310a02b6