James Oldfield <james.oldfi...@cantab.net> 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 <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
