python + ODBC + Oracle + MySQL - money

[Gerhard Häring]

Grig Gheorghiu wrote:
> In my testing, I need to connect to Oracle, SQL Server and DB2 on
> various platforms. I have a base class with all the common code, and
> derived classes for each specific database type using specific database
> modules such as cxOracle, mxODBC and pyDB2. The derived classes are
> pretty thin, containing only some syntax peculiarities for a given
> database type. The code is clean and portable.

So maybe you're lucky that all your database modules use the same access 
to query parameters. MySQLdb and cx_Oracle would be different in that 
MySQLdb has paramstyle = "format" and cx_Oracle has paramstyle = 
"qmark/named", i. e. to query a specific record of the person table you 
would use

p_id = 4711
cur.execute("select firstname from person where person_id=%s", (p_id,))

using MySQLdb, and:

cur.execute("select firstname from person where person_id=?", (p_id,))

using cx_Oracle.

Now, probably a lot of people have written wrappers for DB-API modules 
that translate one paramstyle to the other. The most sensible solution 
is to translate the format/pyformat one to others.

Often, one other solution is to use a higher-level database interface 
uses your database modules internally, but has the same consistent 
interface for the outside.

In my recent evaluations, I liked PyDO2 for this 
(http://skunkweb.sourceforge.net/pydo2.html). Unlike SQLObject, it is 
able to use MySQL and Oracle now, though there is work underway to add 
Oracle support to SQLObject.

OTOH, the advice to use MySQLdb and cx_Oracle directly is probably a 
good one, especially for a newcomer to Python. It's a good way to learn 
Python and learning the Python DB-API is a good idea if you want to do a 
database application in Python. You can use higher-level interfaces (or 
write them yourself) later on.

And if this is serious work with business value, then just buying a 
mxODBC license and go on with the real problem is probably the most 
sensible solution.

You can use the time saved for learning Python, then, which is perhaps 
more fun :-)

Cheers,

-- Gerhard