SQLAlchemy and Informix advice

[Massimo Valle]

I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database.

Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries.

I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page.

Now I have a couple of newbie questions for the SQLAlchemy experts:

1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question.

2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use.

Thanks for help,

Massimo Valle

[Richard Gerd Kuesters]

Hi Massimo!

In the past, I have used SQLAlchemy to connect to Informix (using the db2 driver), but it was for pure lazyness -- I had to write everything by hand, and my application already was using SQLAlchemy, so ... But that was back in 2007. Those codes are long dead and not in my possession.

Let's see if I can address those problems better for you:

1. "Does anyone know if the Informix support is something will be added?": you can see all official dialects supported by SQLAlchemy in here: http://docs.sqlalchemy.org/en/rel_0_9/dialects/index.html

All supported dialects in SQLAlchemy have something in common (IMHO): SQL, obviously. Informix have support to SQL, of couse, but it also have a lot of other tools, spatial support, JSON and so on; that I may find hard see completely supported by SQLAlchemy. Of course, there are some engines that looks quite familiar to these, PostgreSQL, -but- I think it's support in SQLAlchemy is a completely different subject.

Are you planning to use SQLAlchemy ORM on top of Informix for common SQL tasks? If so, you can adapt - or even contribute - to the ibm_db_sa adapter :) Here's a good lecture: http://techspot.zzzeek.org/2012/10/25/supporting-a-very-interesting-new-database/ -- but, of course, the SQLAlchemy source code is the best way to know how dialects works under the hood.

If you're planning to use with the new JSON integration, boy I think you should stick to the roots somewhere, away from SQLAlchemy and close to Mongo's adapters (somewhere else I had bumped with MongoAlchemy, that provides a quite similar API to Mongo as SQLAlchemy offers to others RDBMs, but IMHO it looks like a huge waste of code time since SQL and NoSQL databases have just one thing in common: they store data somewhere).

2. "Does this mean I can only use SQLAlchemy Core features and not the ORM?": I don't know about that anymore, since everything I write to Informix (if and when applied) are not SQLAlchemy (or even Python) related, at all.

If you provide more information about what you're trying to accomplish, it may still be possible with SQLAlcheny, given some circumstances.


Best regards,
Richard.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

we’ve had a dedicated Informix dialect in the past, however I’m unable to maintain it because I don’t have any way to test it, it was contributed by someone many years ago.   The dialect was moved out to https://bitbucket.org/zzzeek/sqlalchemy-informixdb, and most of what’s needed for it to work should be there.  It might need some tweaks to get it going, e.g. with getting the setuptools entry point to be correct and all that, but overall it should have what’s needed for rudimental access.

Also I’m not sure why ibm_db_sa advertises informix support when this is not the case, can you elaborate on this?  do they support only connectivity but not actually the right SQL format?   

[Massimo Valle]

Thank you Richard,

about the 2nd question, sorry but I'm new to SQLAlchemy and trying to find a way, so using the ORM for SQL access to Informix is my goal. Following the tutorial, I found for the ORM I have to create a Session and my guess is for doing this, I need the SQLAlchemy module support through the ibm_db_sa module. At this time it spits an error because it expects a DB2 database (no Informix support). Or, is there another way to do it?

Unfortunately I have no time to contribute to the module for Infornix support, therefore I'm looking if there is a way to achieve my goal (that's using the ORM) as it's now.

thanks again for your help,

Massimo Valle

[Richard Gerd Kuesters]

Massimo, git it a try to the dialect that Mike passed you. I think it should give you a better start then using the ibm_db_sa module.

[Massimo Valle]

My problem with InformixDB (I tried it) is it seems too weakly supported/used, while I need something more reliable, sorry.

ibm_db seems more stable but the problem is with the "ibm_db_sa" adapter (SQLAlchemy module). My problem is when I try the "IBM_DB SA adapter sanity test" as from the project wiki (https://code.google.com/p/ibm-db/wiki/README). I always get this error:

ibm_db_dbi.DatabaseError?: ibm_db_dbi::DatabaseError?: SQLNumResultCols failed: IBM?Driver?[IDS/UNIX32] The specified table (sysibm.sysdummy1) is not in the database. SQLCODE=-206

The same happens when creating a Session() in SQLAlchemy. It seems the ibm_db_sa adapter thinks to DB2 only and try to query the sysibm.sysdummy1 system table, which is not present in Informix.

On the same wiki page I commented about this fact, and a project member (Rahul) (with an @ibm.com email address) answered:

"Informix is currently not supported for SQLALchemy's ibm_db_sa backend."

Btw, since I'm new to SQLAlchemy, I don't understand the full implications of this, and that was my question about the ability to use the ORM or just the Core features.

Thanks for help,

Massimo

[Michael Bayer]

Informix is a commercial product that is not cheap to keep running so I’d assume you’re working for a commercial entity with IBM support?  Perhaps you can get support from Rahul at IBM to either improve sqlalchemy-informixdb (or they can just take it) or the ibm_db_sa product ?  The improvement and modernization of the ibm_db_sa adapter in the first place was due to engagements with clients that IBM supports.   I have a relationship with Rahul to the degree that he can throw me questions as he needs and I’ve submitted patches to the project as well.