SQLAlchemy Db2 driver contribution

[Former user]

Hi, I'm developing sqlalchemy driver for Ibm Db2 and wish to contribute. It is based on SA 0.3.4 + PyDb2-1.1.0 + many patches. Currently I'm merging changes to SA 0.3.7 and doing more fixes to PyDb2. I plan to finish it to the end of week. There are several changes to std SA/lib and SA/test.

Questions: - is it good enough to merge changes with current release (0.3.7) or should I merge it to development (svn://...) - pydb2 project has weak activity (http://sourceforge.net/projects/pydb2) - I made some improvements/bugfixes to the module, so the question is what to do with that, is it ok to add my release to SF-pydb2 patch list?

I'm sending working copy of db2.py just for preview.

Regards from Croatia, Robert

[Former user]

db2 driver for SA - just for preview

[Mike Bayer]

Ok this is great. yeah you def. have to patch based on trunk, we refactored the Dialect/ExecutionContext interface between 0.3.6 and 0.3.7. trunk isnt changing so wildly on a day-to-day basis so it should be manageable.

any patches that are against pydb2 itself should go to the pydb2 list.

[Former user]

FYI: As you've probably noticed PyDB2 appears to be unmaintained at this time (the latest thing in CVS appears to be 18 months old), and has a number of bugs. An alternative which I've been using for a short while is to use a generic ODBC interface (possible because the CLI in DB2 is effectively ODBC). For example, via the ODBC support built into the PyWin32 extensions, or with pyodbc which seems to work happily under both Linux and Windows, and has a Windows binary distribution (something PyDB2 sadly lacks - and which currently prevents me from using it on one of our servers until/if I can figure out the compilation process).

I've found the "ODBC only" access route to be quite useful given that it's possible to connect to a DB2 database via ODBC without having to have it in the local catalog (i.e. without having to use CATALOG NODE or CATALOG DB). For example:

Python 2.4.4 (#1, May 19 2007, 20:32:06) 
[4.1.1 (Gentoo 4.1.1-b2f0d64fa8c06b5662ce6831dc3fe1588397c76b)](GCC) on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> conn = pyodbc.connect('DRIVER=DB2;PROTOCOL=TCPIP;HOSTNAME=myserver;PORT=60000;DATABASE=MYDB;UID=dave;PWD=XXXX')
>>> cursor = conn.cursor()
>>> cursor.execute('VALUES CURRENT DATE')
<pyodbc.Cursor object at 0xb7c48638>
>>> d = cursor.fetchall()
>>> d[0](0)[0](0)
datetime.date(2007, 7, 30)

Combined with the "lightweight" CLI/ODBC only driver (documented in the InfoCenter and available from this page) this can be a relatively simple way to access DB2 databases without the hassle (and weight!) of the full client.

Still, to the point: I've recently become interested in using one of the Python web application frameworks (probably Pylons) for development, but really need it to work on DB2. This driver looks like a great start and I'll certainly give it a whirl on a Linux box, but I also have a need for it to work on a Windows server (where, as mentioned above, I can't currently get PyDB2 working). Is there any chance you might consider a version using pyodbc? It might even prove a little easier than this driver given that pyodbc seems more "complete" (built in support for the datetime module, for example).

Anyway, if there's anything I can do to help the effort, please feel free to contact me (dave at waveform dot plus dot com), and thanks for the driver!

[Mike Bayer]

pyodbc is fine, we already use it for MS-SQL. you might want to look at it to see our current approach to supporting the same database with two different DBAPIs (this is an interim style)

at a later date, we will also be making a database-agnostic pyodbc (and mxODBC) library.

[Mike Bayer]

oh also we should probably be targeting 0.4 in the trunk.

[Former user]

Hi everyone,

it seems that I'm not to be trusted: i promised some things months ago and nothing is done. I'm very sorry for that but i'm having huge problem with my spare time. on my job i can't do such things, since we (still) don't use SA ... and my spare time became very limited ... some family obligations, recently i got second child etc. I don't really know when i'll be able to integrate my work with trunk, since i don't know how to do this, it will take much longer than to someone with experience.

The idea with pyodbc is IMHO good, and the same driver could work in 2 modes (something similar already exists, i think for oracle).

It is shame that one big IBM doesn't invest few hours per week to maintain/continue pydb2 driver, and for instance, this driver too. Maybe someone could send some suggestions to them, they can be interested since they advertise OSS ruby db2 driver used in ruby on rails ...

I would appreciate if someone help me, it will speed up the process. and after the first version of the driver is delivered, i'm affraid i won't be able to maintain it :( What do you suggest? Once more sorry for the inconvenience.

[Mike Bayer]

for reference, heres a link to a newer DB2 effort led by Antonio Cangiano (but also, not sure what the status is on it):

http://antoniocangiano.com/2007/09/04/python-django-and-db2-status-update/

[Mike Bayer]

looks like its a production product - here ya go: http://code.google.com/p/ibm-db/