【产品名称】OceanBase
【产品版本】2.7X
【问题描述】PYTHON 如何通过 JDBC驱动连接OceanBase。
python 可以通过 jaydebeapi 这个库来使用 JDBC驱动。
JayDeBeApi module allows you to connect from Python code to databases using Java JDBC. It provides a Python DB-API v2.0 to that database.
https://github.com/baztian/jaydebeapi
安装: pip install jaydebeapi
示例代码如下:
import jaydebeapi dirver = "com.alipay.oceanbase.jdbc.Driver" url = "jdbc:oceanbase://IP地址:端口/用户?rewriteBatchedStateme&useUnicode=true&characterEncoding=utf-8" user = "用户@租户#集群" password = "密码" jarFile = "E://common//temp//oceanbase-client-1.1.7.jar" conn=jaydebeapi.connect(dirver,url,[user,password],jarFile) curs = conn.cursor() curs.execute("select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;") rs = curs.fetchall() for r in rs: print(r) curs.close() conn.close()
结果如下:
E:\anaconda3\python.exe E:/common/temp/obtest.py
('2021-06-23 17:55:48',)
Python直接用MySQL的库连就可以了
ORACLE 租户是无法用MYSQL连的。
如果用pymysql之类的mysql库去连接ORACLE租户的数据库会报错:
E:\anaconda3\python.exe E:/common/temp/obtest_as_mysql.py Traceback (most recent call last): File "E:/common/temp/obtest_as_mysql.py", line 14, in <module> database='unview', charset='utf8') File "E:\anaconda3\lib\site-packages\pymysql\connections.py", line 353, in __init__ self.connect() File "E:\anaconda3\lib\site-packages\pymysql\connections.py", line 633, in connect self._request_authentication() File "E:\anaconda3\lib\site-packages\pymysql\connections.py", line 907, in _request_authentication auth_packet = self._read_packet() File "E:\anaconda3\lib\site-packages\pymysql\connections.py", line 725, in _read_packet packet.raise_for_error() File "E:\anaconda3\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error err.raise_mysql_exception(self._data) File "E:\anaconda3\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.NotSupportedError: (1235, 'Oracle tenant for current client driver is not supported')
dreamrise 你好,
我们基于OBserver 2.2.77版本(以及OBServer 2.2.74版本) + OB JDBC 1.1.7版本,分别对Oracle模式和MySQL模式进行测试,结果可连接可执行,以下是测试案例:
import jaydebeapi def oboracle_test(): url = 'jdbc:oceanbase://这里是IP:这里是端口/UNITTESTS' user = '这里是个账号' password = '这里是密码' jarFile = 'C:/Users/wgz/PycharmProjects/ob_test/ob_test/oceanbase-client-1.1.7.jar' driver = 'com.alipay.oceanbase.jdbc.Driver' conn = jaydebeapi.connect(driver, url, [user, password], jarFile) curs = conn.cursor() 、 deleteSql = 'drop table test_python' try: curs.execute(deleteSql) except Exception as e: print(e) createSql = 'create table test_python (id number , test_varchar varchar2(32))' curs.execute(createSql) for i in range(10): curs.execute("insert into test_python values (0,'oracle_test')") querySql = 'select * from test_python' curs.execute(querySql) result = curs.fetchall() print(result) curs.close() conn.close() def obmysql_test(): url = 'jdbc:oceanbase://这里是IP:这里是地址/TEST' user = '这是账号' password = '这是密码' jarFile = 'C:/Users/wgz/PycharmProjects/ob_test/ob_test/oceanbase-client-1.1.7.jar' driver = 'com.alipay.oceanbase.jdbc.Driver' conn = jaydebeapi.connect(driver, url, [user, password], jarFile) curs = conn.cursor() deleteSql = 'drop table test_python' try: curs.execute(deleteSql) except Exception as e: print() createSql = 'create table test_python (id int , test_a varchar(32))' curs.execute(createSql) for i in range(10): curs.execute("insert into test_python values (0,'mysql_test')") querySql = 'select * from test_python' curs.execute(querySql) result = curs.fetchall() print(result) curs.close() conn.close() oboracle_test() obmysql_test()
结果如下:
我是驱动和中间件的产品经理,如果你在使用Python通过jdbc访问OB过程中依旧有问题,或者其他问题,可以让项目中的OceanBase同学联系我,拉个群,我们进行沟通。
你好,我们环境下使用有问题,能否告知下花名或者其余方式。
请钉钉社区群里 @庆涛 ,我拉群分析你的问题。
The server time_zone 'GMT+8:00' defined in the 'serverTimezone' parameter cannot be parsed by java TimeZone implementation. See java.util.TimeZone#getAvailableIDs() for available TimeZone, depending on your JRE implementation.
本机是没有报错,但是部署到linux主机上后,就报这个错误
我这不是写个教程给大家看么. 关键的是,你们应该提供官方的纯正驱动库了,而不是基于JDBC驱动,这个驱动用jaydebeapi有很多BUG.
这篇帖子好实用!
帖子收藏了!
你好,我在测试的时候发现在定义jar文件路径的时候
如果写成和上面你定义的类似的绝对路径
jarFile = "E:/projects/oceanbase-client-x.x.x.jar"
会无法连接,报下面的错
Class com.alipay.oceanbase.jdbc.Driver is not found
只有把jar文件放在运行的工作路径下,然后定义
jarFile = 'oceanbase-client-x.x.x.jar'
才可以连接成功。
这是jar包的问题吗?我在连接别的驱动的时候没有出现过这个问题。
感谢回答,不过已经解决了~
是因为路径里面有中文 ,无法识别。全改成英文就可以了~
oracle租户的貌似这种用session不支持?
result = session.query(WF_PROCESS_ACTIVITY_RESPONSES).filter_by(RESPONSE_ID=38253).first()
还是报找不到驱动的问题,请问还有谁可以联系吗?帮忙看看。python用的3.6