如何使用pyodbc / unixODBC在python3中连接Informix数据库中的select数据

I'm having a problem selecting data from Informix database tables using python3.5 and pyodbc/unixODBC.

我在使用python3.5和pyodbc/unixODBC从Informix数据库表中选择数据时遇到了问题。

I have a little py file (ifx.py) the contents of which are:

我有一个小py文件(ifx.py)，它的内容是:

import pyodbc
db=pyodbc.connect('DSN=Test1')
db.execute("SELECT * FROM customer ")

and I get an error :

我得到一个错误:

pyodbc.Error: ('HY000', '[HY000] [Informix][Informix ODBC Driver]Invalid byte in codeset conversion input. (21000) (SQLExecDirectW)')

All the searches I've done imply something to do with LOCALE settings, but I check and they're all set the same:

我所做的所有搜索都暗示了与语言环境设置有关的一些东西，但我进行了检查，它们都设置为相同的:

CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819
dbs_collate=en_US.819

If I use the unixODBC 'isql' utility I can connect to and query the data happily.

如果我使用unixODBC 'isql'实用程序，我可以很高兴地连接和查询数据。

Version Information.

版本信息。

unixODBC.x86_64 0:2.2.14-14.el6
pyodbc.version => '4.0.6'
Linux => Red Hat Enterprise Linux Server release 6.8 
python3.5 => Python 3.5.3 
Database => IBM Informix Dynamic Server Version 12.10.FC6X5 
ClientSDK => IBM Informix CSDK Version 4.10, IBM Informix-ESQL Version 4.10.FC6

Environment Variable ODBCINI points to /etc/odbc.ini. Contents are:

环境变量ODBCINI指向/etc/odbc. ini。内容是:

[ODBC Data Sources]
Test1=IBM INFORMIX ODBC DRIVER
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[Test1]
Driver=/opt/informix/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=eunice
LogonID=
pwd=
Servername=indika_test
Port=9916
CursorBehavior=0
CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819
TRANSLATIONDLL=/opt/informix/lib/esql/igo4a304.so

/etc/odbcinst.ini contents:

/etc/odbcinst.ini内容:

[ODBC Drivers]
IBM INFORMIX ODBC DRIVER=Installed
[IBM INFORMIX ODBC DRIVER]
Driver=/opt/informix/lib/cli/iclit09b.so
Setup=/opt/informix/lib/cli/iclit09b.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y

and odbcinst -j output is

odbcinst -j输出为

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Thanks in advance for any help or suggestions.

谢谢您的帮助和建议。

3 个解决方案

#1

This is probably due to an multibyte character in the customer table, which can not be converted to Latin 1, ISO 8859-1 from UTF8. Even with same codes set on both CLIENT_LOCALE and DB_LOCALE, an ODBC client makes an internal conversion from UTF-8 to 8859. If there are any 2-byte chars (chars beyond extended-ascii 255) they will not convert to a Latin-1 client. I would look at the data in customer table and identify the problematic data.

这可能是由于客户表中的多字节字符，无法从UTF8转换为拉丁文1、ISO 8859-1。即使在CLIENT_LOCALE和DB_LOCALE上设置了相同的代码，ODBC客户端也会进行从UTF-8到8859的内部转换。如果有任何2字节的字符(超过扩展ascii 255的字符)，它们将不会转换为Latin-1客户端。我将查看customer表中的数据并识别有问题的数据。

#2

It is possible (or likely) that there is an Unicode encoding mismatch. On my Debian system, the python builds (both 2.7 and 3.4) use UCS-4 encoding. You can verify with this command:

可能(或可能)存在Unicode编码不匹配。在我的Debian系统上，python构建(包括2.7和3.4)使用UCS-4编码。您可以使用以下命令进行验证:

python3 -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"

python3进口系统;print(sys - c”。maxunicode < 66000和'UCS2'or 'UCS4)”

Once you find the encoding, you need to make sure the Informix odbc.ini file has the same Unicode encoding. In my odbc.ini file,

找到编码后，需要确保Informix odbc。ini文件具有相同的Unicode编码。在我的odbc。ini文件,

[ODBC]

(ODBC)

;uncomment the below line for UNICODE connection

;取消对下一行的UNICODE连接的注释

UNICODE=UCS-4

UNICODE = ucs - 4

I hope this resolves your issue.

我希望这能解决你的问题。

#3

Thanks for the responses. It was unicode mismatch.

谢谢你的回复。这是unicode不匹配。

db=pyodbc.connect('DSN=Test1')
db.setdecoding(pyodbc.SQL_WCHAR, encoding='UTF-8')
db.setdecoding(pyodbc.SQL_CHAR, encoding='UTF-8')
db.setencoding(encoding='UTF-8')

By adding setencoding and setdecoding I was able to successfully select data form the Informix DB.

通过添加setencoding和setdecoding，我能够成功地从Informix DB中选择数据。

#1

#2