Database experiences in Python: Good or Bad?

[Steve Holden]

"Jeff Schedin" <greenbeard400 at yahoo.com> wrote ...
> I am thinking of getting into Python but before I take the plunge I
> wanted to get some opinions on how the database connectivity is
> actually working in python since the work I will be doing will be
> heavy on getting data from Oracle, MSSQL and DB2 databases off of all
> sorts of OS'.
>
Python should be reasonably helpful in such an environment.

> (Currently I am programming on an AS/400 in RPG which due to the
> integrated nature of OS/400 DB2 is native - no drivers I just declare
> the file and read or embed the SQL directly into the code. Needless to
> say all of this monkey business with drivers makes me a little
> nervous.)
>
Well, yes, but how would you handle multiple databases in RPG? Having no
choice makes your design decision easy ;-)

> I have looked at the DB SIG and think that if the drivers actually
> implement all of the features I will be fine.  Some of the drivers
> look a little scary to me - this could just be my perception of course
> - while others look like a lot of work (mxODBC - which looks good
> requires some compiling ?)  Some of my main concerns are that metadata
> be available and that the drivers are activly maintained either
> privately or by an active community.
>
mxODBC is available as a Windows binary installer, and I believe you can
also get binary distributions for other platforms. You don't say whether you
plan to migrate away from the AS/400 for the Python exercise. I wouldn't
mind betting that there isn't yet an AS/400 binary distribution. However,
having spent some time helping to port mxODBC to the Cygwin platform I can
say it's pretty clean code.

> It seems that the Open Source databases are fine in this area:
> PostgreSQL, MySQL etc  but what about DB2/Oracle/MSSQL?  I don't mind
> having to do a little extra coding I just want to be able to download
> a driver (I will even compile if the instructions are decent) that I
> know will work and that will evolve as the Database Vendors do.
>
There are at least two useful Oracle modules (DCOracle2 and cxOracle),  a
Sybase module, a DB2 module, and so on.  I don't really think you need worry
too much, though it's a pity that Microsoft are encouraging migration away
from ODBC (which is platform-independent) to OLEDB (which isn't, but
implements more general features).

> Java's JDBC api seems to be well supported by most databases so would
> Jython be a better approach?  Since databases are the heart of many
> projects I must assume that things working fine but from my basic
> research (ok it was just ala google) I don't seem too confident in
> Python's DB-API compliance or support.
>
You don't need Jython, and you should be aware that it doesn't implement
quite a few of the Python library modules which tend to be taken for granted
in the CPython world.

> Perhaps I am just thinking about the whole topic incorrectly (it sure
> feels like it).  I mean don't most DBs expose an API which these
> drivers are then just coded against?  All wisdom appreciated.
>
Each database does indeed have its own, usually proprietary, CLI (Call-Level
Interface). Accessing this from Python would be a nightmare of inconsistency
if you had to deal with several database platforms, which is why ODBC was
invented: it provides a standardised way of interacting with multiple
database products. It's also why the DB SIG devised the DB API.
>
> Opinions?
>
It's perfectly possible to write multi-database code in Python. The biggest
problems come about because different modules can use different styles for
query parameterization (for instance, where mxODBC requires "SELECT * FROM
tbl WHERE myField=?", MySQLdb requires "SELECT * FROM tbl WHERE
myField=%s"), though this relates back to the databases themselves, and
would be difficult to overcome at module level.

If ODBC will let you do everything you need to with databases, and if all
your required DBI platforms have ODBC drivers, and if you don't mind paying
a license fee for commercial use, I'd recommend mxODBC. Otherwise, mixing
and matching modules with database platforms is not much more troublesome.

Since I recently saw myself described as an "opinionated language bigot",
opinions come with the standard disclaimer: "caveat emptor". Hope this
helps.

regards
-----------------------------------------------------------------------
Steve Holden                                 http://www.holdenweb.com/
Python Web Programming                http://pydish.holdenweb.com/pwp/
-----------------------------------------------------------------------