[Tutor] Truly generic database API

[Kent Johnson]

Smith, Jeff wrote:
> I'm looking for a truly generic database API in that the underlying DB
> could be text, XML, SQL engine, etc.
> 
> For instance, initially, the underlying database will probably be text
> files but we may at some point want to move to a real database server or
> possibly an XML file without having to recode all of the upper level
> access.

I don't know of any database API that spans even text files and a 
conventional database.

The broadest Python database API I know of is the standard Python DB-API 
which give mostly portable access to a wide variety of databases. You 
will still have to deal with differences in data types, differing 
options in the API, and different SQL dialects but you can use a 
database as light as SQLite or as high-powered as Oracle or SQL Server 
or (insert your favorite high-end database here).
http://www.python.org/dev/peps/pep-0249/
http://www.python.org/doc/topics/database/modules/

Object/relational layers like SQLObject and SQLAlchemy give you a 
higher-level API and more insulation from the variation between 
databases at the cost of a more limited selection of supported databases.
http://www.sqlobject.org/
http://www.sqlalchemy.org/

I think to get the level of portability you are asking for you will have 
to write your own domain-specific data access module. This is a good 
idea anyway - you don't want to be spreading SQL code all over your 
program, for example. Your first implementation might be built on text 
files, maybe using the csv module. Later you can write new 
implementations for other back ends. If you write unit tests for the 
module it will ease the transition to a new back end greatly.

I do question why you need such broad portability. Are you sure you 
can't start with something simple like SQLite with the option of MySQL, 
PostgreSQL or a commercial product later?

Kent