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

[Alan Gauld]

On 27/08/15 01:11, 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.

That's the usual approach with Sqlite. Remember it is just a single
file so as soon as you open it it is locked so other users can't
access it. But that's not going to be a problem for your app, at
least in the early days.

Of course keeping any file open for extended periods carries a
risk of corruption so you may want to implement an auto store/copy
regime so that there is always a recent backup. But if the app is
only being used for a few minutes at a time then it might be
overkill.

> assuming that opening and closing a db connection has enough overhead
> that I should only do this once.  But I do not *know* that this is
> true.  Is it?

Its a good habit to get into. In fact Sqlite doesn't take too much work 
to open because its just a file but once you get into server databases 
its a much bigger overhead. So I'd just treat Sqlite as another database
in that regard.

> In the first iteration of my project, my intent is to create and
> populate the db with tables external to the program.  The program will
> only add entries to tables, query the db, etc.  That is, the structure
> of the db will be pre-set outside of the program, and the program will
> only deal with data interactions with the db.

Yes, that makes sense.

> the overall design of the program OO, but I am wondering how to handle
> the db manager module.  Should I go OO here as well?

I'm not clear what exactly you see the db manager module doing?
Is this the admin module? The data loader that sits outside the app?
Or a module within the app used by the objects?

For admin (assuming a CLI)  I'd personally stick with procedural.

For data loader I'd stick with procedural and pure SQL.

For the main app I'd build a very thin procedural API over
the individual SQL queries and then let each model class
handle its own data access via that API. The API is then all
you need to change if that database changes.
OR go with a third party ORM (more to learn for little gain IMHO).


hth
-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos