What are the viable database abstraction layers for Python

Question

I'm starting to get involved in an open source project Gramps which is exploring switching their backend from BSDDB to a relational database. Either SQLite or MySQL we haven't fully decided and may even try to do both in some limited capacity. I'm a professional developer but I'm new to python so I'm not that familiar with the current selection of tools/libraries. I've been tasked with researching DB Abstraction Layers. There is currently a wiki discussion going on to compare them. An object relational mapper might be nice but isn't absolutely necessary. though I know that is usually synonymous with a DB Abstraction Layer. If an ORM is included ad hock queries have to be available without to much wrestling.

Right now the list includes:

CouchDB I haven't yet looked into this.

DB-API this seems to be a standard python api and each db creates their own module that uses it. Even BSDDB seems to have one written but I haven't fully explored it. are the modules interchangeable?

SQLAlchemy This seems to be the most popular right now? but I have very limited exposure to the python world.

SQLObject I haven't yet looked into this.

So what are peoples views and suggestions on database abstraction layers for python?

Answer 1

Look very closely at SQLAlchemy.

You can test and develop with SQLite.

You can go into production with MySQL -- making essentially no changes to your applications.

The DB-API, while widely adhered-to, has enough flexibility that (1) you aren't insulated from SQL variation in the underlying RDBMS and (2) there are still DB driver-specific features that are hard to hide.

Another good ORM layer is the ORM that's part of Django. You can (with a little effort) use just the Django ORM without using the rest of the Django web framework.

Use an ORM Layer (SQLAlchemy or SQLObject) in preference to DB-API.

Why? Your model should be a solid, clear, well-thought-out OO model. The relational mapping should come second after the object model. SQLAlchemy makes this a reasonable approach.

A "DB Abstraction Layer" will happen in the normal course of events. Indeed, because of DB-API (as used by SQLAlchemy) you gave two abstraction layers: ORM and DB-API.

Answer 2

CouchDB is not a relational database, so it doesn't have a DB-API interface. It's a document database which means it wouldn't be as useful for Gramps because it would require some contortions to identify links between related people. On top of that it can only run in client/server mode.

Any ORM like SQLAlchemy, SQLObject, or the Django ORM are implemented on top of DB-API and I recommend using any of these over direct DB-API because it can give Gramps the flexibility to run sqlite in embedded mode for local desktop users and then also share, sometime down the road, a Postgresql/MySQL database connection with a web based version of Gramps.

Answer 3

Accessing a proper database from Python is almost always done using a DB-API 2.0-compliant adapter module. While all DB-API modules have identical APIs (or very similar; not all backends support all features), if you are writing the SQL yourself, you will probably be writing SQL in a product-specific dialect, so they are not as interchangeable in practice as they are in theory.

Honestly, SQLite sounds perfect for your use case. I wouldn't bother with "embedded MySQL"; that sounds like the worst of both worlds. Whether you want an ORM like SQLAlchemy is completely up to you; there are good arguments either way. Personally, I dislike ORMs, but then I have a math degree, so the fact that I appreciate SQL as a language probably isn't too surprising :)