[Tutor] How should my code handle db connections? Should my db manager module use OOP?

[Steven D'Aprano]

On Wed, Aug 26, 2015 at 07:11:42PM -0500, boB Stepp wrote:

> My ongoing project will be centered around an SQLite db.  Since almost
> all data needed by the program will be stored in this db, my thought
> is that I should create a connection to this db shortly after program
> startup and keep this connection open until program closure.

If you do this, you will (I believe) hit at least three problems:

- Now only one program can access the DB at a time. Until the first 
  program closes, nobody else can open it.

- Your database itself is vulnerable to corruption. SQLite is an easy to 
  use database, but it doesn't entirely meet the ACID requirements of a 
  real DB.

- If your database lives on a NTFS partition, which is very common for
  Linux/Unix users, then if your program dies, the database will very 
  likely be broken.

I don't have enough experience with SQLite directly to be absolutely 
sure of these things, but Firefox uses SQLite for a bunch of things that 
(in my opinion) don't need to be in a database, and it suffers from 
these issues, especially on Linux when using NTFS. For example, if 
Firefox dies, when you restart you may lose all your bookmarks, history, 
and most bizarrely of all, the back button stops working.


-- 
Steve