Database abstraction layers comparison

From Gramps

This page if for a comparison of python database abstraction layers. it was started to help with GEPS 010: SQL Backend. and is specific for how they might be used for GRAMPS.

This question was asked on StackOverflow.

  • While these comparisons are interesting, this is a little bit like asking "what car should I buy?" That is, it really depends on a lot of details that none these suggestions take into account. In addition, projects are summarized in a brief (and therefore unrealistic) fashion. But, it at least serves as a starting point to further examine these projects. Thanks! --Dsblank 16:51, 2 January 2010 (UTC)

CouchDB

http://code.google.com/p/couchdb-python/

Pylons took the time to be able to use it.

This is not a db abstraction layer and not even a relational db.

DB-API

http://wiki.python.org/moin/DatabaseProgramming/

Python has an API to make it easy to move from one SQL-based DB to another called DB-API. Each DB may have multiple different modules available for it. If we settle on this solution then we should do some quick searches to make sure we pick the right modules.

  • MySQL: Yes MySQLDB used by SQLAlchemy
  • SQLite: Yes sqlite3 (included in Python 2.5 or greater) pysqlite both used by SQLAlchemy
  • BSDDB: No. The DB-API looks to be only for relational dbs.
  • ORM (Object Relational Mapper): no

"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." kquinn

Django

http://www.djangoproject.com/

Django also provides DB independence, but is geared towards web deployment:

"Django developed its ORM (and template language) from scratch. While that may have been a pragmatic decision at the time, Python now has SQLAlchemy, a superior database layer that has gained a lot of momentum. Django’s in-house ORM lacks multiple database support, and forces constraints on your database models (e.g. that every database table must have a single, integer primary key). If you choose Django, your project gains a near-inseparable dependency on Django’s ORM and database requirements." marcus cavanaugh 2009

Django's DB abstraction probably isn't a good fit. While powerful I doubt any projects are using it outside of Django.

pydo

pydo

doesn't look viable and looks like limited users and documentation. tied to a webframework.

SQLAlchemy

http://www.sqlalchemy.org/

  • MySQL: Yes
  • SQLite: Yes
  • BSDDB: No.

supported dbs

  • ORM (Object Relational Mapper): yes but doesn't force it.
  • mulitple database support?: yes source
  • Viability: Last release was January 24, 2009. They seem to have an established development team and user base. Project appears to be 3 years old,

"SQLAlchemy is designed to operate with a DB-API implementation built for a particular database" source

"SQLAlchemy, widely considered to be the best Python ORM available. SQLAlchemy includes multiple database support and just about any crazy combination of database requirements needed, and it handles ORM very well — yet it also allows you to provide raw SQL as needed." marcus cavanaugh 2009

SQLObject

http://www.sqlobject.org/

  • MySQL: yes
  • SQLite: yes
  • BSDDB: no

requirements

  • ORM (Object Relational Mapper): yes this is what sqlobject is all about.
  • mulitple database support?: ?
  • Viability: Last release was 2008-12-08, 7 developers on the project. couldn't find old release dates but first was posted 2003-04-09. link to wiki is broken which isn't the best sign but we've all had those times before...

comparison

Pylons took the time to be able to use it.

Storm

https://storm.canonical.com/

  • MySQL: yes
  • SQLite: yes
  • BSDDB: no
  • ORM (Object Relational Mapper): yes
  • mulitple database support?: yes
  • Viability: currently developed. seems like a fairly good site with some documentation. The bigest drawback is that the project is only a year old. a pro is that it may be easier to use than sqlalchemy.