linux 上python集成cx_Oracle流程

1、所需要的文件

1、https://www.oracle.com/database/technologies/instant-client/downloads.html
这是oracle迷你客户端的下载,根据你的oracle版本和服务器的版本进行选择，这里需要两个文件
    instantclient-basic-linux.x64-21.1.0.0.0.zip
    instantclient-sdk-linux.x64-21.1.0.0.0.zip
2、https://pypi.org/search/?q=cx_Oracle  # 这是轮子的下载地址，因为一般都是内网环境，通过这个安装cx_Oracle 模块,直接pip install 就可以了，没什么其他的依赖

 

2、将文件上传到服务器，

比如/opt/app/oracle，对两个文件进行解压，会解压到同一个目录/opt/app/oracle/instanclient11_2，类似这样的目录，这里要创建一个软连接，在连接数据库的时候会去找这个文件，不然会报错

ln -s libclntsh.so.11.1 libclntsh.so

3、设置环境变量，这就是把刚刚的oracle目录,加入到环境变量就可以了

export ORACLE_HOME="/opt/app/oracle/instanclient11_2"
export LD_LIBRARY_PATH="/opt/app/oracle/instanclient11_2"
PATH=$ORACLE_HOME:$LD_LIBRARY_PATH:$PATH
export PATH

4、我的测试模块基于 anaconda 2.7 和oracle11.0.2.0做的，这里有个参数可以保证中文写入oracle ,先确定oracle是什么字符集，然后设置os.environ['NLS_LANG']='AMERICAN_AMERICA.ZHS16GBK'   就可以了

#  coding=utf-8
import cx_Oracle
import pymysql
import json
import datetime as dt
import uuid,os


os.environ['NLS_LANG']='AMERICAN_AMERICA.ZHS16GBK'
class OracleDB(object):
    def  __init__(self,user,pwd,ip,port,sid):
        self.connect=cx_Oracle.connect(user,pwd,ip+":"+port+"/"+sid)
        self.cursor=self.connect.cursor()

    """处理数据二维数组，转换为json数据返回"""
    def select(self,sql):
        list=[]
        self.cursor.execute(sql)
        result=self.cursor.fetchall()
        col_name=self.cursor.description
        for row in result:
            dict={}
            for col in range(len(col_name)):
                key=col_name[col][0]
                value=row[col]
                dict[key]=value
            list.append(dict)
        js=json.dumps(list,ensure_ascii=False,indent=2,separators=(',',':'))
        return js

    def disconnect(self):
        self.cursor.close()
        self.connect.close()

    def insert(self,sql,list_param):
        try:
            self.cursor.executemany(sql,list_param)
            self.connect.commit()
            print("insert success")
        except Exception as e:
            print(e)
        finally:
            self.disconnect()

class mysqlDB(object):
    def  __init__(self,user,pwd,ip,port):
        connect = pymysql.connect(host=ip, port=port, user=user, passwd=pwd, db='mysql',
                               charset='utf8')
        self.connect=connect
        self.cursor=self.connect.cursor(cursor=pymysql.cursors.DictCursor)


    def _format_data(self,rows):
        DOMAIN = '023'
        res = []
        cols = ['ID', 'POINT_ID', 'DOMAIN', 'RESOURCEID', 'RESOURCENAME', 'RESOURCETYPE', 'TABLENAME', 'DATASOURCE',
                'SOURCENAME', 'STATTYPE', 'STAT_PERIOD', 'REC_COUNT', 'REC_STORE', 'OUT_COUNT', 'FAILED_COUNT',
                'STATUS_UPDATATIME','BEGINTIME', 'ENDTIME', 'BUSI_BEGINTIME', 'BUSI_ENDTIME', 'MODEL_NAME']

        for row in rows:
            tmp = []
            row['ID'] = str(uuid.uuid1())
            row['POINT_ID'] = DOMAIN + row['source_id']
            row['DOMAIN'] = DOMAIN
            row['RESOURCEID'] = row['source_id']
            row['RESOURCENAME'] = u'通联明细'
            row['RESOURCETYPE'] = u'通联明细'
            row['TABLENAME'] = 'EDGE_GROUPCALL_DETAIL' if row['source_id'] == 'BBD_CALL_INFO' else 'EDGE_GROUPMSG_DETAIL'
            row['DATASOURCE'] = 'ZT'
            row['SOURCENAME'] = 'TB_LTECDR'
            row['STATTYPE'] = 'INCR'
            row['STAT_PERIOD'] = ''
            row['REC_COUNT'] = row['rec_count']
            row['OUT_COUNT'] = row['out_count']
            row['FAILED_COUNT'] = 0
            row['STATUS_UPDATATIME'] = dt.datetime.now()
            row['BEGINTIME'] = row['compute_time']
            row['ENDTIME'] = row['compute_time'] + dt.timedelta(hours=1)
            row['BUSI_BEGINTIME'] = row['compute_time'].strftime('%Y:%m:%d 00:00:00')
            row['BUSI_ENDTIME'] = (row['compute_time'] + dt.timedelta(hours=1)).strftime('%Y:%m:%d 00:00:00')
            row['MODEL_NAME'] = 'LOAD_HIVE_BBD'
            for key in cols:
                tmp.append(row.get(key))
            res.append(tmp)
        return res


    """ 处理存储的原始数据,整理成汇智需要的格式"""
    def select_monitor_source(self):
        sql = u''' select source_id,compute_time,rec_count,out_count from pangu.tb_monitor_count where sync = 0 '''
        self.cursor.execute(sql)
        rows = self.cursor.fetchall()
        return self._format_data(rows)


    def disconnect(self):
        self.cursor.close()
        self.connect.close()

    def update_monitor_sync(self):
        try:
            sql = ''' update pangu.tb_monitor_count set sync = 1 where sync = 0 '''
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as e:
            self.connect.rollback()
            print(e)
        finally:
            self.disconnect()

def sync_monitor():
    bbd_oracle = OracleDB('bbd', 'bbd', '12.4.0.68', '1521', 'zyk')
    mysqldb = mysqlDB('bbdweb', 'bbdweb@lDiDE5LB', '12.68.1.32', 3306)
    cols = ['ID', 'POINT_ID', 'DOMAIN', 'RESOURCEID', 'RESOURCENAME', 'RESOURCETYPE', 'TABLENAME', 'DATASOURCE',
            'SOURCENAME',
            'STATTYPE', 'STAT_PERIOD', 'REC_COUNT', 'REC_STORE', 'OUT_COUNT', 'FAILED_COUNT', 'STATUS_UPDATATIME',
            'BEGINTIME',
            'ENDTIME', 'BUSI_BEGINTIME', 'BUSI_ENDTIME', 'MODEL_NAME']
    cols_expr = ','.join(cols)
    vals_expr = ':' + ',:'.join(cols)
    sql = ''' insert into BBD.TB_MONITOR_INCR (%s) values (%s) ''' % (cols_expr, vals_expr)
    rows = mysqldb.select_monitor_source()
    bbd_oracle.insert(sql, rows)
    mysqldb.update_monitor_sync()
    bbd_oracle.disconnect()
    mysqldb.disconnect()


if __name__ =="__main__":
    sync_monitor()