SA and IBM DB2

[Luca Lesinigo]

Hello there. I'd like to use SQLalchemy with an existing db2 database
(I can already access it with plain SQL using pyODBC from a python-2.6/
win32 system).

Googling around, I found http://code.google.com/p/ibm-db and it seems
to have an updated DB-API driver for python-2.6/win32, but the latest
SA adapter is for sqlalchemy-0.4.

Is there any way to access DB2 from sqlalchemy-0.6 or -0.7?
If that helps, I'm gonna use it in read-only (ie, no INSERT, UPDATE,
DELETE queries will be issued nor would they be accepted by the db)

thanks, Luca

[Michael Bayer]

A project I'd like to take on at some point, or to get someone else to do it, would be to write a modernized SQLAlchemy 0.7 dialect for DB2, where we would use DB2's DBAPI, but not their SQLAlchemy dialect which is out of date and they appear to not be doing much with. I'd write a new dialect rather than porting/looking at the one IBM wrote just so there's no potential licensing issues. The new DB2 dialect would live with all the other dialects under the SQLAlchemy project itself.

I understand DB2 has a free "express" edition so it would be a matter of getting that going and working out the dialect. Dialects aren't too hard to write so we do get them contributed, but for the moment we don't have a DB2 story for modern SQLAlchemy versions.

>
> thanks, Luca
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Bastian]

Hi Luca,

On Jun 29, 12:43 pm, Luca Lesinigo <l...@lesinigo.it> wrote:
> Hello there. I'd like to use SQLalchemy with an existing db2 database
> (I can already access it with plain SQL using pyODBC from a python-2.6/
> win32 system).

You could also try to use http://pypi.python.org/pypi/JayDeBeApi/
Currently it is quiet slow on cPython though. The advantage is that
you can run the same code on Jython (where it runs already quiet
fast).

> Googling around, I foundhttp://code.google.com/p/ibm-dband it seems

> to have an updated DB-API driver for python-2.6/win32, but the latest
> SA adapter is for sqlalchemy-0.4.

If you google further you should find an (unsupported) patch for
sqlalchemy 0.6 (at least).

I created JayDeBeApi with the aim to be able to use DB2s JDBC drivers,
although you could use any database / JDBC driver. The next step I was
planning was to create a SQLAlchemy JayDeBeApi connector and a DB2
Dialect for that connector. If you're interested, please jump in and
help to improve JayDeBeApi and to create a SQLAlchemy connector/
dialect :)

Regards

Bastian

[Christian Klinger]

Hi Michael,

i am intrested in writing a dialect for DB2. Is there any howto which
covers what is needed to start. Do you think we should write an
extension, or should this dialect in sqlalchemy itself?

Thanks in advance
Christian

[Michael Bayer]

On Jul 6, 2011, at 11:19 AM, Christian Klinger wrote:

> Hi Michael,
>
> i am intrested in writing a dialect for DB2. Is there any howto which covers what is needed to start. Do you think we should write an extension, or should this dialect in sqlalchemy itself?

first off, HOORAY, secondly, this would be a dialect within SQLAlchemy itself under sqlalchemy.dialects.

Here are the two files we would need:

sqlalchemy/dialects/db2/base.py
sqlalchemy/dialects/db2/ibm_db.py

So in "base.py", the base dialect classes, things that deal with the kind of SQL that DB2 deals with. Preferably no details that are specific to the DBAPI. In ibm_db.py is where things that are specific to IBMs DBAPI are present. At some later point, if for example pyodbc could also connect to DB2, we'd add a "pyodbc.py" file there.

Then to do what's in base.py, ibm_db.py, you need to emulate what's in all the other dialects. Some smaller ones to look at are firebird, sybase. More involved are mssql, postgresql, oracle. The MySQL dialect is good too but that one is particularly complicated due to a lot of difficulties MySQL presents.

When I write a new dialect from scratch, the first thing I do is just to get it to run at all, which usually means a script like this:

e = create_engine('db2:ibm_db://scott:tiger@localhost/test')
c = e.connect()
print c.execute('SELECT 1').fetchall()

That's pretty much "hello world". You might try to work with a few variants of "hello world" just to get things going.

Then, you can start moving onto the actual tests. This is also an incremental process, and I usually start with test/sql/test_query.py which tests basic round trips. The last section of README.unittests has several paragraphs on how to test new dialects and includes an overview of which tests to start with.

[Christian Klinger]

Hi Michael,

thanks for input. If i find some time i will start...

Christian

[Cornelius Kölbel]

Hello,

one year gone and I wonder if there might be any news on the question of
sqlalchemy and DB2.

The previous patches of ibm_db_sa
(https://bitbucket.org/jazle/ibm_db_sa/downloads) do not exist anymore.

But the current ibm_db_sa 0.1.6 produces an import error at
from sqlalchemy import logging

(at least at the 0.1.6 version from pypi)

So what would be the current status of sqlalchemy and DB2?

Thanks a lot and kind regards
Cornelius

[Michael Bayer]

no news here !

we're still entirely open to someone willing to take the initiative on this one.