Python之cx_Oracle、psycopg2模块处理数据库数据

冷不防 2022-04-13 05:40 339阅读 0赞

# coding=utf-8
    import sys
    import cx_Oracle
    import psycopg2
    import psycopg2.extras
    import datetime
    
    conn_oracle = cx_Oracle.connect("")
    print("Opened oracle successfully")
    cur_oracle = conn_oracle.cursor()
    
    conn_pg = psycopg2.connect(database='amdb', user='', password='', host='',
                               port='')
    print("Opened pg successfully")
    # cur_pg = conn_pg.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cur_pg = conn_pg.cursor()

def process_data(rows):
        rows = [('10.131.178.78', 'T10103', 'E4D058D6-E5CB-40FA-9A9D-04DA1D6DEF22-00064'),
                ('10.131.178.77', 'T60302', 'E4D058D6-E5CB-40FA-9A9D-04DA1D6DEF22-00055')]
        for row in rows:
            #数据处理关键代码
            ip_list.append("""\'%s\'""" % str(row[0]))
        pg_sql = "select resourceid from am_managedobject where resourcename in (%s)" % (",".join(ip_list))
        return pg_sql

def read_COLLECT(id_list):
        """读取COLLECT表"""
        ids = (",".join(id_list))
        collect_config_sql = "SELECT COLL_FUNC from COLLECT  where AGENT_CODE='APM_PG'"
        collect_config = exec_oracle_sql(collect_config_sql)
        result_list = []
        for cfg in collect_config:
           #数据库的COLL_FUNC 形式为select resourceid from am_managedobject where resourcename in (%s)
            result = exec_pg_sql(cfg[0] % ids)
            result_list.append(result)
        return result_list

def get_some_info(new_dict, attr_code, result, perf_attr):
        """获取操作系统信息,插入数据库"""
        print(new_dict, attr_code, result, perf_attr)
        #Oracle数据库处理日期方法
        dt = datetime.datetime.now()
        perf_attr_sql = ""
        for resids in result:
            for resid in resids:
                for key in new_dict.keys():
                    if int(key) == resid[0]:
                        params = {'devtype': new_dict[key][1], 'devid': new_dict[key][2], 'attrcode': attr_code,
                                  'result': resid[1], 'dt': dt}
                        if perf_attr == 2:
                            try:
                                cur_oracle.execute(
                                    'insert into ATTR_RESULT values(:devtype,:devid,:attrcode,:result,:dt)',
                                    params)
                                print("配置数据插入成功！成功数据为：%s" % params)
                            except cx_Oracle.IntegrityError as e:
                                print("配置数据插入失败，重复插入！失败数据为：%s" % params)
                        elif perf_attr == 1:
                            try:
                                cur_oracle.execute(
                                    'insert into PERF_RESULT values(:devtype,:devid,:attrcode,:result,:dt)',
                                    params)
                                print("性能数据插入成功！成功数据为：%s" % params)
                            except cx_Oracle.IntegrityError as e:
                                print("性能数据插入失败，重复插入！失败数据为：%s" % params)