Python/SQLite best practices

[Chris Angelico]

On Tue, Aug 6, 2019 at 7:45 AM David Raymond <David.Raymond at tomtom.com> wrote:
> 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)
>

Easier just to leave the isolation level and let it automatically
begin. (This is another reason to use the commit and rollback methods,
as they may flag the connection as "hey, remember to begin before the
next query".)

> 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.
>

I grew up on DB2 5.0 (after working in PC File and dbase), and you
never did a BEGIN TRANSACTION unless you wanted to set specific
parameters, but always had to COMMIT/ROLLBACK. The database itself
would automatically open a transaction as soon as you do any query,
and leave it open till you're done. So to me, that was never a
problem.

ChrisA