Python/SQLite best practices

[Chris Angelico]

On Tue, Aug 6, 2019 at 5:05 AM David Raymond <David.Raymond at tomtom.com> wrote:
> I believe the default Connection context manager is set up for the context to be a single transaction, with a commit on success or a rollback on a failure. As far as I know it does NOT close the connection on exiting the context manager. That only happens automatically when it's getting garbage collected/going out of scope/correct terminology that I can't seem to remember.
>
>
> For transactions and general use I vastly prefer using "isolation_level = None" when creating my connections, and then explicitly issuing all begin, commit, and rollback commands with cur.execute("begin;"), conn.commit(), conn.rollback() etc.
>
>
> contextlib.closing() can be used to wrap cursors for use with with
> (and also connections if they are created with isolation_level = None)
>
> with contextlib.closing(sqlite3.connect(fi, isolation_level = None)) as conn:
>     conn.row_factory = sqlite3.Row
>     with contextlib.closing(conn.cursor()) as cur:
>         cur.execute("begin;")
>         stuff
>         conn.commit()
>
>
>
> Normally though my stuff tends to look like the below (for better or for worse):
>
> conn = sqlite3.connect(fi, isolation_level = None)
> try:
>     conn.row_factory = sqlite3.Row
>     with contextlib.closing(conn.cursor()) as cur:
>         cur.execute("standalone query not needing an explicit transaction;")
>         stuff
>         cur.execute("begin;")
>         multiple queries that needed the explicit transaction
>         stuff
>         cur.execute("commit;")
> except something bad:
>     blah
> finally:
>     conn.rollback()
>     conn.close()
>

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.

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

ChrisA