views:

826

answers:

6

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?

+11  A: 

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.

S.Lott
+3  A: 

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 :)

kquinn
well apparently I cant vote yet but I agree with you about liking sql
AaronS
+1  A: 

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.

Van Gale
A: 

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.

Manuel Ceron
thanks for the heads up. I checked it out but like you said the projects only a year old. I as recently burned by picking a fringe tech that sounded good for my tech stack but wasn't so I'd prefer to recommend something more main stream.
AaronS
A: 

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

Trey Stout
Interesting article. Heavy-handed in it's "this can never be made to work" (which is demonstrably untrue) but an interesting list of points.
S.Lott
I have to agree interesting but a bit extreme. As I haven't used a true orm before I can't say for certain how painful they might become but it appears people are liking them and making them work.
AaronS
Well he has pretty good evidence why they won't work. It's a failure to understand relational algebra. Objects <> Rows. ORMs are a horrible crutch that teach laziness and lead to more complexity than just the SQL itself. Most people never have to worry about ACID compliance, so ORMs are ok for them
Trey Stout
A: 

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!

pi