[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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?