Python/SQLite best practices

[David Raymond]

"What's the advantage of this over letting the connection object do
that for you? As the context manager exits, it will automatically
either commit or roll back. If you want to guarantee closing _as
well_, then you can do that, but you can at least use what already
exists."

After review I guess I should have phrased it more as a "here's what I've found for reference" rather than a "here's what _you_ should do"


Part of it is large use of the Command Line Interface for SQLite, and similar command line tools for other db's, which all work in autocommit mode by default, so that's how my brain is now wired to think about executing things.

The context manager transaction feature I can see using, and might actually start switching to it as it's explicit enough. Though oddly, __enter__ doesn't seem to actually begin a transaction, not even a deferred one. It's only __exit__ that either commits or rolls back.
(Eh, it'd "probably" be simple enough to subclass Connection so that __enter__ and __exit__ work properly no matter the isolation_level. Famous last words)

The implicit stuff I hated because it never seemed straightforward enough. Especially since there used to be implicit commits as well as implicit begins ("Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.") Maybe because I was new to both Python and SQLite at the time, but there was a lot of "stop doing hidden stuff I didn't tell you do" getting muttered, along with others like "why do I need to commit when I never did a begin?" The documentation on it is all of 1 sentence, so there was a lot of trial an error going on.
"The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE)."


"(Also, I'd definitely use conn.commit() rather than
cur.execute("commit"), in case there's extra functionality in the
commit method.)"

True. I know for example that if you try to rollback when not in a transaction that cur.execute("rollback;") will raise an exception whereas conn.rollback() will quietly suppress it for you. So there might be similarly useful stuff in .commit()
sqlite3 is (almost) all C though, so there'd be noticeably more digging and decyphering required to check. (For me anyway)