InterSystems IRIS使用python pyodbc连接 linux环境,odbc驱动安装，DSN配置，数据源配置，linux中文不展示问题

1、官方文档

ODBC Installation and Validation on UNIX® Systems | Using the InterSystems ODBC Driver | InterSystems IRIS for Health 2024.1

By default, a full ODBC installation is performed with a standard InterSystems installation. If you perform a custom installation (as described in the Installation Guide), you can select the “SQL client only” option to install only the client access components (ODBC client driver).

In addition, however, a stand-alone installer is provided for InterSystems ODBC. To use this installer:

1. Create the directory where you wish to install the client, such as /usr/irisodbc/.

2. Copy the appropriate zipped tar file into the directory that you just created.

   The ./dist/ODBC/ directory contains zipped tar files with names like the following:

   ODBC-release-code-platform.tar.gz
   

• where release-code is a release-specific code (that varies among InterSystems versions and releases) and platform specifies the operating system that the ODBC client runs on.

• Go to the directory you created and manually unpack the .tar file, as follows:

  # gunzip ODBC-release-code-platform.tar.gz
  # tar xvf ODBC-release-code-platform.tar
   
  

• This creates bin and dev directories and installs a set of files.

• Run the ODBCInstall program, which will be in the directory that you created. This program creates several sample scripts and configures irisodbc.ini under the mgr directory. For example:

  # pwd
  /usr/irisodbc
  # ./ODBCInstall
   
  

2、驱动地址

InterSystems IRIS | Driver Packages

同步到gitee的：于光/iris-driver-distribution

3、ai解读官方文档

根据官方文档提供的信息，以下是在 Linux 系统上安装和配置 ODBC 驱动程序的总结：

### 执行独立安装

1. 创建安装客户端的目录，如 `/usr/irisodbc/`。
2. 将适当的压缩 tar 文件复制到您创建的目录中。
3. 手动解压 `.tar` 文件，创建 `bin` 和 `dev` 目录并安装一组文件。
4. 运行 `ODBCInstall` 程序，该程序位于您创建的目录中，用于创建示例脚本并配置 `irisodbc.ini`。
   
### ODBC 客户端文件

- `libiodbc.so`：iODBC 驱动程序管理器，支持 8 位和 Unicode ODBC API。
- `libodbc.so`：unixODBC 驱动程序管理器，用于 8 位 ODBC API。
- InterSystems ODBC 客户端驱动程序：`libirisodbc35` 支持 8 位 ODBC 3.5，`libirisodbciw35` 支持 Unicode ODBC 3.5。
 
### 设置共享库路径

- 在 UNIX 系统上使用第三方共享库时，必须通过设置 `LD_LIBRARY_PATH` 来定义 `InterSystems IRIS LibPath` 参数。这是一项安全措施，以防止非特权用户更改路径。

### 故障排除共享对象依赖关系

1. 使用适当的命令列出 InterSystems ODBC 驱动程序的动态依赖项，例如在 Solaris 上使用 `ldd` 命令。
2. 如果没有找到依赖项，则说明所有依赖项都是有效的。如果有错误，请运行命令强制共享对象加载器查找当前目录。
   
以上总结可以帮助您在 Linux 系统上安装和配置 ODBC 驱动程序。如果您需要进一步的帮助或指导，请随时告诉我！

4、ubuntu实操记录，安装odbc驱动

#1  创建路径/usr/irisodbc
root@ubuntu24:/home# cd /usr/
root@ubuntu24:/usr# mkdir irisodbc
root@ubuntu24:/usr# cd irisodbc/
 
#2  解压驱动 ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
root@ubuntu24:/usr/irisodbc# ls
ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
root@ubuntu24:/usr/irisodbc# tar -zxvf ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
ODBCinstall
bin/
bin/libirisodbc35.so
bin/libirisodbciw35.so
bin/libirisodbcur6435.so
bin/odbcgateway.so
bin/odbcgatewayiw.so
bin/odbcgatewayur64.so
bin/irisconnect.so
bin/libiodbc.so
bin/libodbc.so
bin/libodbc.so.2
bin/libodbc.so.2.0.0
dev/
dev/odbc/
dev/odbc/redist/
dev/odbc/redist/iodbc/
dev/odbc/redist/iodbc/odbc.ini_iODBCtemplate
dev/odbc/redist/iodbc/include/
dev/odbc/redist/unixodbc/
dev/odbc/redist/unixodbc/odbcinst.ini_unixODBCtemplate
dev/odbc/redist/unixodbc/readme.txt
dev/odbc/redist/unixodbc/odbc.ini_unixODBCtemplate
dev/odbc/redist/unixodbc/include/
dev/odbc/redist/unixodbc/include/autotest.h
dev/odbc/redist/unixodbc/include/ini.h
dev/odbc/redist/unixodbc/include/log.h
dev/odbc/redist/unixodbc/include/lst.h
dev/odbc/redist/unixodbc/include/odbcinst.h
dev/odbc/redist/unixodbc/include/odbcinstext.h
dev/odbc/redist/unixodbc/include/odbctrac.h
dev/odbc/redist/unixodbc/include/odbctrace.h
dev/odbc/redist/unixodbc/include/sql.h
dev/odbc/redist/unixodbc/include/sqlext.h
dev/odbc/redist/unixodbc/include/sqlspi.h
dev/odbc/redist/unixodbc/include/sqltypes.h
dev/odbc/redist/unixodbc/include/sqlucode.h
dev/odbc/redist/unixodbc/include/sqp.h
dev/odbc/redist/unixodbc/include/unixodbc_conf.h
dev/odbc/redist/unixodbc/include/uodbc_extras.h
dev/odbc/redist/unixodbc/include/uodbc_stats.h
dev/odbc/redist/unixodbc/libodbcinst.so
dev/odbc/redist/unixodbc/libodbcinst.so.2
dev/odbc/redist/unixodbc/libodbcinst.so.2.0.0
dev/odbc/redist/unixodbc/isql
dev/odbc/redist/unixodbc/iusql
dev/odbc/redist/unixodbc/odbcinst
dev/odbc/redist/ssl/
dev/odbc/redist/ssl/readme.txt
dev/odbc/redist/ssl/odbcssl.ini.template
dev/odbc/redist/ssl/irisodbc.ini.template
 
# 解压后情况
root@ubuntu24:/usr/irisodbc# ls
bin  dev  ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz  ODBCinstall
root@ubuntu24:/usr/irisodbc# ll
total 4636
drwxr-xr-x  4 root root    4096 Jul 15 14:16 ./
drwxr-xr-x 15 root root    4096 Jul 15 14:15 ../
drwxrwxr-x  2 root root    4096 Apr 14  2023 bin/
drwxr-xr-x  3 root root    4096 Apr 14  2023 dev/
-rw-r--r--  1 root root 4720787 Jul 15 14:16 ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
-rwxrwxr-x  1 root root    4501 Apr 14  2023 ODBCinstall*
root@ubuntu24:/usr/irisodbc# pwd
/usr/irisodbc
root@ubuntu24:/usr/irisodbc# ls
bin  dev  ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz  ODBCinstall
root@ubuntu24:/usr/irisodbc# cd /usr/irisodbc
root@ubuntu24:/usr/irisodbc# ll
total 4636
drwxr-xr-x  4 root root    4096 Jul 15 14:16 ./
drwxr-xr-x 15 root root    4096 Jul 15 14:15 ../
drwxrwxr-x  2 root root    4096 Apr 14  2023 bin/
drwxr-xr-x  3 root root    4096 Apr 14  2023 dev/
-rw-r--r--  1 root root 4720787 Jul 15 14:16 ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
-rwxrwxr-x  1 root root    4501 Apr 14  2023 ODBCinstall*
 
# 安装驱动 可见创建了一个文件irisodbc.ini文件。
root@ubuntu24:/usr/irisodbc#  ./ODBCinstall
    Creating irisodbc.ini ...
 
 Done setting up ODBC and SQLGateway!
 
 
root@ubuntu24:/usr/irisodbc# ls
bin  dev  mgr  ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz  ODBCinstall
 
# 查找文件路径 /usr/irisodbc/mgr/irisodbc.ini
root@ubuntu24:/usr/irisodbc# find /usr/irisodbc/ -name "irisodbc.ini"
/usr/irisodbc/mgr/irisodbc.ini
root@ubuntu24:/usr/irisodbc# ll
total 4640
drwxr-xr-x  5 root root    4096 Jul 15 14:17 ./
drwxr-xr-x 15 root root    4096 Jul 15 14:15 ../
drwxrwxr-x  2 root root    4096 Apr 14  2023 bin/
drwxr-xr-x  3 root root    4096 Apr 14  2023 dev/
drwxr-xr-x  2 root root    4096 Jul 15 14:17 mgr/
-rw-r--r--  1 root root 4720787 Jul 15 14:16 ODBC-2023.1.0.229.0-lnxubuntu2204x64.tar.gz
-rwxrwxr-x  1 root root    4501 Apr 14  2023 ODBCinstall*
root@ubuntu24:/usr/irisodbc# cd mgr/
root@ubuntu24:/usr/irisodbc/mgr# ls
irisodbc.ini
 
# 该驱动默认的DSN连接配置，如果直接用dsn连接，直接用dsn=User，或者dsn=Userunixodbc
# 看配置的driver驱动配置：/usr/irisodbc/bin/libirisodbc35.so， 说明iris的驱动就是这个so文件，所以代码里面配置这个路径
 
root@ubuntu24:/usr/irisodbc/mgr# cat irisodbc.ini
[ODBC Data Sources]
User = User
Userunixodbc = Userunixodbc
 
[User]
Driver          = /usr/irisodbc/bin/libirisodbc35.so
Description     = IRIS ODBC driver
Host            = localhost
Namespace       = USER
UID             = _SYSTEM
Password        = SYS
Port            = 1972
Protocol        = TCP
Query Timeout   = 1
Static Cursors  = 0
Trace           = off
TraceFile       = iodbctrace.log
Authentication Method   = 0
Security Level  = 2
Service Principal Name = iris/localhost.domain.com
 
[Userunixodbc]
Driver          = /usr/irisodbc/bin/libirisodbcur6435.so
Description     = IRIS ODBC driver
Host            = localhost
Namespace       = USER
UID             = _SYSTEM
Password        = SYS
Port            = 1972
Protocol        = TCP
Query Timeout   = 1
Static Cursors  = 0
Trace           = off
TraceFile       = iodbctrace.log
Authentication Method   = 0
Security Level  = 2
Service Principal Name = iris/localhost.domain.com
 
root@ubuntu24:/usr/irisodbc/mgr# ls
irisodbc.ini
 
# python查询版本
root@ubuntu24:/usr/irisodbc/mgr# cd /home/code/
root@ubuntu24:/home/code# vim c1.py
root@ubuntu24:/home/code# python3 c1.py
('',)
root@ubuntu24:/home/code# python3 c1.py
('',)
root@ubuntu24:/home/code# cat c1.py
import pyodbc
 
driver = '/usr/irisodbc/bin/libirisodbc35.so'
server = 'ip'
database = 'test'
username = ''
password = ''
port = '1927'
 
 
cnxn = pyodbc.connect(Driver=driver, Server=server, Database=database, UID=username, PWD=password, Port=port)
 
cursor = cnxn.cursor()
cursor.execute("SELECT $ZVERSION")
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
 
cnxn.close()
 
 
# python select 1
root@ubuntu24:/home/code# cp c1.py c2.py
root@ubuntu24:/home/code# vim c2.py
root@ubuntu24:/home/code# python3 c2.py
(1,)
root@ubuntu24:/home/code# cat c2.py
import pyodbc
 
driver = '/usr/irisodbc/bin/libirisodbc35.so'
server = 'ip'
database = 'test'
username = ''
password = ''
port = '1927'
 
 
cnxn = pyodbc.connect(Driver=driver, Server=server, Database=database, UID=username, PWD=password, Port=port)
 
cursor = cnxn.cursor()
cursor.execute("SELECT 1")
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
 
cnxn.close()
 
root@ubuntu24:/home/code#
root@ubuntu24:/home/code#

5、python代码

 
import pyodbc
 
driver = '/usr/irisodbc/bin/libirisodbc35.so'
server = 'xxx'
database = 'xxx'
username = 'xxx'
password = 'xxx'
port = '1927'
 
 
cnxn = pyodbc.connect(Driver=driver, Server=server, Database=database, UID=username, PWD=password, Port=port)
 
cursor = cnxn.cursor()
cursor.execute("SELECT 1")
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
 
cnxn.close()
 

6、中文显示空字符串问题

增加

 
###ensure it reads strings correctly.
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf8')
cnxn.setencoding(encoding='utf8')

root@ubuntu:/home/code# cat c1.py
import pyodbc
 
driver = '/usr/irisodbc/bin/libirisodbc35.so'
server = 'ip'
database = ''
username = ''
password = ''
port = '1972'
 
 
cnxn = pyodbc.connect(Driver=driver, Server=server, Database=database, UID=username, PWD=password, Port=port)
 
 
 
###ensure it reads strings correctly.
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf8')
cnxn.setencoding(encoding='utf8')
 
 
cursor = cnxn.cursor()
cursor.execute("SELECT $ZVERSION")
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
 
cnxn.close()
 
root@ubuntu:/home/code# vim c1.py
root@ubuntu:/home/code# python3 c1.py
('IRIS for UNIX (Red Hat Enterprise Linux for x86-64) 2021.1.2 (Build 338_0_21956U) Thu Nov 3 2022 16:05:53 EDT',)
root@ubuntu:/home/code#