New submission from Géry <gery.o...@gmail.com>:
In non-autocommit mode (manual commit mode), the sqlite3 database driver
implicitly issues a BEGIN statement before each DML statement (INSERT, UPDATE,
DELETE, REPLACE) not already in a database transaction, BUT NOT before DDL
statements (CREATE, DROP) nor before DQL statements (SELECT) (cf.
https://github.com/python/cpython/blob/master/Modules/_sqlite/cursor.c#L480):
```
/* We start a transaction implicitly before a DML statement.
SELECT is the only exception. See #9924. */
if (self->connection->begin_statement && self->statement->is_dml) {
if (sqlite3_get_autocommit(self->connection->db)) {
result = _pysqlite_connection_begin(self->connection);
if (!result) {
goto error;
}
Py_DECREF(result);
}
}
```
Like Mike Bayer explained in issue #9924, this is not what other database
drivers do, and this is not PEP 249 compliant (Python Database API
Specification v2.0), as its author Marc-André Lemburg explained (cf.
https://mail.python.org/pipermail/db-sig/2010-September/005645.html):
> Randall Nortman wrote:
> # PEP 249 says that transactions end on commit() or rollback(), but it
> # doesn't explicitly state when transactions should begin, and there is
> # no begin() method.
>
> Transactions start implicitly after you connect and after you call
.commit() or .rollback(). They are not started for each statement.
>
> # I think the implication is that transactions begin
> # on the first execute(), but that's not explicitly stated. At least
> # one driver, pysqlite2/sqlite3, does not start a transaction for a
> # SELECT statement. It waits for a DML statement (INSERT, UPDATE,
> # DELETE) before opening a transaction. Other drivers open transactions
> # on any statement, including SELECT.
> #
> # My question for the DB-SIG is: Can I call it a bug in pysqlite2 that
> # it does not open transactions on SELECT? Should the spec be amended
> # to make this explicit? Or are both behaviors acceptable, in which
> # case perhaps a begin() method needs to be added for when the user
> # wants control over opening transactions?
>
> I should probably add a note to PEP 249 about this.
Aymeric Augustin said in issue #10740:
> While you're there, it would be cool to provide "connection.autocommit =
> True" as an API to enable autocommit, because "connection.isolation_level =
> None" isn't a good API at all -- it's very obscure and has nothing to do with
> isolation level whatsoever.
So I suggest that we introduce a new autocommit property and use it to enable a
truly PEP 249 compliant manual commit mode (that is to say with transactions
starting implicitly after connect(), commit() and rollback() calls, allowing
transactional DDL and DQL):
```
autocommit = True # enable the autocommit mode
autocommit = False # disable the autocommit mode (enable the new PEP 249
manual commit mode)
autocommit = None # fallback to the commit mode set by isolation_level
```
I also suggest that we use this new PEP 249 manual commit mode (with
transactional DDL and DQL) by default and drop the old manual commit mode
(without transactional DDL and DQL). We could use the following migration
strategy:
1. During the deprecation period:
- Add the new autocommit property with the value None by default, so that the
old manual commit mode is still the default.
- Add a deprecation warning for the value None of the autocommit property, in
favor of the other values True and False. It will prompt users who enabled the
autocommit mode with isolation_level = None to use autocommit = True instead,
and users who disabled the autocommit mode (that is to say users who enabled
the old manual commit mode) with isolation_level = DEFERRED/IMMEDIATE/EXCLUSIVE
to use autocommit = False instead AND add to their code the potentially missing
commit() calls required by the new PEP 249 manual commit mode.
2. After the deprecation period:
- Set the value of the autocommit property to False by default, so that the new
PEP 249 manual commit mode becomes the new default.
- Remove the value None of the autocommit property and its deprecation warning.
- Remove the value None of the isolation_level property, so that the old manual
commit mode disappears.
----------
components: Library (Lib)
messages: 360732
nosy: ghaering, lemburg, maggyero, r.david.murray, zzzeek
priority: normal
severity: normal
status: open
title: Add an autocommit property to sqlite3.Connection with truly PEP 249
compliant manual commit mode and migrate
type: enhancement
versions: Python 3.8
_______________________________________
Python tracker <rep...@bugs.python.org>
<https://bugs.python.org/issue39457>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
