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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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.
Method 2
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 🙂
Method 3
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.
Method 4
I really like Storm:
Storm is an object-relational mapper
(ORM) for Python developed at
Canonical. The project has been in
development for more than a year for
use in Canonical projects such as
Launchpad, and has recently been
released as an open-source product.
In my opinion, Storm is much easier to learn than SQLAlchemy. Is similar to Django’s ORM.
Method 5
web2py has a database abstraction layer that can be used in a standalone manner. We switched between sqlite3, Microsoft SQL Server, Oracle and MySQL with zero code changes. Impressed.
Method 6
When I started converting a legacy app to use an ORM I looked into SQLObject and SQLAlchemy. At first I went with SQLObject because it looked familiar (past Django experience) and SQLAlchemy seemed complicated. After about 2 hours I started to hit walls with SQLObject. I then looked at SQLAlchemy again and was instantly rewarded. Not only did it understand and map every weird table in the database, it even could do the even weirder lookups I had to do later!
Method 7
I think that CouchDB would be best choice for such project as Gramps.
Useful CouchDB features for Gramps:
- No schema, no migrations.
- Support for storing files directly to database, for example family photos, etc.: http://guide.couchdb.org/draft/api.html#attachments
- Ubuntu supports CouchDB via desktop-couch and it is integrated with Ubuntu One for easy sharing or backuping: http://www.freedesktop.org/wiki/Specifications/desktopcouch
- You can easily replicate couchdb databases, join them to one big database, etc…
- couchDB is very flexible.
Method 8
If your project will ever have any real complexity, stay away from ORMs.
http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0