cx_Oracle 操作oracle数据库

cx_Oracle 操作oracle数据库

1、安装配置cx_Oracle模块

      参考 https://www.jianshu.com/p/78e6640bf4b0 

     Window系统配置

     1）安装cx_oracle模块

          pip3 install cx_Oracle

     2）下载所需的软件包vcredist_x64.exe，instantclient-basic-windows.x64-11.2.0.4.0.zip（根据自己连接oracle数据库版本和系统位数下载）

           下载地址：https://www.oracle.com/database/technologies/instant-client/downloads.html

     3） 解压instantclient-basic-windows.x64-11.2.0.4.0.zip，然后点击运行instantclient_11_2里的adrci.exe

     4）双击安装vcredist_x64.exe

     5）配置path变量
　　 a）电脑-属性-高级系统设置-高级-环境变量，将instantclient_11_2路径C:\Program Files\instantclient_11_2添加到path环境变量中；如果本地还安装有oracle数据库，要添加到oracle的路径前面

        b）电脑-属性-高级系统设置-高级-环境变量-新建用户变量：

              ORACLE_HOME=C:\Program Files\instantclient_11_2 ,

              TNS_ADMIN=C:\Program Files\instantclient_11_2，

              NLS_LANG= C:\Program Files\instantclient_11_2，NLS_LANG是为了防止中文乱码

      6）将instantclient_11_2目录下oci.dll，oraocci11.dll，oraociei11.dll、ocijdbc11.dll复制到python\Lib\site-packages目录下

      7）创建一个监听文件tnsnames.ora到C:\Program Files\instantclient_11_2，打开添加以下内容，并保存     

oral=
     (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST= IP地址)(PORT =端口))
     (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME =数据库名称)
     ) )

Linux系统配置

1）安装cx_Oracle
   python pip install cx_Oracle
2）安装libaio
   sudo yum install libaio
3）下载instantclient-basic-linux.x64-21.1.0.0.0.zip和instantclient-sdk-linux.x64-21.1.0.0.0.zip；
   mkdir -p /opt/oracle
   cd /opt/oracle
   unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
 删除instantclient-sdk-linux.x64-21.1.0.0.0的SDK目录下的admin文件夹，然后将SDK目录下所有文件复制到/opt/oracle/instantclient_21_1目录下
4）配置环境变量：
  export ORACLE_HOME=/opt/oracle/instantclient_21_1
  export PATH=$PATH:$ORACLE_HOME
  export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
  # 下面两行是解决编码问题，如果不设置下面两行，从oracle导出中文数据会出现乱码
  export LANG="zh_CN.UTF-8"
  export NLS_LANG=AMERICAN_AMERICA.UTF8
  export TNS_ADMIN=/opt/oracle/instantclient_21_1

  source /etc/profile
5）移动tnsnames.ora至/opt/oracle/instantclient_21_1/network/admin目录下
6）copy libclntsh.so 到python site-packages
7）查看 libclntsh.so文件内容
  vim libgmp.so
  libclntsh.so.21.1
  建立软连接：
  ln -s libclntsh.so.21.1 libclntsh.so
8）vim /etc/ld.so.conf
  添加一行：
  /opt/oracle/instantclient_21_1
  运行：ldconfig
9）sudo sh -c "echo /opt/oracle/instantclient_21_1 > /etc/ld.so.conf.d/oracle-instantclient.conf"
  sudo ldconfig

2、python封装 cx_orcale操作

数据库配置:
############    测试_内网      ############
#    host_name: '127.0.0.1'
#    port: 8080
#    service_name: 'service_name'
#    username: 'username'
#    password: 'password'
############    测试_外网      ############
    host_name: '127.0.0.1'
    port: 8080
    sid: 'sid'
    username: 'username'
    password: 'password'

 

import cx_Oracle
import os
import sys
import yaml

sys.setrecursionlimit(10000)  # 例如这里设置为一万
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
os.environ['ORACLE_HOME'] = r'C:\Program Files\My Program Files\instantclient_12_2'
os.environ['TNS_ADMIN'] = r'C:\Program Files\My Program Files\instantclient_12_2'
# sys.path.insert(0, r'C:\Program Files\My Program Files\instantclient_12_2;')

''' 公共类'''
class MyOracle(object):
    def __init__(self, db_path, db_name):
        '''
        :param db_path: 获取数据库配置文件路径
        :param db_name: 数据库名
        '''
        self.db_conf = self._get_yamlMsg(db_path).get(db_name)
        self.hostname = self.db_conf.get('host_name')
        self.port = self.db_conf.get('port')
        self.sid = self.db_conf.get('sid', None)
        self.service_name = self.db_conf.get('service_name', None)
        self.username = self.db_conf.get('username')
        self.password = self.db_conf.get('password')

    def _get_yamlMsg(self, sql_path):
        '''
        # open方法打开直接读出来
        :return:
        '''
        with open(sql_path, 'r', encoding='utf-8') as f:
            sqlMsg = f.read()
        # 用load方法转字典
        sql_dic = yaml.load(sqlMsg)
        return sql_dic

    def get_connection(self):
        try:
            if self.service_name:
                tnsname = cx_Oracle.makedsn(self.hostname, self.port, service_name=self.service_name)
            else:
                tnsname = cx_Oracle.makedsn(self.hostname, self.port, self.sid)
            self.con = cx_Oracle.connect(self.username, self.password, tnsname)
        except Exception as e:
            print('连接数据库出错：', e)

    def get_cur(self):
        return self.con.cursor()

    def excute_one(self, sql):
        try:
            cur = self.get_cur()
            cur.execute(sql)
            # cur.prepare('select * from t_emp a where a.empid=:id')
            # cur.execute(None,{'id':id})
            line = cur.fetchone()
        except Exception as e:
            print('数据库查询出错：', e)
            cur.close()
            self.con_close()
        cur.close()
        return line

    def excute_some(self, sql):
        try:
            cur = self.get_cur()
            cur.execute(sql)
            lines = cur.fetchall()
        except Exception as e:
            print('数据库查询出错：', e)
            cur.close()
            self.con_close()
        cur.close()
        return lines

    def excute(self, sql):
        try:
            cur = self.get_cur()
            cur.execute(sql)
            effectRow = cur.rowcount
        except Exception as e:
            print('数据库出错：', e)
            cur.close()
            self.con_close()
        cur.close()
        return effectRow


    def con_close(self):
        self.con.close()

if __name__ == '__main__':
    # 获取数据库配置文件路径
    db_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'conf', "application.yaml")
    db = MyOracle(db_path, '数据库配置')
    db.get_connection()
    print(db.excute_some('''select * from dual'''))
    db.con_close()