[DB-SIG] Database Abstraction in Python

[James Gardner]

Hi Tom

I've been working on a database abstraction layer for Python for a few months which I think you might find useful. I with Marc-Andre agree that it can be tricky to create a database abstraction layer because of the differences in SQL syntax, data typing etc but I published a specification to this list in December for what I considered to be the best compromise between providing database portability but also providing enough functionality to be useful. I've since implemented the specification as the web.database module of the python web modules. 

Chris Cogden Wrote:
> "any attempt to create a abstraction system that covers all application 
> requirements AND all database implementations is doomed to failure, or 
> mediocrity."

Well, see what you think of this one.. it doesn't cover ALL requirements but I and others using the python web modules have used it in commercial projects and found it to be useful.

It supports SQLite, MySQL and has a pure Python implementation as a fallback if you don't have a database engine (easyish to support another database). It supports common data types, most basic SQL operations including joins, foreign and primary keys, converts the data to and from the appropriate format and has an object relational mapper built on top of it to treat an SQL database as a series of dictionaries (web.database.object). You can execute the portable SQL dialect specified directly or use Python methods to build queries. It supports a DB-API 2.0 style interface and parameter substitution. It differs from ADOdb which only provides abstraction in the methods used to execute SQL. The SQL you write is guaranteed to run exactly the same on any supported database since information on the table structures is stored as a table in the database and conversions are done transparently. You can also access the underlying DB-API 2.0 cursor if you want to. 

I planned to release it as a stand-alone package in the next few weeks but since the topic has come up it makes sense to mention it on this list now. It isn't properly packaged up and is documented for use in the web modules so be prepared for a bit of work.

James


Links
-----

Original Specification:
http://www.pythonweb.org/projects/pdbc/html/

Download:
http://www.pythonweb.org/projects/webmodules/

Documentation:
http://www.pythonweb.org/projects/webmodules/doc/0.5.3/html_multipage/lib/module-web.database.html
http://www.pythonweb.org/projects/webmodules/doc/0.5.3/html_multipage/lib/object.html