[issue39457] Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate
James Oldfield 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
<https://bugs.python.org/issue39457>
[issue39457] Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate
James Oldfield added the comment:
> Yes if you are talking about SQLite, the database ENGINE
I sure was! In this comment I will stick to saying either "SQLite engine" or
"sqlite3 driver" as appropriate, hopefully that will be clearer.
> But here I am talking about SQLite3, the Python database DRIVER
Yep, I was aware of that. I was trying to say, please don't use the word
"autocommit" in the sqlite3 driver when that word has a related but different
meaning in the SQLite engine.
> You do not issue BEGIN statements with database DRIVERS, they are issued
> implicitly, so that the manual mode is the default mode for database DRIVERS.
This sentence isn't literally true for several reasons (you say "you do not"
but I certainly do, you use of "with database drivers" is dubious, and you seem
to have causality in the wrong direction). I think there might be a bit of a
language barrier here, so I hope you don't mind if I leave this to one side.
> Cf. this Stack Overflow answer for more details:
> https://stackoverflow.com/a/48391535/2326961
I am fully, and painfully, aware of when the sqlite3 driver code will
automatically issue BEGIN statements to the engine. I have no need to read
StackOverflow answers about it, I have read the C source code to sqlite3 (and
pysqlite) directly. I spent more time than I care to admit recently doing that!
In fact that happened as a result of reading several confusing StackOverflow
answers about transactions (maybe I'll write my own and add to the confusion...)
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. This is fully and intentionally
supported by the sqlite3 driver, and the original author has said so and even
recommended. For example, let's look at this code:
conn = sqlite3.connect(path, isolation_mode=None)
conn.execute("INSERT INTO test (i) VALUES (?)", (1,)) # stmt 1
foo = conn.execute("SELECT * FROM test").fetchall()# stmt 2
conn.execute("BEGIN") # stmt 3
conn.execute("INSERT INTO test (i) VALUES (?)", (4,)) # stmt 4
bar = conn.execute("SELECT * FROM test").fetchall()# stmt 5
conn.execute("COMMIT") # stmt 6
Statement 1 and statement 2 execute using the SQLite engine's autocommit mode.
Statements 3 through to 5 execute in a single transaction and do *not* use the
SQLite engine's autocommit mode. (Technically statement 6 actually does use
autocommit because COMMIT uses the autocommit mechanism under the hood ... but
let's forget about that!)
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'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!
Now, maybe your reaction is that "autocommit mode *in the driver*" can have a
different meaning from "autocommit mode *in the engine*". Yes, it can, but that
doesn't mean it should! Please, just pick a different name! For example, say
"manual mode" (instead of autocommit=True) or "auto-start-transaction mode"
(instead of autocommit=False).
> No, you do not want that at the database DRIVER level. Because like Mike
> Bayer explained in issue #9924, this is not what other database DRIVERS do,
> and this is not PEP 249 compliant
The "that" you are referring to here was when I said that I prefer to set
isolation_level = None, like the above code snippet. Do not tell me that it is
not what I want; it certainly IS what I want! I do not want the sqlite3 driver
getting in the way between me and the SQLite engine. Many future users of the
sqlite3 driver are likely to feel the same way, and the API should allow that
to happen clearly.
--
___
Python tracker
<https://bugs.python.org/issue39457>
___
___
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue39457] Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate
James Oldfield added the comment:
If this ever gets implemented, "autocommit" would be a terrible name for it.
That word has a very specific meaning in SQLite, which is essentially the same
as "not in a transaction started with BEGIN ...". At the moment, if you want to
explicitly control when transactions start (a good idea considering how
confusing the current behaviour is) then you would set isolation_mode to None
and manually start a transaction with `execute("BEGIN")` - at which point you
are NOT in autocommit mode, until you commit or rollback. According to this
proposal, if I want manual control over transactions, I would set
`conn.autocommit = True`, even though I *don't* want autocommit mode (according
to SQLite's definition)!
--
nosy: +james.oldfield
___
Python tracker
<https://bugs.python.org/issue39457>
___
___
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
