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

Peter Otten __peter__ at web.de
Thu Aug 27 09:53:25 CEST 2015

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

def open_db():
    global _db
    if _db is None:
        _db = sqlite3.connect(...)
        is_owner = True
        is_owner = False
        yield _db
        if is_owner:

(2) Multiple connections:

def open_db():
    db = sqlite3.connect(...)
        yield db

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 ;)

More information about the Tutor mailing list