python3.6连接oracle数据库

朱雀 2023-02-23 01:25 31阅读 0赞

## 一、环境信息 ##

> 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即可  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU_size_16_color_FFFFFF_t_70 1]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200704180945646.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200704181122522.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2EyMDA4MjIxNDYwODU=,size_16,color_FFFFFF,t_70