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

[Peter Otten]

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.  I am
> 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?  If not, then the alternative would make more sense,
> i.e., open and close the db as needed.
> 
> 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.  My intent is to make
> 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?  With each
> pertinent method handling a very specific means of interacting with
> the db?  Or go a procedural route with functions similar to the
> aforementioned methods?  It is not clear to me that OOP provides a
> real benefit here, but, then again, I am learning how to OOP during
> this project as well, so I don't have enough knowledge yet to
> realistically answer this question.

Don't overthink your project. However thorough your preparations you will 
get about 50 percent of your decisions wrong.

Use version control to keep track of your code and don't be afraid to throw 
parts away that don't work out.

Implement a backup scheme for the database lest you annoy your wife by 
making her reenter data, and there you go. The database will be small for a 
long time, so it will be practical to make a copy every day.

Regarding database access:

(1) A single connection:

_db = None

@contextmanager
def open_db():
    global _db
    if _db is None:
        _db = sqlite3.connect(...)
        is_owner = True
    else:
        is_owner = False
    try:
        yield _db
    finally:
        if is_owner:
            _db.close()

(2) Multiple connections:

@contextmanager
def open_db():
    db = sqlite3.connect(...)
    try:
        yield db
    finally:
        db.close()

You can use both the same way you deal with an open file:

with open_db() as db:
   ...

I don't say you should use the above code, I just want to demonstrate that 
you can happily defer the answer to your connections question.

Regarding OO design in general: keep your classes small. You can't go wrong 
with fewer, smaller and more general methods ;)