【无标题】demo1

# coding=utf-8
import copy
import os
from openpyxl import load_workbook
import pymysql

from warnings import filterwarnings
from itertools import groupby
import logging


# 忽略mysql告警信息
filterwarnings("ignore", category=pymysql.Warning)

logging.basicConfig(level=logging.INFO,
                    filename='./log.txt',
                    filemode='w',
                    format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s')

data_key1=[]
data_key2=[]
data_key3=[]

log_info = []


# 获取工作表的内容
def get_excel_data(excel_path):
    # 获取文件路径
    filename = os.path.join(os.path.dirname(os.path.abspath(__file__)), excel_path)
    # 加载excel
    wb = load_workbook(filename)
    # 获取excel中的工作表名称
    sheets = wb.sheetnames
    #获取每个工作表的内容
    sheet1 =  wb[wb.sheetnames[0]]

    # 获取批次号
    result_sheet_pch = list(list(sheet1.iter_cols(min_row=2, min_col=1, max_col=1, values_only=True))[0])
    batch_number = list(set(result_sheet_pch))

    sheet1_key,sheet1 = transform_result(sheet1)
    sheet2_key,sheet2 = transform_result(wb[wb.sheetnames[1]])
    sheet3_key,sheet3 = transform_result(wb[wb.sheetnames[2]])

    global data_key1,data_key2,data_key3
    data_key1 = sheet1_key
    data_key2 = sheet2_key
    data_key3 = sheet3_key

    #调用排序的方法，对结果进行排序
    sheet1 = sort_data(data_key1,sheet1)
    sheet2 = sort_data(data_key2,sheet2)
    sheet3 = sort_data(data_key3,sheet3)


    return batch_number,sheet1,sheet2,sheet3,sheets

#根据批次号获取数据库内的数据
def get_db_by_batch_number(batch_number):
    conn = pymysql.connect(host="47.98.235.160", user="lj_test", password="lj_test", database="lj_api_test",
                           cursorclass=pymysql.cursors.DictCursor)
    # 使用cursor方法获取操作游标，得到一个可以执行sql语句，并且操作结果作为字典返回的游标
    cur = conn.cursor()
    batch_number = ",".join('%s' % id for id in batch_number)

    # 执行sql
    cur.execute("SELECT t.pch, t.ddh, t.ljh, t.tp, t.sl, t.sj FROM t_result AS t WHERE t.pch in("+batch_number+")")
    # # 获取全部数据 为list
    data1 = cur.fetchall()
    data1 = sort_data(data_key1,data1)


    cur.execute('SELECT t.pch, t.ddh, t.ljh, t.tp, t.sl, t.sj FROM t_result AS t WHERE t.pch = "2"')
    data2 = cur.fetchall()
    data2 = sort_data(data_key2, data2)

    cur.execute('SELECT t.pch, t.ddh, t.ljh, t.tp, t.sl, t.sj FROM t_result AS t WHERE t.pch = "2"')
    data3 = cur.fetchall()
    data3 = sort_data(data_key3, data3)

    # 关闭游标
    cur.close()
    # 关闭连接
    conn.close()
    return data1,data2,data3


def sheet_compareWith_data(excel_path):

    batch_number, sheet1, sheet2, sheet3,sheet_name = get_excel_data(excel_path)

    data1, data2, data3 = get_db_by_batch_number(batch_number)
    result_compare(sheet1,data1,sheet_name[0])
    result_compare(sheet2,data2,sheet_name[1])
    result_compare(sheet3,data3,sheet_name[2])











def result_compare(sheet,db_data,sheet_name):
    logging.info("========================"+sheet_name+"=================================")
    sheet1 = groupby(sheet, key=lambda x: (x["pch"]))
    data1 = groupby(db_data, key=lambda x: (x["pch"]))
    for s_key, s_group in sheet1:  # 1,3,5
        s_list = list(s_group)
        d_list = []
        for d_key, d_group in data1:  # 1,3,5
            if str(s_key) == str(d_key):
                d_list = list(d_group)
                break

        # 先判断长度相等
        if len(s_list) == len(d_list):
            # 判断每个值是否相等
            for i in range(len(s_list)):
                flag = True
                for key, value in s_list[i].items():
                    if str(value) != str(d_list[i][key]):
                        logging.error("不通过，数值不相等，批次号：" + str(s_key) + "，未通过的key:" + key)
                        logging.error("预期结果为：" + str(s_list[i]))
                        logging.error("实际结果为：" + str(d_list[i])+"\n")
                        flag = False
                        break
                if flag:
                    logging.info("通过，批次号：" + str(s_key)+"\n")



        else:
            logging.error("批次号：" + str(s_key) + "，长度不一致")
            logging.error("预期结果为：" + str(s_list))
            logging.error("实际结果为：" + str(d_list)+"\n")




#排序
def sort_data(keys,data):
    n_key = copy.deepcopy(keys)
    #根据所有的key排序
    n_key.reverse()
    for i in n_key:
        data = sorted(data, key=lambda obj: str(obj[i]))
    return data





# 将工作表的内容转换成想要的数据格式
def transform_result(sheet):
    # 获取工作表的数据
    result_sheet_datas = list(sheet.iter_rows(values_only=True))
    # 获取key(第一行)
    sheet_key = list(result_sheet_datas[0])
    sheet_value = result_sheet_datas[1:]
    sheet_result = [dict(zip(sheet_key, v)) for v in sheet_value]
    return sheet_key , sheet_result



if __name__ == '__main__':

    path = r'C:\Users\Administrator\Desktop\ljtest.xlsx'
    sheet_compareWith_data(path)



# import cx_Oracle
# def main():
# 	# 建立连接
# 	db = cs_Oracle.connect('username', 'password', 'url/servername:port')
# 	# 获取游标
# 	cursor = db.cursor()
# 	# 查询数据
# 	cursor.execute('SELECT * FROM t_temp')
# 	# 获取数据
# 	data = cursor.fetchall()
# 	print(data)
# 	# 获取字段名
# 	title = cursor.description
# 	print(title)