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?

Festus

Answer 1

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.

Hooten

a very elegant expression of a too-often ignored precept. – Obsession

so have one global connection but wrap it in a class? That doesn't seem much different than having one global connection but putting it in a module... Is it a bad thing to have each module starting their own connections? – Festus

nevermind, after some reading I found out that two connections to the same database don't work so well :/ – Festus

@Carson: the object encapsulates the connection, providing users with some uniform interface. If you decide to change the connection or the DB sometime in the future, the user code will have to be changed minimally – Hooten

You don't need to have a global connection. For example, if you have db.get_connection(), that method could choose whether to return the same connection object used by other calls to db.get_connection(), or a new one. Then you could maintain a connection pool etc. – Lamblike

you're right, although the sql may need changing. In any case, I've created this class that has a single connection, and hands out cursors rather than multiple connections, as sqlite3 2.4.1 doesn't like multiple connections (it locks the db) – Festus

Answer 2

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.

Pulsate

it is a fairly simple app, all this package is doing is giving names to a bunch of queries, really. I was just unsure of when to create database connections. – Festus

+1 for suggesting SQLAlchemy, even for a simple app it might be a good idea, it makes writing database queries so much more Pythonic :) It also internally implements connection pooling and per-thread scoped session handling so you don't have to worry about these things. Just make a session when you need it and it will handle the gory details and optimizations. – Monophthong