Python 连接和操作 DB2 和 Oracle 数据库

calendar Feb 22, 2022   ---   · 4 min read · Oracle DB2 JPype JayDeBeApi  ·
Share on: linkedin copy
使用 Python 就难免要应对到不同数据库连接的问题，Python 目前也没有 Java 使用 JDBC 瘦客户端驱动那么方便。本人在用 Python 连接 SQL Server 时经常还是会有些问题，此文只着力于如何用 Python 连接 DB2 和 Oracle 数据库。

从本文中我们将会学到

  1. Python 围绕着 ibm_db 来操作 DB2
  2. 以 Python DB-API 2.0 规范来操作 DB2
  3. cx_Oracle Python 库 + Oracle Instant Client 操作 Oracle
  4. 用 Oracle 的  SID 还是 Service Name 来连接数据库
  5. Python 中借助 JayDeBeApi 使用 JDBC 驱动来操作数据库(以 Oracle 为例)
  6. 从 JayDeBeApi 中我们了解到 JPype 有助于我们在 Python 中调用 Java 

Python 操作 DB2

连接 DB2 数据库要简单的多，只要安装 ibm-db 库
pip install ibm-db # 或用是 ibm_db
假设数据库中创建有一个表  t1, 并有记录
1CREATE TABLE t1(c1 integer, c2 varchar(8));
2INSERT INTO t1(c1, c2) values(1, 'aaa'), (2, 'bbb');

立即查询

然后查询记录的代码如下
1import ibm_db
2conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=dbuser;PWD=your-password;", "", "")
3stmt = ibm_db.exec_immediate(conn, "select * from t1")
4
5result = ibm_db.fetch_both(stmt)
6while result:
7    print(result)
8    result = ibm_db.fetch_both(stmt)

执行后输出的结果为
{'C1': 1, 0: 1, 'C2': 'aaa', 1: 'aaa'}
{'C1': 2, 0: 2, 'C2': 'bbb', 1: 'bbb'}
both 表示是结果行里既有列名也有索引号。

这和我们在 Python 中使用数据库的标准流程(conn->conn.cursor()->cursor.execute()->cursor.fetchXxx()) 不太一致，DB2 的操作以 ibm_db 模块为中心。ibm_db 的 conn 对象没什么有效的操作函数, 我们可以列出 ibm_db 的函数和属性
1print('\n'.join([name for name in dir(ibm_db) if name.lower()==name and (not name.startswith('__'))]))
active
autocommit
bind_param
callproc
check_function_support
client_info
close
column_privileges
columns
commit
conn_error
conn_errormsg
conn_warn
connect
createdb
cursor_type
dropdb
exec_immediate
execute
execute_many
立即执行单条语句用 exec_immediate(), 如果要绑定参数来执行就得用到 prepare, bind_param 和 execute 函数

绑定参数查询

1stmt = ibm_db.prepare(conn, "select * from t1 where c1=?")
2ibm_db.bind_param(stmt, 1, 2)
3ibm_db.execute(stmt)
4
5param = (2,)
6ibm_db.execute(stmt, param)

然后像上面那样处理结果

调用存储过程

创建一个示例存储过程
1CREATE PROCEDURE test_proc(
2    IN empno integer,
3    IN randing integer,
4    OUT name varchar(8))
5LANGUAGE SQL
6BEGIN
7    SELECT 'abc' INTO name FROM sysibm.sysdummy1;
8END

然后调用它
1stmt, *_, name = ibm_db.callproc(conn, 'test_proc', (1, 2, ''))

输出为
abc
返回值为 statement 和所有输入输出参数组成的列表，输入值我们并不用关心，所以用 *_ 去匹配它们。

调用存储过程时注意存储过程的名称，参数个数及类型的匹配，不然任何错误都是报
Exception: Describe Param Failed: [IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "TEST_PROC" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884 SQLCODE=-440
根本无法明确是什么错误。

IBM DB2 的 Python 驱动是以二进制的行式提供的，而且非标准方式使用 DB2 数据库，几乎有方法的参数都是 (*args, **kwargs), 如何调用只好看 IBM 的 Application development in Python with ibm_db，或者也可以看源代码 https://github.com/ibmdb/python-ibmdb

标准方式使用 DB2

上面的非标准方式操作 DB2 很反常规，幸好这个驱动同时也提供了
ibm_db_dbi: Python driver for IBM DB2 and IBM Informix databases that complies to the DB-API 2.0 specification
 1import ibm_db
 2import ibm_db_dbi
 3
 4ibm_db_conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;UID=dbuser;PWD=your-password;", "", "")
 5# 如果是连接本地数据库，可用 ibm_db.connect("study", "dbuser", your-password")
 6conn = ibm_db_dbi.Connection(ibm_db_conn)
 7
 8cursor = conn.cursor()
 9cursor.execute("select * from t1")
10for row in cursor.fetchall():
11    print(row)

输出为
(1, 'aaa')
(2, 'bbb')
最后别忘了用 conn.close() 关闭连接。

Python 操作 Oracle 数据库

Python 连接 Oracle 数据库就比较复杂一些了，除了安装  cx_Oracle 库
pip install cx-oracle
麻烦的事还在后头

现在试着用 cx_Oracle 来连接数据库
1import cx_Oracle
2
3conn = cx_Oracle.connect('system', 'oracle', '192.168.86.10:1521/EE.oracle.docker')

注意：cx_Oracle 连接时要用 service name, 而不是 SID, 下面将会介绍到如何得到 service name，以及通过 SID 构建 DSN 字符串。

错误来了
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
因为这不是一个瘦客户端，需要安装 Oracle 的本地库来支援该驱动，即 Oracle Instant Client。还可用预先安装了 Oracle Instant Client 的 Docker 镜像 或 Dockerfile。

下面以在 Mac OS 上安装 Oracle Instant Client 为例，可选择安装 Oracle Instant Client Downloads for macOS(Intel x86) 里的 Basic Light Package(ZIP)(大小 35M)。然后解压某个目录下，比如
unzip instantclient-basiclite-macos.x64-19.8.0.0.0dbru.zip -d ~/Developers
现在只要连接 Oracle 数据库前加上一行代码
1cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")

因此完整的操作 Oracle 数据库的代码是
 1import cx_Oracle
 2
 3cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")
 4
 5conn = cx_Oracle.connect('system', 'oracle', '192.168.86.10:1521/EE.oracle.docker')
 6cursor = conn.cursor()
 7cursor.execute('select * from dual')
 8for row in cursor.fetchall():
 9    print(row)
10conn.close()

输出
('X',)
除了调用 cx_Oracle.init_oracle_client 来指定 Oracle Instant Client 的位置外，还能用环境变量 LD_LIBRARY_PATH 的方式
export LD_LIBRARY_PATH=/Users/yanbin/Developers/instantclient_19_8:$LD_LIBRARY_PATH
这样的话，就不需要 xc_Oracle.init_oracle_client() 这个函数调用了。

如何通过 SID 进行连接

前面提到 cx_Oracle 是用 service name 进行连接的，如查把 service name  换作 SID 是不能连接成功的，比如写成
1conn = cx_Oracle.connect('system', 'oracle', '192.168.86.33:1521/ee')

报错
cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
这儿有两种方式

通过 SID 构造 DSN 字符串
1dsn_str = cx_Oracle.makedsn("192.168.86.33", "1521", "ee")
2conn = cx_Oracle.connect('system', 'oracle', dsn_str)

此时的 dsn_str 是
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.86.33)(PORT=1521))(CONNECT_DATA=(SID=ee)))
或者在 tnsnames.ora 中找到 service name, 也可用 SID 连接数据库后，通过 SQL 语句 select * from global_name 找到 service name。

JayDeBeApi 桥接 JDBC 到 Python

最后，有人尝试用 JayDeBeApi 在 Python 中使用 JDBC 数据库驱动来连接数据库，这样做似乎对 Oracle 来讲可以省去安装 Oracle Instant Client 软件包，但带来的负担更重了，还需要安装 Java 虚拟机，并且运行的时候还得启动 JVM，用 Python 一个很重要的原因就是要摆脱 Java。

比如操作 DB2
1import jaydebeapi
2conn = jaydebeapi.connect(
3    jclassname="oracle.jdbc.OracleDriver",
4    url="jdbc:oracle:thin:@192.168.86.33:1521:ee",
5    driver_args=("system", "oracle"),
6    jars='/Users/yanbin/.m2/repository/com/oracle/database/jdbc/ojdbc8/21.5.0.0/ojdbc8-21.5.0.0.jar')
7cursor = conn.cursor()
8# use cursor standard APIs

因为实际上扮演连接操作数据库的角色是 Java 的 JDBC, 所以 url 是一个 JDBC 连接字符串，JayDeBeApi 用 url 结合 driver_args 中的参数去调用 Java 的 Driver.getConnection(url, *driver_args) 方法。JayDeBeApi 所实现的是在 Java 与 Python 之间进行类型的转换，以调用相应的 JDBC 函数。

JayDeBeApi 依赖于 JVM，它借助于 JPype1 试图从 JAVA_HOME 或 PATH 环境变量中找到 java 执行程序启动 JVM。如果没有 JAVA_HOME 或 PATH 中找不到 java，则最后因系统而异采用不同的查找定位 JAVA_HOME 的办法，见 _jvmfinder.py，所以这会造成启动 JVM 非常的慢。

找到的 JVM 的路径可以通过 JPype1 代码打印出来
pip install jpype1
1from jpype import getDefaultJVMPath
2print(getDefaultJVMPath())

如果定位到 JAVA_HOME, 输出类似如下的路径
/Library/Java/JavaVirtualMachines/jdk1.8.0_281.jdk/Contents/Home/jre/lib/jli/libjli.dylib
否则报告
jpype._jvmfinder.JVMNotFoundException: No JVM shared library file (libjvm.so) found. Try setting up the JAVA_HOME environment variable properly.
基于 JayDeBeApi 的实现原理，它几乎可以应用于所有 JDBC 驱动支持的数据库。

参考：

  1. Python downloads and related resources
  2. ibm_db API documentation
永久链接 https://yanbin.blog/python-connect-db2-oracle-databases/, 来自 隔叶黄莺 Yanbin's Blog
[版权声明] 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。