commit parity with sqlite3 doesn't exist with `conn.commit()`

[startakovsky]

Description

I was reading this and learned that the context manager for the sqlite3 library automatically handles commits but not closes. I am referring to this line from the Python documentation: # Connection object used as context manager only commits or rollbacks transactions

Seems like the situation is not the same with aiosqlite. That you need to commit.

In other words the first example below should work, but it doesn't.

Doesn't work in an analogous fashion with sqlite3.

async with aiosqlite.connect(DB_FILEPATH) as conn:
        await conn.execute(
            '''
            UPDATE .... 
            ''', 
            (
                ...values
            )
        )
conn.close()

Current code requires an explicit conn.commit()

async with aiosqlite.connect(DB_FILEPATH) as conn:
        await conn.execute(
            '''
            UPDATE .... 
            ''', 
            (
                ...values
            )
        )
       await conn.commit()
conn.close()

[startakovsky]

Anyway, so I believe this is a bug and my request would be to do modify the context manager for the aiosqlite.connection object. I am not going to (attempt) to submit a PR unless I know we're in agreement or if I'm wrong etc. or any special caveats as to what makes this hard to do.

[teobouvard]

This issue was already raised 3 years ago in #110, and it did not receive any answers either.

[amyreese]

Happy to review a PR that adds similar behavior to the connection object. The part that concerns me is this:

If there is no open transaction upon leaving the body of the with statement, or if autocommit is True, the context manager does nothing.

I’m not sure how that’s handled in sqlite, or if that requires special checks, but would like to make sure that nuance is matched here as well.

Edit: and of course, I would want any PR to maintain the existing aiosqlite behavior of automatically closing the connection when the context ends, in addition to any new behavior to automatically commit open transactions.

[auxym]

Does aiosqlite aim to conform to PEP 249, in particular with respect to the autocommit behavior? I looked through the docs and the connection object does not seem to have an autocommit option.

Based on this issue, I understand that aiosqlite then behaves as autocommit=False? Then does it ensure that a transaction is always open (by automatically executing BEGIN TRANSACTION after a db.commit())? Perhaps this could be explicitly clarified in the docs.

If I may ask a question tangentially related, say we run something like this:

    await conn.execute('UPDATE ....  ', ( ...values))
    await db.commit() 

Is it then possible that the async event loop runs some other transaction in between our two awaits? How can we ensure transaction ordering? Hope that is clear...