State of using SQLAlchemy with Informix

[Daniel Popowich]

I am consulting for a company that has an Informix installation, v 11.70.

After much (MUCH) pain, I was able to connect to Informix with both ibm_db and the DB API layer, ibm_db_dbi.

Jumping through other hurdles, I was able to get SQLA connected with ibm_db_sa.

After that, hell.  Most of the SQL generated by sqla ends in syntax errors or other, opaque errors next to imossible to diagnose.  To name a few specifics, I have Boolean fields not mapping to proper values for Informix ('0', '1' instead of 't', 'f'), None values not mapping to null, bizzarre opaque errors that otherwise work in dbaccess when I run the SQL I *think* is being generated.

Before I get into nitty gritty with code, stack traces, etc., let me ask this:  is this doable?  Is SQLAlchemy<->ibm_db_sa<->ibm_db_dbi<->ibm_db<->Informix production worthy?

Python 3.4.3
SQLAlchemy==1.0.13
ibm-db==2.0.7
ibm-db-sa-py3==0.3.1-1
Informix 11.70

[hemlatabhattibm]

Hi Daniel ,

I am sorry for the inconvenience caused.Currently ibm_db_sa is not supported for python3x version.We are working actively to provide the support .Can you please try a 2.x version of python?
ibm_db_sa supports the maximum SQLAlchemy version 0.9.10.Please use the recommended version.

Regards
Hemlata

[Daniel Popowich]

On Wednesday, June 1, 2016 at 12:33:31 AM UTC-4, hemlatabhattibm wrote:

Hi Daniel ,

I am sorry for the inconvenience caused.Currently ibm_db_sa is not supported for python3x version.We are working actively to provide the support .Can you please try a 2.x version of python?
ibm_db_sa supports the maximum SQLAlchemy version 0.9.10.Please use the recommended version.

Hemlata,

Thanks for the suggestion of trying python2 version of ibm_db_sa, but I seem to be in a worse off place.  Given the following simple SQLA model, which uses declative base:

class User(Base):
   """Sqlalchemy model to represent a User of the system."""

   __tablename__ = 'user'

   id = Column(Integer, primary_key=True)
   username = Column(String(255), nullable=False, unique=True)
   passwd = Column(String(255), nullable=False)
   disabled = Column(Boolean, nullable=False, default=False)
   last_login = Column(DateTime)

And this simple query (dbsession is a SQLA scoped session):

>>> dbsession.query(User).all()

I get the following exception:

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

Looking at the source for ibm_db_sa, it has several references to sysibm.sysdummy1, which I believe is DB2-specific.

So, again I ask: can one use ibm_db_sa with Informix?

The above happens with:

python 2.7
Informix 11.70
ibm-db==2.0.7
ibm-db-sa==0.3.2
SQLAlchemy==0.9.10

Full stack trace is below:

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in all(self)
   2305
   2306         """
-> 2307         return list(self)
   2308
   2309     @_generative(_no_clauseelement_condition)

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __iter__(self)
   2423         if self._autoflush and not self._populate_existing:
   2424             self.session._autoflush()
-> 2425         return self._execute_and_instances(context)
   2426
   2427     def _connection_from_session(self, **kw):

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   2436             mapper=self._mapper_zero_or_none(),
   2437             clause=querycontext.statement,
-> 2438             close_with_result=True)
   2439
   2440         result = conn.execute(querycontext.statement, self._params)

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _connection_from_session(self, **kw)
   2427     def _connection_from_session(self, **kw):
   2428         conn = self.session.connection(
-> 2429             **kw)
   2430         if self._execution_options:
   2431             conn = conn.execution_options(**self._execution_options)

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in connection(self, mapper, clause, bind, close_with_result, execution_options, **kw)
    879         return self._connection_for_bind(bind,
    880                                          close_with_result=close_with_result,
--> 881                                          execution_options=execution_options)
    882
    883     def _connection_for_bind(self, engine, execution_options=None, **kw):

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in _connection_for_bind(self, engine, execution_options, **kw)
    884         if self.transaction is not None:
    885             return self.transaction._connection_for_bind(
--> 886                 engine, execution_options)
    887         else:
    888             conn = engine.contextual_connect(**kw)

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in _connection_for_bind(self, bind, execution_options)
    325                         "given Connection's Engine")
    326             else:
--> 327                 conn = bind.contextual_connect()
    328
    329         if execution_options:

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in contextual_connect(self, close_with_result, **kwargs)
   1906
   1907         return self._connection_cls(self,
-> 1908                                     self.pool.connect(),
   1909                                     close_with_result=close_with_result,
   1910                                     **kwargs)

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in connect(self)
    340         """
    341         if not self._use_threadlocal:
--> 342             return _ConnectionFairy._checkout(self)
    343
    344         try:

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _checkout(cls, pool, threadconns, fairy)
    647     def _checkout(cls, pool, threadconns=None, fairy=None):
    648         if not fairy:
--> 649             fairy = _ConnectionRecord.checkout(pool)
    650
    651             fairy._pool = pool

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in checkout(cls, pool)
    442     @classmethod
    443     def checkout(cls, pool):
--> 444         rec = pool._do_get()
    445         try:
    446             dbapi_connection = rec.get_connection()

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _do_get(self)
    979             if self._inc_overflow():
    980                 try:
--> 981                     return self._create_connection()
    982                 except:
    983                     self._dec_overflow()

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _create_connection(self)
    287         """Called by subclasses to create a new ConnectionRecord."""
    288
--> 289         return _ConnectionRecord(self)
    290
    291     def _invalidate(self, connection, exception=None):

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in __init__(self, pool)
    418         pool.dispatch.first_connect.\
    419             for_modify(pool.dispatch).\
--> 420             exec_once(self.connection, self)
    421         pool.dispatch.connect(self.connection, self)
    422

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/event/attr.pyc in exec_once(self, *args, **kw)
    248                 if not self._exec_once:
    249                     try:
--> 250                         self(*args, **kw)
    251                     finally:
    252                         self._exec_once = True

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/event/attr.pyc in __call__(self, *args, **kw)
    258             fn(*args, **kw)
    259         for fn in self.listeners:
--> 260             fn(*args, **kw)
    261
    262     def __len__(self):

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc in go(*arg, **kw)
   1217         if once:
   1218             once_fn = once.pop()
-> 1219             return once_fn(*arg, **kw)
   1220
   1221     return go

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.pyc in first_connect(dbapi_connection, connection_record)
    164                                     _has_events=False)
    165                 c._execution_options = util.immutabledict()
--> 166                 dialect.initialize(c)
    167             event.listen(pool, 'first_connect', first_connect, once=True)
    168

/home/vagrant/env/local/lib/python2.7/site-packages/ibm_db_sa/base.pyc in initialize(self, connection)
    663     # object which selects between DB2 and AS/400 schemas
    664     def initialize(self, connection):
--> 665         super(DB2Dialect, self).initialize(connection)
    666         self.dbms_ver = connection.connection.dbms_ver
    667         self.dbms_name = connection.connection.dbms_name

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in initialize(self, connection)
    246
    247         if self.description_encoding is not None and \
--> 248                 self._check_unicode_description(connection):
    249             self._description_decoder = self.description_encoding = None
    250

/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in _check_unicode_description(self, connection)
    333                     expression.select([
    334                         expression.literal_column("'x'").label("some_label")
--> 335                     ]).compile(dialect=self)
    336                 )
    337             )

/home/vagrant/env/local/lib/python2.7/site-packages/ibm_db_dbi.pyc in execute(self, operation, parameters)
   1333         self._all_stmt_handlers = []
   1334         self._prepare_helper(operation)
-> 1335         self._set_cursor_helper()
   1336         self._execute_helper(parameters)
   1337         return self._set_rowcount()

/home/vagrant/env/local/lib/python2.7/site-packages/ibm_db_dbi.pyc in _set_cursor_helper(self)
   1216         except Exception, inst:
   1217             self.messages.append(_get_exception(inst))
-> 1218             raise self.messages[len(self.messages) - 1]
   1219         if not num_columns:
   1220             return True

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

 

[hemlatabhattibm]

Hi Daniel,

I checked and found that Informix db is not supported by  ibm-db-sa .

Please see the following pages which says only LUW support.

https://github.com/ibmdb/python-ibmdbsa

Thanks
Hemlata