使用SID连接Oracle数据库

一、Python连接Oracle数据库

　　1. 数据库配置文件config.py：

oracle_config = {
    "host": "192.168.97.128",
    "port": "1521",
    "user": "abcd",
    "password": "abcd123",
    "sid": "masabc",
}

　　2. 连接数据库app.py：

import json
import logger
import cx_Oracle as oracle
import traceback
from config import oracle_config
import cx_Oracle


class DatabaseAdapter:
    orahost = None
    oraport = 1521
    oraservicename = None
    orauser = None
    orapassword = None
    connectionstr = None
    conn = None
    cursor = None
    dsn = None

    def __init__(self):
        '''
        初始化，从配置文件读取服务器信息
        '''
        try:
            self.orahost = oracle_config['host']
            self.oraport = oracle_config['port']
            self.sid = oracle_config['sid']
            self.orauser = oracle_config['user']
            self.orapassword = oracle_config['password']
            self.dsn = cx_Oracle.makedsn(
                self.orahost,
                self.oraport,
                sid=self.sid,
            )
        except:
            logger.writeLog("读取数据库配置文件失败!")

    def oraconnect(self):
        '''
        连接数据库方法
        '''
        try:
            self.conn = cx_Oracle.connect(
                self.orauser,
                self.orapassword,
                self.dsn,
                encoding='utf-8'
            )
            self.cursor = self.conn.cursor()
            return self.conn, self.cursor
        except:
            errstr = traceback.format_exc()
            logger.writeLog("Oracle数据库连接错误:" + errstr)

    def insert(self, sqlstr, para):
        '''
        数据库插入
        需要采用绑定变量的方式进行，否则会有安全问题
        '''
        # para = { dept_id=280, dept_name="Facility" }
        # cursor.execute("""
        # insert into departments (department_id, department_name)
        # values (:dept_id, :dept_name)""", data)
        try:
            if self.cursor:
                self.cursor.execute(sqlstr, para)
                self.conn.commit()
            else:
                # 进行重连
                logger.writeLog("Oracle数据库尝试重新连接", "insertfail.log")
                self.oraconnect()
                if self.cursor:
                    self.cursor.execute(sqlstr, para)
                    self.conn.commit()
                else:
                    logger.writeLog("Oracle数据库重连插入失败:" + sqlstr + json.dumps(para), "insertfail.log")
        except:
            errstr = traceback.format_exc()
            logger.writeLog("Oracle数据库插入失败:" + errstr + sqlstr + json.dumps(para), "insertfail.log")

    def search(self, sqlstr, para=None):
        '''
        数据库查询
        '''
        try:
            if para == None:
                self.cursor.execute(sqlstr)
            else:
                self.cursor.execute(sqlstr, para)
            rows = self.cursor.fetchall()
            return rows
        except:
            logger.writeLog("Oracle数据库查询失败:" + sqlstr)
            return False

    def closeconn(self):
        '''
        关闭数据库连接
        '''
        try:
            self.cursor.close()
            self.conn.close()
        except:
            errstr = traceback.format_exc()
            logger.writeLog("Oracle数据库连接关闭错误:" + errstr)


if __name__ == "__main__":
    db = DatabaseAdapter()
    ret = db.oraconnect()
    print(ret)

    # 测试验证
    jsonobj = {'ESN': 76511706, 'FuelVolumeTotal': 0.57263308763504, 'DEFVolumeTotal': 0.0235559437423944,
               'OccurrenceTime': '2020-2-28 9:0:54'}

    sqlstr = """
            insert into user2.BASE_00059_3(ESN, FuelVolumeTotal, DEFVolumeTotal, OccurrenceTime)
            values (:ESN, :FuelVolumeTotal, :DEFVolumeTotal, to_date(:OccurrenceTime, 'YYYY-MM-DD HH24:MI:SS'))
            """
    parameters = {'ESN': jsonobj['ESN'],
                  'FuelVolumeTotal': jsonobj['FuelVolumeTotal'],
                  'DEFVolumeTotal': jsonobj['DEFVolumeTotal'],
                  'OccurrenceTime': jsonobj['OccurrenceTime']
                  }

    db.insert(sqlstr, parameters)

　　3. 日志文件logger.py：

import sys
import logging
from logging.handlers import TimedRotatingFileHandler
import os

def writeLog(message, filenames = "runtime.log"):
    logging.basicConfig(level=logging.WARNING,
                        format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                        filemode='a')
    formatter = logging.Formatter('%(asctime)s:%(filename)s:%(funcName)s:[line:%(lineno)d] %(levelname)s %(message)s')
    CURRENT_DIR = os.path.dirname(__file__)
    LOG_FILE = os.path.abspath(os.path.join(CURRENT_DIR, "logs", filenames))
    fileTimeHandler = TimedRotatingFileHandler(LOG_FILE, "D", 1, 0,encoding='utf-8')
    fileTimeHandler.suffix = "%Y%m%d.log"
    fileTimeHandler.setFormatter(formatter)
    loggers = logging.getLogger('')
    loggers.addHandler(fileTimeHandler)
    loggers.warn(message)
    loggers.handlers.pop()

二、cx_Oracle连接报错处理

　　1.错误信息

2020-05-29 13:52:03,960 logger.py[line:18] WARNING Oracle数据库连接错误:Traceback (most recent call last):
  File "C:/Users/mabot/Desktop/DataIn/bfcecdw/DataInput/database.py", line 48, in oraconnect
    encoding='utf-8'
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help

　　2.报错处理，参考网上教程(https://blog.csdn.net/qq_36227528/article/details/102758559)

　　下载连接对应的oracle版本的客户端安装包，将安装包中dll文件复制到python安装路径中即可。