Problems with column names using pyodbc and informix

[dumischbaenger]

Hello,

I'm using pyodbc to connect to our Informix DBMS.

I can connect but have troubles using result sets created with fetchall().

The column values of the rows can't be accessed by their names. By chance I found out that the cursor description is also wrong.

Here is my example:

#!/usr/bin/env python3

import pyodbc
import logging

logging.basicConfig(format='%(asctime)s %(levelname)s %(module)s.%(funcName)s.%(lineno)s:%(message)s', datefmt = "%Y-%m-%d %H:%M:%S", level="INFO")
logging.info("program started")

connection=None
try:
  connection = pyodbc.connect("DSN=yucatan_v06@onlcelsiustcp",autocommit=True)
except:
  fatal("connect scheiterte")


#connection.setdecoding(pyodbc.SQL_WCHAR, encoding='latin1')
#connection.setdecoding(pyodbc.SQL_CHAR, encoding='latin1')
#connection.setencoding(encoding='latin1')

#connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
#connection.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
#connection.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-8')
connection.setencoding(encoding='utf-8')

dbname="keine"
cursor=connection.cursor()
cursor.execute("SELECT first 1 dbinfo('dbname') as c111111, 1 as c222222222222 from systables")

name1=cursor.description[0][0]
name2=cursor.description[1][0]

logging.info("description name column1: " + name1 + ":" + str(name1.encode("UTF-8")) + ":" + str(len(name1)))
logging.info("description name column2: " + name2 + ":" + str(name2.encode("UTF-8")) + ":" + str(len(name2)))

rows=cursor.fetchall()
for row in rows:
  dbname=row[0]
  dbname=row.c111111
cursor.close()

logging.info("result: " + dbname)

logging.info("program stopped")
logging.shutdown()

Here's  the output:

python3.4 errorExample.py
2017-05-04 15:44:46 INFO errorExample.<module>.7:program started
2017-05-04 15:44:46 INFO errorExample.<module>.32:description name column1: c111:b'c\x001\x001\x001':7
2017-05-04 15:44:46 INFO errorExample.<module>.33:description name column2: c222222:b'c\x002\x002\x002\x002\x002\x002':13
Traceback (most recent call last):
  File "errorExample.py", line 38, in <module>
    dbname=row.c111111
AttributeError: 'pyodbc.Row' object has no attribute 'c111111'

 

Here are my tools:

python3 --version
Python 3.4.5

 

pip list
pip (9.0.1)
pudb (2016.1)
Pygments (2.1.3)
pyodbc (4.0.16)
setuptools (20.7.0)
urwid (1.3.1)
wheel (0.29.0)

 

odbcinst -j
unixODBC 2.3.1
DRIVERS............: /opt/unixodbc/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/unixodbc/etc/odbc.ini
FILE DATA SOURCES..: /opt/unixodbc/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Regards,
Bernhard