Best python module for Oracle, but portable to other RDBMSes

[Magnus Lycka]

Jonathan Gardner wrote:
> On database portability...
> 
> While it is noble to try to have a generic interface to these
> libraries, the end result is that the databases are always different
> enough that the interface just has to work differently. 

Considering the use case in question...

"What I'd like to do is use Python to access an Oracle 9.X database for
exporting a series of tables into one aggregated table as a text file,
for import into a mainframe database."

...it certainly seems reasonable to achieve this without too much
modifications between database engines. The problem I see directly
is if it uses the system tables to figure out what to export, but
if it doesn't, I don't forsee any big problems. There are even
ebcdic codecs in Python! :)

Read the DB-API 2 spec well. You might also want to look at
http://www.thinkware.se/cgi-bin/thinki.cgi/DatabaseProgrammingWithPython

I guess the main differences would be the connect string and
parameter passing.

Using ODBC (e.g. via mxODBC) should remove these problems.
Assuming that you use a subset of SQL which is supported by all
your engines (seems reasonable for this use case) it should be
enough to change ODBC data source to select data from either
Oracle or some other server.

Once upon a time, ODBC meant a significant performance penalty.
I don't know if that is still true.

Note that mxODBC has a licence that doesn't allow it to be used
freely in commercial contexts.

There is also an ODBC driver in the Python Windows extensions,
but I don't think it's been actively developed for many years.
It's not complient with DB API 2. There is another DB API 2
driver for ODBC sources on Windows called adodbapi, but in my
experience, it's slow and has problems with unicode strings in
its error handling.