[issue39457] Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate

[Géry]

Géry <gery.ogam at gmail.com> added the comment:

@james.oldfield

> What that answer doesn't mention is that, even with even with isolation_mode=None, it's perfectly possible to start a transaction, which takes the SQLite engine out of autocommit mode.

Exactly, so since autocommit=True is equivalent to isolation_mode=None I do not see why you the name ‘autocommit’ would be a problem. As you said, when you issue BEGIN, you leave autocommit mode.

> Under your proposal, the first line would be changed to say "autocommit=True", even though not all the code below is in autocommit mode (according to the SQLite engine's definition).

What is the difference with isolation_mode=None which also means autocommit mode?

> What's more, I could insert this line of code between statements 3 and 6:
>    print("Autocommit mode?", conn.autocommit)
> And it would print True even though autocommit mode is off!

No, because the autocommit property would be automatically updated to False at conn.execute("BEGIN"), which is the standard behaviour as @lemburg explained.

@lemburg

> I guess the SQLite driver does not start a new transaction for SELECTs, since these are usually read-only

Nor for DDL statements (CREATE, DROP).

> For the same reason, removing the SELECT "optimization" may cause
> a backwards incompatible change, which can be tricky to identify
> and cause corruption of data (in this case, data not written to
> the database, where it previously was written).

Since DQL statements (SELECT) are read-only, maybe we could keep the optimization and start transactions implicitly only for DDL statements (CREATE, DROP)?

----------

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue39457>
_______________________________________