Python 连接和操作 DB2 和 Oracle 数据库 | 隔叶黄莺 Yanbin Blog

2022-02-22 | 阅读(715)

使用 Python 就难免要应对到不同数据库连接的问题，Python 目前也没有 Java 使用 JDBC 瘦客户端驱动那么方便。本人在用 Python 连接 SQL Server 时经常还是会有些问题，此文只着力于如何用 Python 连接 DB2 和 Oracle 数据库。

从本文中我们将会学到

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

Python 操作 DB2

连接 DB2 数据库要简单的多，只要安装 ibm-db 库

pip install ibm-db # 或用是 ibm_db

假设数据库中创建有一个表 t1, 并有记录

CREATE TABLE t1(c1 integer, c2 varchar(8));
INSERT INTO t1(c1, c2) values(1, 'aaa'), (2, 'bbb');

1 2	CREATE TABLE t1(c1 integer, c2 varchar(8)); INSERT INTO t1(c1, c2) values(1, 'aaa'), (2, 'bbb');

立即查询

然后查询记录的代码如下

import ibm_db
conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=dbuser;PWD=your-password;", "", "")
stmt = ibm_db.exec_immediate(conn, "select * from t1")

result = ibm_db.fetch_both(stmt)
while result:
    print(result)
    result = ibm_db.fetch_both(stmt)

import ibm_db

conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=dbuser;PWD=your-password;", "", "")

stmt = ibm_db.exec_immediate(conn, "select * from t1")

result = ibm_db.fetch_both(stmt)

while result:

print(result)

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 的函数和属性

print('\n'.join([name for name in dir(ibm_db) if name.lower()==name and (not name.startswith('__'))]))

1	print('\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 函数

绑定参数查询

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

param = (2,)
ibm_db.execute(stmt, param)

stmt = ibm_db.prepare(conn, "select * from t1 where c1=?")

ibm_db.bind_param(stmt, 1, 2)

ibm_db.execute(stmt)

param = (2,)

ibm_db.execute(stmt, param)

然后像上面那样处理结果

调用存储过程

创建一个示例存储过程

CREATE PROCEDURE test_proc(
    IN empno integer,
    IN randing integer,
    OUT name varchar(8))
LANGUAGE SQL
BEGIN
    SELECT 'abc' INTO name FROM sysibm.sysdummy1;
END

CREATE PROCEDURE test_proc(

IN empno integer,

IN randing integer,

OUT name varchar(8))

LANGUAGE SQL

BEGIN

SELECT 'abc' INTO name FROM sysibm.sysdummy1;

END

然后调用它

stmt, *_, name = ibm_db.callproc(conn, 'test_proc', (1, 2, ''))

1	stmt, *_, 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

import ibm_db
import ibm_db_dbi

ibm_db_conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;UID=dbuser;PWD=your-password;", "", "")
# 如果是连接本地数据库，可用 ibm_db.connect("study", "dbuser", your-password")
conn = ibm_db_dbi.Connection(ibm_db_conn)

cursor = conn.cursor()
cursor.execute("select * from t1")
for row in cursor.fetchall():
    print(row)

import ibm_db

import ibm_db_dbi

ibm_db_conn = ibm_db.connect("DATABASE=study;HOSTNAME=localhost;UID=dbuser;PWD=your-password;", "", "")

# 如果是连接本地数据库，可用 ibm_db.connect("study", "dbuser", your-password")

conn = ibm_db_dbi.Connection(ibm_db_conn)

cursor = conn.cursor()

cursor.execute("select * from t1")

for row in cursor.fetchall():

print(row)

输出为

(1, 'aaa')
(2, 'bbb')

最后别忘了用 conn.close() 关闭连接。

Python 操作 Oracle 数据库

Python 连接 Oracle 数据库就比较复杂一些了，除了安装 cx_Oracle 库

pip install cx-oracle

麻烦的事还在后头

现在试着用 cx_Oracle 来连接数据库

import cx_Oracle

conn = cx_Oracle.connect('system', 'oracle', '192.168.86.10:1521/EE.oracle.docker')

import cx_Oracle

conn = 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 数据库前加上一行代码

cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")

1	cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")

因此完整的操作 Oracle 数据库的代码是

import cx_Oracle

cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")

conn = cx_Oracle.connect('system', 'oracle', '192.168.86.10:1521/EE.oracle.docker')
cursor = conn.cursor()
cursor.execute('select * from dual')
for row in cursor.fetchall():
    print(row)
conn.close()

import cx_Oracle

cx_Oracle.init_oracle_client(lib_dir="/Users/yanbin/Developers/instantclient_19_8")

conn = cx_Oracle.connect('system', 'oracle', '192.168.86.10:1521/EE.oracle.docker')

cursor = conn.cursor()

cursor.execute('select * from dual')

for row in cursor.fetchall():

print(row)

conn.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 是不能连接成功的，比如写成

conn = cx_Oracle.connect('system', 'oracle', '192.168.86.33:1521/ee')

1	conn = 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 字符串

dsn_str = cx_Oracle.makedsn("192.168.86.33", "1521", "ee")
conn = cx_Oracle.connect('system', 'oracle', dsn_str)

1 2	dsn_str = cx_Oracle.makedsn("192.168.86.33", "1521", "ee") conn = 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

import jaydebeapi
conn = jaydebeapi.connect(
    jclassname="oracle.jdbc.OracleDriver",
    url="jdbc:oracle:thin:@192.168.86.33:1521:ee",
    driver_args=("system", "oracle"),
    jars='/Users/yanbin/.m2/repository/com/oracle/database/jdbc/ojdbc8/21.5.0.0/ojdbc8-21.5.0.0.jar')
cursor = conn.cursor()
# use cursor standard APIs

import jaydebeapi

conn = jaydebeapi.connect(

jclassname="oracle.jdbc.OracleDriver",

url="jdbc:oracle:thin:@192.168.86.33:1521:ee",

driver_args=("system", "oracle"),

jars='/Users/yanbin/.m2/repository/com/oracle/database/jdbc/ojdbc8/21.5.0.0/ojdbc8-21.5.0.0.jar')

cursor = conn.cursor()

# 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

from jpype import getDefaultJVMPath
print(getDefaultJVMPath())

1 2	from jpype import getDefaultJVMPath print(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 驱动支持的数据库。

参考：

本文链接 https://yanbin.blog/python-connect-db2-oracle-databases/, 来自隔叶黄莺 Yanbin Blog

ddd on postgres in (?,?) 和 =any(?) 用法/性能对比好文章
Perry Conn on Java 9 前/后使用 JAXB (包括支持 javax.* 或 jakarta.*)Your writing has a way of making even the most complex topics accessib...
Yanbin on Windows 安装使用 GCC(mingw-w64)是的，像 Rust 和 dotnet core 一样，都可以支持 Cross compile. Rust 和 g++ 在 Linux 下编译...
MerrickZ on Windows 安装使用 GCC(mingw-w64)我记得G++/GCC都是支持交叉编译的，你可以在linux/macos下面编译出windows平台的二进制PE文件……
Jack on 从 Rust 官方文档理解 Ownership哈哈，年纪大了才会下垂