某现场客户程序使用python,引用了jaydebeapi库连接oceanbase的jdbc驱动oceanbase-client-*.*.*.jar
JayDeBeApi是一个python模块,可通过它来使用java的JDBC连接数据库,为该数据库提供了 Python DB-API v2.0
OB官网给的jaydebeapi连接ob的列子:
#!/usr/bin/env python3.6
# -*- coding: UTF-8 -*-
encoding = "utf8"
import jaydebeapi
def ob_test():
url = 'jdbc:oceanbase://host:port/database'
user = 'u*****'
password = 'p*****'
driver = 'com.alipay.oceanbase.jdbc.Driver'
jarFile = './oceanbase-client-2.2.3.jar'
sqlStr = 'select * from test_python'
# conn=jaydebeapi.connect('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@10.0.0.0:1521/orcl',['hwf_model','hwf_model'],'E:/pycharm/lib/ojdbc14.jar')
conn = jaydebeapi.connect(driver, url, [user, password], jarFile)
curs = conn.cursor()
curs.execute(sqlStr)
result = curs.fetchall()
print(result)
curs.close()
conn.close()
ob_test()
使用pip安装jaydebeapi:
pip install JayDeBeApi
python setup.py install
jython setup.py install
客户的需求是想要之前在oracle中执行正常的变量绑定同样在ob的oracle租户下正常执行,但报错:
all_tabs_sql = """select
t.COLUMN_NAME,
t.DATA_TYPE
from ALL_TAB_COLS t
where t.OWNER= :OWNER
and t.TABLE_NAME = :TARGET_TABLE
and t.COLUMN_NAME not like 'S$%'
order by t.COLUMN_ID"""
t_cur.execute(all_tabs_sql, {"OWNER": "ODT", "TARGET_TABLE": "S$SESSION_K0"})
客户的需求是指定绑定变量赋值,默认的jaydebeapi不支持,只支持以下这种按顺序的绑定变量:
insert into table1 (a1) values(?)
或者这种:
经OB驱动专家确认,jaydebeapi的 jaydebeapi.Cursor._set_stmt_parms少了以下function:
添加红框内的到jaydebeapi的Cursor._set_stmt_parms即可实现