James Oldfield <[email protected]> added the comment:
There's some confusion here over what autocommit=True would do. I believe the
last three comments give three different interpretations! Géry said
conn.autocommit would change to False when I start a transaction with
execute("BEGIN"), Mike said it wouldn't (because it represents the driver's
state, not the engine's, by analogy with other DB API drivers), and Marc-Andre
says execute("BEGIN") wouldn't be allowed in the first place (or at least it
would issue a warning).
To reiterate, the ability to control transactions manually is already supported
in the sqlite3 driver, in the form of isolation_mode=None. My first request is
simply that **this ability continues to exist**. This functionality was
implemented deliberately - the original author of pysqlite recommended this
usage, and care has been taken over the years not to break it. Please do not
point out that this is not DB API compliant; I know that, and I just don't
care! So long as DB API compliant usage is _also_ supported, even the default,
that doesn't prevent this other mode from existing too. Many others are using
the mode, even if they are not commenters here, so I don't believe it is
feasible to break or remove this functionality, even if you're not a fan.
My second request was: feel free to rename this option from
"isolation_mode=None" to something else if you wish, but please don't call it
"autocommit=True" because that's just too confusing. I feel like the confusion
in the comments above justifies this point of view.
As I see it, that leaves two options:
Option 1: Suck it up and use autocommit=True as the option name. It's
confusing, but there's so much precedent that it has to be so. This is Mike
Bayer's suggestion (except he didn't say it was confusing, that's just my
commentary). I think that this option is only feasible if conn.autocommit only
refer's the driver's state, not the underlying engine's state, confusing though
that is i.e. once set to true it would *always* be true, even if a transaction
is started.
Option 2: Reserve autocommit=True for the underlying SQLite engine autocommit
mode. That means detecting when there's an attempt to use execute("BEGIN") or
similar, and then issuing a warning or error. It also means supplying some
other, third, option for what I'm asking (like today's isolation_mode=None).
Although option 2 is closer to what I originally requested, I do worry it means
that the non-DBAPI mode will appear unsupported and fall into neglect. If the
API for accessing it is to set autocommit=None, to mean legacy behaviour, and
then also isolation_mode=None to mean the type of legacy behaviour, then it
doesn't look like the most recommended API ever. And yet, for those that don't
care about DB API (which I imagine is most users of the sqlite3 driver), this
is probably the best API to use.
So I reluctantly agree that option 1, using autocommit=True, is actually best
overall. I would ask that there is at least a note in the documentation so that
it's clear this is allowed to work. Something like this:
If autocommit=True then the sqlite3 module will never automatically start
transactions. The underlying SQLite database engine operates in autocommit mode
whenever no transactions are active, so the net effect of this is to use
SQLite's autocommit mode [1].
Note that, when autocommit=True, the sqlite3 module will not intercept and
stop a statement to explicitly start a transaction, such as with
execute("BEGIN"). In that case, a transaction is started and the underlying
SQLite engine is no longer in autocommit mode. (The sqlite3 Connection object
will still report autocommit=True; this does not indicate that the SQLite
engine is autocommit mode, just that the sqlite3 module is not going to
implicitly start any transactions.)
The connection commit() and rollback() methods may be used for transactions
started explictly when autocommit=True, and the connection may be used as a
context manager, just as it can be when autocommit=False. If no transaction is
currently active then those methods silent pass with no effect.
[1]
https://sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
Side note: When I started down this rabbit hole several weeks ago, I repeatedly
came across the extremely confusing phrase "SQLite operates in autocommit mode
by default". It took me a while to realise that autocommit is not a flag that
it is possible to turn off on a connection *when you open it*. The text I used
above, "The underlying SQLite database engine operates in autocommit mode
whenever no transactions are active" was carefully chosen and I consider it to
be much clearer, regardless of whatever else ends up happening.
----------
_______________________________________
Python tracker <[email protected]>
<https://bugs.python.org/issue39457>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
