Python/SQLite best practices

[Chris Angelico]

On Wed, Aug 7, 2019 at 2:28 AM Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>
> On Mon, 5 Aug 2019 20:12:27 +0200, Karsten Hilbert
> <Karsten.Hilbert at gmx.net> declaimed the following:
>
>
> >Transactions involving several commands may require passing
> >around of connections and/or cursors, however.
> >
>
>         Probably both -- as I recall, DB-API spec is that .commit() is done on
> the connection, not the cursor. Though I'd prefer to put transaction
> control at a single higher level
>
>         create connection
>         create initial cursor
>         create transaction (though DB-API makes this tricky -- typically this
> occurs on the first DML request that modifies data, but not for mere
> SELECTs)
>                 do stuff with cursor, maybe passing connection if "stuff" needs
> secondary cursors
>         COMMIT or ROLLBACK based on return from "do stuff"
>

That would only be useful if the underlying database is capable of
multiple independent transactions on a single connection, and would
just get in the way otherwise. Some databases support a form of
"nested transactions" where you set a savepoint and then have the
option to either release the savepoint ("commit") or rollback to the
savepoint; but releasing a savepoint doesn't actually commit anything,
and the overall transaction still controls everything. My usual idiom
with databasing code is to create a single connection (or maybe a pool
if I need concurrency) and then use "with conn, conn.cursor() as cur:"
to create a cursor and set up a transaction, all at once. At the end
of that block, the cursor is disposed of, and the transaction
committed/rolled back.

ChrisA