python使用cx_Oracle和pandas实现数据对比写入excel

每天坚持写一点东西，加油搬砖人

1.新数据读取

import cx_Oracle as Oracle
from const import Const as CS
import pandas as pd


class SelectOrder(object):
    def __init__(self, tns, order_id):
        self.db = Oracle.connect(tns)
        self.cursor = self.db.cursor()
        self.order_id = order_id

    def select_order_info(self):
        self.cursor.execute(CS.main_trade.format(order_id=self.order_id))
        row_values = self.cursor.fetchall()
        row_key_info = self.cursor.description
        data_key = []
        for i in row_key_info:
            data_key.append(i[0])
        trade_data_dict = []
        for i in row_values:
            trade_data_dict.append(dict(zip(data_key, i)))
        return trade_data_dict

    def get_trade_info(self, trade_data_dict):
        user_id = "USER_ID:"
        trade_id = "TRADE_ID:"
        for i in trade_data_dict:
            user_id = user_id+str(i.get("USER_ID")) + ","
            trade_id = trade_id+str(i.get("TRADE_ID"))+","

    def select_table_name(self):
        values = self.select_order_info()
        print(values)
        table_name = values[0].get("INTF_ID")
        table_names = table_name.split(',')
        print(table_names)
        return table_names

    def select_trade_id(self):
        values = self.select_order_info()
        print(values)
        trade_id = values[0].get("TRADE_ID")
        # trade_ids = trade_id.split(',')
        print(trade_id)
        return trade_id

    def select_table_info(self):
        table_name = self.select_table_name()
        print(table_name)
        trade_id = self.select_trade_id()
        # df = pd.DataFrame()
        # df.to_excel("data_info.xlsx")
        data_dict = {}
        for i in table_name:
            self.cursor.execute(CS.trade_info.format(table_name=i, trade_id=trade_id))
            row_value = self.cursor.fetchall()
            table_key = self.cursor.description
            data_key = []
            for j in table_key:
                data_key.append(j[0])
            one_data_list = []
            for k in row_value:
                one_data_list.append(dict(zip(data_key, map(lambda x: self.replace_data_none(x), k))))
            print("one_data_list:", one_data_list)
            data_dict[i] = one_data_list
        return data_dict

    def replace_data_none(self, x):
        return '' if x is None else str(x)

    def write_excel(self, data_dict):
        print(data_dict)
        with pd.ExcelWriter("data_info.xlsx") as wt:
            for key, value in data_dict.items():
                df = pd.DataFrame(value)
                df.to_excel(wt, sheet_name=key, index=False)
        print("创建完成")

    def __del__(self):
        self.cursor.close()
        print("关闭路由")
        self.db.close()
        print("关闭db")


# if __name__ == '__main__':
#     sq = SelectOrder(CS.JOUR42, "3121013038752937")
#     # sq.select_order_info()
#     # table_name = sq.select_table_name()
#     list_data = sq.select_table_info()
#     sq.write_excel(list_data)

 

2.老数据读取

import cx_Oracle as Oracle
from const import Const as CS
import pandas as pd


class SelectOldOrder(object):
    def __init__(self, tns, order_id):
        self.db = Oracle.connect(tns)
        self.cursor = self.db.cursor()
        self.order_id = order_id

    def select_old_order_info(self):
        self.cursor.execute(CS.old_main_trade.format(order_id=self.order_id))
        row_values = self.cursor.fetchall()
        row_key_info = self.cursor.description
        data_key = []
        for i in row_key_info:
            data_key.append(i[0])
        trade_data_dict = []
        for i in row_values:
            trade_data_dict.append(dict(zip(data_key, i)))
        return trade_data_dict

    def select_old_table_name(self):
        values = self.select_old_order_info()
        table_name = values[0].get("INTF_ID")
        table_name_list = table_name.split(',')
        table_name_list.remove("TF_B_TRACE_TRADE")
        table_name_list.remove("")
        return table_name_list

    def select_old_trade_id(self):
        values = self.select_old_order_info()
        trade_id_list = values[0].get("TRADE_ID")
        # trade_ids = trade_id.split(',')
        return trade_id_list

    def select_old_table_info(self):
        table_name = self.select_old_table_name()
        print(table_name)
        trade_id = self.select_old_trade_id()
        data_dict = {}
        for i in table_name:
            if i == "TF_B_TRADE":
                self.cursor.execute(CS.old_main_trade_1.format(trade_id=trade_id))
            else:
                self.cursor.execute(CS.old_trade_info.format(table_name=i, trade_id=trade_id))
                row_value = self.cursor.fetchall()
                table_key = self.cursor.description
                data_key = []
                for j in table_key:
                    data_key.append(j[0])
                one_data_list = []
                for k in row_value:
                    one_data_list.append(dict(zip(data_key, map(lambda x: self.replace_data_none(x), k))))
                print("one_data_list:", one_data_list)
                data_dict[i] = one_data_list
        return data_dict

    def replace_data_none(self, x):
        return '' if x is None else str(x)

    def write_excel(self, data_dict):
        print(data_dict)
        with pd.ExcelWriter("old_data_info.xlsx") as wt:
            for key, value in data_dict.items():
                df = pd.DataFrame(value)
                df.to_excel(wt, sheet_name=key, index=False)
        print("创建完成")

    def __del__(self):
        self.cursor.close()
        print("关闭路由")
        self.db.close()
        print("关闭db")


# if __name__ == '__main__':
#     old_sq = SelectOldOrder(CS.old_jour42, "3119011001315425")
#     # table_name = old_sq.select_old_table_name()
#     # print(table_name)
#     # trade_id = old_sq.select_old_trade_id()
#     data_dict = old_sq.select_old_table_info()
#     print(data_dict)
#     old_sq.write_excel(data_dict)

 

3.主函数

from data_info import SelectOrder as Oder
from old_data_info import SelectOldOrder as OldOrder
from const import Const as cs
import pandas as pd


class MainRun(object):
    def __init__(self):
        pass

    def write_to_excel(self, old_data_dict):
        with pd.ExcelWriter("data_info.xlsx") as wt:
            for key, value in old_data_dict.items():
                df = pd.DataFrame(value)
                df.to_excel(wt, sheet_name=key, index=False)

    def back_hand_to_excel(self, old_list_data, new_list_data):
        with pd.ExcelWriter("data_info.xlsx") as wt:
            for key, value in old_list_data.items():
                df = pd.DataFrame(value)
                df.to_excel(wt, sheet_name=key, index=False)
                if key in list(new_list_data.keys()):
                    df_new = pd.DataFrame(new_list_data[key])
                    df1 = df_new.style.apply(self.highlight_max, axis=1)
                    print(df1)
                    df1.to_excel(wt, sheet_name=key, startrow=len(df) + 1, index=False,
                                 columns=self.data_columns(df, df_new),
                                 header=False)

    def highlight_max(self, s):
        lst = []
        for i in s:
            if len(i) >= 0:
                lst.append('background-color:yellow; color:red;')
        return lst

    def data_columns(self, df_old, df_new):
        old_column = list(df_old.columns)
        new_column = list(df_new.columns)
        if len(old_column) > 0:
            data_column = sorted(new_column, key=lambda x: (old_column.index(x) if x in old_column else len(new_column)))
        else:
            data_column = new_column
        return data_column

    def main_run(self, old_order_id, new_order_order_id):
        old_order = OldOrder(cs.old_jour42, old_order_id)
        old_list_data = old_order.select_old_table_info()
        # self.write_to_excel(old_list_data)
        new_order = Oder(cs.JOUR42, new_order_order_id)
        new_list = new_order.select_table_info()
        self.back_hand_to_excel(old_list_data=old_list_data, new_list_data=new_list)


if __name__ == '__main__':
    mr = MainRun()
    mr.main_run(old_order_id="3119011001315425", new_order_order_id="3121013038752937")

 

 

3.配置

class Const(object):
    main_trade = "select * from uop_jour42.tf_b_trade_2021 t where 1=1 and t.order_id ='{order_id}' "
    JOUR42 = "UOP_JOUR42/Abdf_jour2_1357@10.230.55.84:16821/hnjourdb2"
    trade_info = "select * from uop_jour42.{table_name}_2021 t where 1=1 and t.trade_id ='{trade_id}'"
    old_jour42 = "UOP_JOUR42/Tgbv_213@192.192.1.33:1688/JOURPDB"
    old_main_trade = "select * from uop_jour42.tf_bh_trade t where 1=1 and t.order_id ='{order_id}'"
    old_trade_info = "select * from uop_jour42.{table_name} t where 1=1 and t.trade_id ='{trade_id}'"
    old_main_trade_1 = "select * from uop_jour42.tf_bh_trade t where 1=1 and t.trade_id ='{trade_id}'"

 

结构

效果