Python/SQLite best practices

[David Raymond]

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.com at python.org> On Behalf Of Dave via Python-list
Sent: Monday, August 05, 2019 1:49 PM
To: python-list at 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