Python之cx_Oracle、psycopg2模块处理数据库数据-蒲公英云

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

冷不防 2022-04-13 05:40 416阅读 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)