How should I build a simple database package for my python application?

Question

I'm building a database library for my application using sqlite3 as the base. I want to structure it like so:

db/
    __init__.py
    users.py
    blah.py
    etc.py    

So I would do this in Python:

import db
db.users.create('username', 'password')

I'm suffering analysis paralysis (oh no!) about how to handle the database connection. I don't really want to use classes in these modules, it doesn't really seem appropriate to be able to create a bunch of "users" objects that can all manipulate the same database in the same ways -- so inheriting a connection is a no-go.

Should I have one global connection to the database that all the modules use, and then put this in each module:

#users.py
from db_stuff import connection

Or should I create a new connection for each module and keep that alive?

Or should I create a new connection for every transaction?

How are these database connections supposed to be used? The same goes for cursor objects: Do I create a new cursor for each transaction? Create just one for each database connection?

Answer 1

I know this doesn't really answer the actual question you asked, but the real answer is that you probably should not implement your own database package. You should probably use an existing one (e.g. SQLALchemy) and then use whatever pattern is standard for that library.

If you really want to do your own then the best approach is going to depend on a lot of factors, e.g. Is the project sure to only ever need a connection to one database?

If it's a fairly simple application I think importing a global connection object is probably the way to go. You can always swap it out for a connection pool behind the scenes, etc.

Answer 2

No, don't spread a connection over several modules - this is bad design. Have a single class handle the DB connection and provide services to other classes/modules in your application.

This isn't different from non-DB-related good design principles. A connection is a global resource. Sharing that resource over many modules is akin to having a global variable accessible from many places - which is by default a bad thing (unless you have a very compelling reason, but you don't). Encapsulate the global resource in a class to handle it.