On Tue, Aug 6, 2019 at 5:05 AM David Raymond <david.raym...@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
--
https://mail.python.org/mailman/listinfo/python-list
