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?
解决方案
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.