python3.6连接oracle数据库

一、环境信息

win 10
python 3.6

二、python安装包

cx_Oracle-8.0.0-cp36-cp36m-win_amd64.whl

三、示例代码

import cx_Oracle as oracle
import sys
from common.readconfig import Config

config = Config()

class OracleDb:
    def __init__(self, connect):
        try:
            self.conn = oracle.connect(connect)
            self.cursor = self.conn.cursor()
        except oracle.Error as e:
            sys.exit(1)

    # 针对读操作返回结果集，以字典格式返回
    def execute_query(self, sql):
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            res = []
            cols = [d[0] for d in self.cursor.description]
            for row in result:
                b = dict(zip(cols, row))
                res.append(b)
            return res
        except oracle.Error as e:
            sys.exit(1)

    # 针对更新,删除,事务等操作失败时回滚
    def execute_commit(self, sql=''):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except oracle.Error as e:
            self.conn.rollback()
            sys.exit(1)

    def _close(self):
        try:
            self.cursor.close()
            self.conn.close()
        except AttributeError as e:
            pass

    def __del__(self):
        self._close()


if __name__ == '__main__':
    oracle = OracleDb('username/password@host:port/db')
    res = oracle.execute_query("select rowid, a, b from test t where t.name='zhangsan'")
    print(res[0].get('a'))

四、连接过程中报错处理

1、 cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library

原因：目前plsql的破解版只支持32位的instantclient，32位的instantclient连接64位的Oracle没有问题，但64位的Python却用不了，所以需要下载64位的instantclient。

下载oracle客户端
http://jvniu.jb51.net:81/201708/tools/instantclientx64_jb51.rar
并解压，将目录下所有dll文件拷贝到python安装目录，重启pycharm即可

2、AttributeError: ‘OracleDb’ object has no attribute ‘Error’

网上查找资料发现，是由于sql后面带了;号导致的，去掉分号后成功执行