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:
| # Use with block to create connection ? it will close self.
| with sqlite3.connect(path) as conn:
| cur = conn.cursor()
| except Error as e: