Python Object-Relational Mapping (Revised)

Ian Bicking: A Blog [link], provided some info on Py3K and Python Introspection [link].

For me, the interesting part was his summary of Object-Relational Mapping. Mr. Bicking identifies two broad approaches: lambda introspection and operator overloading.

Lamba Introspection
Dejavu It primarily uses a generic Data Mapper architecture. It is more of an OODB backed by a relational store.
SQLComp The make_query method examines a lambda containing a list comprehension to create SQL. This is only queries, and isn't a complete ORM.

Operator Overloading
SQLObject This is a very complete ORM, cast in the some mold as Django.
SQLAlchemy This provides a Pythonic definition of SQL metadata and a mapping from the SQL metadata to Python class definitions. This is a very, very rich approach, allowing you to straddle the SQL and Object worlds explicitly.
PyORQ This is an older ORM with a few data types but a very "naked" use of overloaded operators to perform queries. Unlike the lambda overloading, the class provides operators that are set operations for queries.

Non-Introspective Approaches
Django, for example, encodes attributes and operators as keyword parameters to methods. It doesn't look inside the Python code, but parses the keywords.
PyDO2 encodes the query explicitly, using functions that mirror SQL operators or tuples that contain string names for the functions.
QLime is an ORM with functional notation, similar to PyDO2.
ORM (the Object-Relational Membrane) mostly captures SQL metadata in Python.
DBClass is focused on an easy way to hack around with SQL queries (to get data from procedures and so on).
Axiom is an object database, or alternatively, an object-relational mapper. It depends on Epsilon.

The Python wiki page on Higher Level Database Programming has additional notes and products that are high level.

Garden-Variety Relational Access
All of these modules provide the standard DB-API (PEP 249) interface to a SQL database.

The most visible access layer product is mx.ODBC for bare ODBC connectivity. This has the advantage of wide portability, and the disadvantage of the narrow ODBC interface.

PDO wraps a variety of other access methods into a single, combined package. I'm not precisely sure why it adds another interface layer, but it appears to simply do away with Cursor objects. However, it does provide a nice list of DB-API 2.0 modules for direct SQL access.

MySQLdb for MySQL
PySQLite and APSW are for the ultra-lightweight SQLite RDBMS.
The PostgresPy project will address many PostgreSQL topics. PyGreSql (aka pgdb), psycopg, PoPy, bpgsql.
kinterbasdb Firebird and Borland's Interbase
pyDB2 DB/2
cx_Oracle Oracle
adodbapi Python access to the MS Windows ADO interface

The Python wiki page on Database Interfaces also has a list of these product-specific access modules.

Rule 1. Do development with SQLite. Why? Eschew Features. Focus on RDBMS for relational store, focus on Python for processing. Stored Procedures and Triggers are a product-specific mine-field. Once the model passes unit tests, move to another RDBMS that supports concurrent users.

Rule 2. For OLTP, use an OR-Mapping and stay away from naked SQL. However (and this is a big however) you will likely be supporting ad-hoc reporting through SQL-based report writers. There are two extemes. At one end is Deja-Vu, which may be too far from the underlying SQL. The other end begins with SQLAlchemy, which may expose too much SQL; ORM and DBClass may be too light on object features.

Rule 3. For OLAP, you have two kinds of applications. Some parts (like dimension conformance) can use an OR-Mapping because they are OLAP-like. For some loading, aggregation and extraction, use direct SQL drivers for the chosen product. For the large-volume fact-oriented loads, use the vendor-supplied bulk loader. Portability is not your concern.

Posted: Wednesday - April 12, 2006 at 10:37 PM

Technorati Tags:
Technorati Cosmos: Technorati Cosmos
Technorati Watchlist: Technorati Watchlist
Add this entry to:   digg   furl   reddit   YahooMyWeb