Not a full expert, but some notes:
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() -----Original Message----- From: Python-list <python-list-bounces+david.raymond=tomtom....@python.org> On Behalf Of Dave via Python-list Sent: Monday, August 05, 2019 1:49 PM To: python-list@python.org Subject: Python/SQLite best practices I'm looking for some tips from experienced hands on on this subject. Some of the areas of interest are (feel free to add more): * Passing connections and cursors - good, bad indifferent? I try to avoid passing file handles unless necessary, so I view connections and cursors the same. Though that said, I'm not aware of any specific problems in doing so. For designs with multiple tables: * Better to pass an sql string to functions that create/add data/update/delete data and pass them to create, insert, update, delete functions; or have those functions for each table? Taking table creation for example, if there are five tables, and the sql string is passed, there would need to be six functions to do it, though the complexity of each function may be reduced a little. [table1create with sql and establishing a cursor, to table5create and then a function that executes the sql]. Best way to establish the connection and cursor, as well as close them? I have seen many ways to do this, and know that the with block can be used to create a connection and close it automatically, but the same is not true of the cursor. Also, using only a with block does not handle any errors as well as a try/with. For example: | try: | # Use with block to create connection – it will close self. | with sqlite3.connect(path) as conn: | cur = conn.cursor() | cur.execute(sql_ProjectsTable) | cur.close() | except Error as e: | print(e) What else? Dave, -- https://mail.python.org/mailman/listinfo/python-list -- https://mail.python.org/mailman/listinfo/python-list