【测试工具】接口数据自动比对测试

背景介绍：
       最近做了一个做项目关于交易头寸新老报表数据比对的测试，涉及到的都是金额相关字段，一张表有很多字段需要核对，数据量较大。报表数据有在前端页面进行展示，但只提供查询功能，没有下载功能，无法导出数据报表；并且报表数据没有落地到数据库中，只保存在内存中。如何快速高效的进行一个完备而准确的测试是一个摆在当前很重要的问题 ？

方案选取：
        方案1：抽样比对。抽样选择某些数据，进行页面查询后比对。可能因为抽样数据的不完备性，造成测试结果存在较大的偏差
        方案2：全量比对。数据量较大，且需要比对的字段较多，全量测试耗时较长，且工作量繁重，如果有问题开发修复后回归测试，工作量也是很大。
        方案1和方案2经评估后均不太可行，如何能够快速高效解决这一问题呢？经过一番思索，想出如下解决方案：
        1.通过接口获取查询数据。
        2.将接口返回数据写入数据库。
        3.用sql自动进行数据比对并输出比对结果。

具体代码实现：
       具体代码实现如下：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @FileName  :datacompare.py
# @Author    :段小王爷
# @Time      :2021-06-02 16:56


import requests
import json
import openpyxl as xl
import os
import sys
import cx_Oracle
import xlrd
import urllib3
from urllib import parse
urllib3.disable_warnings()


path = os.getcwd()
# sysadmin用户的token
sysToken = '9974CA730C63C45FBD8E85C2F36A5644488D977E9F2F7A57381'
# 产品列表
fundlist = '888,111,222,333,444,555,5556'


def get_JSESSIONID():
    """
    获取接口的jsession信息，作为后续接口的header字段
    :return: JSESSIONID
    """
    url = f'https://************.jsp?page=tzrl&token={sysToken}&so&pUrl=https://********&_v=****0159'
    header = {
        "Upgrade-Insecure-Requests": "1",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36",
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Cookie": f"Hm_lvt_a04d53**************************f5f=1621415125,1621907751,1622195301,1622540520;  token={sysToken}"
    }

    respond = requests.get(url=url, headers=header, verify=False)
    JSESSIONID = respond.headers['Set-Cookie'].split(';')[0].split('=')[1]
    return JSESSIONID


def gen_excel(tablename, *result):
    """
    导出数据生成excel文档
    :param tablename: 函数名，作为生成的excel表名
    :param result: 接口返回的记录列表
    :return:
    """
    # 统计接口返回的记录列表
    total = len(result)
    print(f'{tablename} 总记录条数：' + str(total))
    title = list(dict(result[0]).keys())  # 获取第一条记录获取key作为表头
    f = xl.Workbook()
    # 设置sheet页名称
    for sheet in f:
        sheet.title = str(tablename)
    sheet = f.active
    sheet.append(title)
    for i in range(0, total):
        value = list(dict(result[i]).values())
        sheet.append(value)
    f.save("{}/{}".format(path, f'{tablename}.xlsx'))


def gen_test_result(tablename, title, detail):
    """
    生成测试结果
    :param tablename: 表名称
    :param title: 标题行
    :param detail: 差异数据
    :return:
    """
    f = xl.Workbook()
    for sheet in f:
        sheet.title = str(tablename)
    sheet = f.active
    sheet.append(title)
    for i in range(0, len(detail)):
        value = list(detail[i])
        sheet.append(list(value))
    f.save("{}/{}".format(path, f'测试结果{tablename}.xlsx'))


def login_db():
    """
    登录数据库
    :return:
    """
    db = None
    cr = None
    try:
        db = cx_Oracle.connect('数据库用户名', '密码', 'ip端口号')
        cr = db.cursor()
    except Exception as e:
        # mb.showwarning(message = str(e))
        print('数据库连接失败'+str(e))
    return db, cr


def import_db(tablename, *result):
    """
    把数据导入数据库
    :param tablename: 表名
    :param result: 接口返回的数据列表
    :return:
    """
    db, cr = login_db()
    try:
        drop_sql = f"select table_name from user_tables  where table_name = upper('{tablename}')"
        # print(drop_sql)
        cr.execute(drop_sql)
        num = len(cr.fetchall())
        if int(num) > 0:
            oracle_sql = 'drop table ' + tablename
            # print(oracle_sql)
            cr.execute(oracle_sql)
            db.commit()
            print(f'{tablename} 旧数据删除成功')
        else:
            pass
        # print('删除成功')
    except Exception as e:
        # mb.showwarning( message=str( e ).encode( 'utf-8' ) )
        print('表不存在'+str(e))

    title = list(dict(result[0]).keys())
    c = ' '
    b = ''
    i = 1
    param = []
    for field in title:
        insert_table = ':' + str(i) + ','
        b = b + insert_table
        i = i+1
        create_table_sql = field + '  varchar2(500),'
        c = c + create_table_sql
    create_table_sql = c[:-1]
    create_table_sql = 'create  table ' + tablename + '(' + create_table_sql + ')'
    # print(create_table_sql)
    cr.execute(create_table_sql)
    insert_table = b[:-1]
    # print(insert_table)
    if len(result) > 0:
        for num in range(len(result)):
            single_row = list(dict(result[num]).values())
            # print(param)
            param.append(single_row)
    sql = "insert into " + tablename + " values( " + insert_table + ")"
    # print(sql)
    try:
        cr.executemany(sql, param)
        # mb.showwarning( message='数据导入成功' + tablename )
    except Exception as e:
        # mb.showwarning( message=str( e ).encode( 'utf-8' ) )
        print('数据导入失败')
    finally:
        db.commit()
        # print('数据写入成功')
    cr.close()
    db.close()


# 头寸汇总表
def Old_TouCunHuiZongBiao():
    """

    :return:
    """
    JSESSIONID = get_JSESSIONID()
    tablename = sys._getframe().f_code.co_name
    url = 'https://***********/****.biz.ext'
    header = {
        "Accept": "application/json, text/javascript, */*",
        'X-Requested-With': "XMLHttpRequest",
        "Content-Type": "application/json;charset=UTF-8",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Cookie": f"Hm_lvt_a04*****5f=1621415125,1621907751,1622195301,1622540520; JSESSIONID={JSESSIONID}; token={sysToken}"

         }
    data = {
            "pageIndex": 0,
            "pageSize": 1000,
            "sortField": "",
            "sortOrder": "",
            "page": {"begin": 0, "length": 1000}
             }

    respond = requests.post(url=url, data=json.dumps(data), headers=header, verify=False).json()
    # print(respond)
    result = respond['resultObjectList']  # 获取记录
    import_db(tablename, *result)
    gen_excel(tablename, *result)


# 汇总检查表新
def New_TouCunHuiZongBiao():
    tablename = sys._getframe().f_code.co_name
    url = 'https://*********************************/query'
    header = {
        "Accept": "application/json, text/javascript, */*",
        'X-Requested-With': "XMLHttpRequest",
        "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9"
         }

    data = {
        "page":1,
        "size":50,
        "sort":-1,
        "unitName":"",
        "labelId":"",
        "productCodes":"",
        "sysToken":f"{sysToken}"
    }

    respond = requests.post(url=url, data=data, headers=header, verify=False).json()
    # print(respond)
    result = respond['body']['rows']
    import_db(tablename, *result)
    gen_excel( tablename, *result )


# 交易所头寸核查表
def Old_JiaoYiSuoTouCunHeChaBiao():
    JSESSIONID = get_JSESSIONID()
    tablename = sys._getframe().f_code.co_name
    url = 'https://*************biz.ext'
    header = {
        "sec-ch-ua": '''" Not A;Brand";v="99", "Chromium";v="90", "Google Chrome";v="90"''',
        "Accept": "*/*",
        'X-Requested-With': "XMLHttpRequest",
        "sec-ch-ua-mobile": "?0",
        "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
        "Accept-Encoding": "gzip, deflate, br",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Sec-Fetch-Site": "same-origin",
        "Sec-Fetch-Mode": "cors",
        "Sec-Fetch-Dest": "empty",
        "Cookie": f"Hm_lvt_a04d******1621415125,1621907751,1622195301,1622540520; JSESSIONID={JSESSIONID}; token={sysToken}"
    }
    # 对入参进行urlencoded编码
    data_data = '{"fundNameList":"","userId":"sysadmin"}'
    data_url = "/enable/exchange/position"
    data = f'data={parse.quote_plus(data_data)}&url={parse.quote_plus(data_url)}'
    # 发送接口请求
    respond = requests.post(url=url, data=data, headers=header, verify=False).json()
    result = json.loads(respond['ret'])['body']
    import_db(tablename, *result)
    gen_excel( tablename, *result )



# 标准券监控表
def Old_BiaoZhunQuanJianKongBiao():
    """
    查询标准券监控表数据
    :return:
    """
    JSESSIONID = get_JSESSIONID()
    tablename = sys._getframe().f_code.co_name
    url = 'https://************.biz.ext'
    header = {
        "Accept": "*/*",
        'X-Requested-With': "XMLHttpRequest",
        "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Cookie": f"Hm_lvt_a0****=1621415125,1621907751,1622195301,1622540520; JSESSIONID={JSESSIONID}; token={sysToken}"
    }
    # 对入参进行urlencoded编码
    data_url = '/enable/standardstock/allPredict'
    data_data = '{"fundNameList":""}'
    data = f'data={parse.quote_plus(data_data)}&url={parse.quote_plus(data_url)}'
       respond = requests.post(url=url, data=data, headers=header, verify=False).json()
    # print(respond)
    result = json.loads(respond['ret'])
    import_db(tablename, *result)
    gen_excel(tablename, *result)


# 标准券监控表新（入参需要修改）
def New_BiaoZhunQuanJianKongBiao():
    """
    查询标准券监控表数据
    :return:
    """
    tablename = sys._getframe().f_code.co_name
    url = 'https://**********/query'
    header = {
        "Accept": "application/json, text/plain, */*",
        'X-Requested-With': "XMLHttpRequest",
        "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9"
    }
    # 待bug修复后，去掉productCode的值
    data = f'productCode={fundlist}&filter=0&sysToken={sysToken}'
    respond = requests.post(url=url, data=data, headers=header, verify=False).json()
    # print(respond)
    result = respond['body']
    import_db(tablename, *result)
    gen_excel(tablename, *result)


def test_data(tablename):
    db, cr = login_db()
    sql_TouCunHuiZongBiao = """ 
               select 
               a.productCode,
               a.UNITID,
               a.exchangePostition_SH,
               b.JYSTC_SH,
               a.exchangePostition_SZ,
               b.JYSTC_SZ,
               a.t0Position,
               b.T0_BALANCE,
               a.t1Position,
               b.T1_BALANCE,
               a.todayT0TradeIntention,
               b.EN_YHJ_ZQ,
               a.todayT0FrontDeal,
               b.EN_YHJ_ZQ_T0CJ,
               a.predayT1CashIncreament,
               b.EN_YHJ_ZQ_T1CJ,
               a.todayReverseRepoExpire,
               b.EN_YHJ_RQGH,
               a.todayBondCashInterest,
               b.EN_YHJ_DXDF,
               a.nextDayRepoExpireFrozen,
               b.EN_YHJ_TQDJ,
               a.insUsed_SH,
               b.EN_JYS_ZLZY1,
               a.insUsed_SZ,
               b.EN_JYS_ZLZY2,
               a.stockWarrantTrading_SH,
               b.EN_JYS_QZMM1,
               a.stockWarrantTrading_SZ,
               b.EN_JYS_QZMM2,
               a.bondTrading_SH,
               b.EN_JYS_ZQMM1,
               a.bondTrading_SZ,
               b.EN_JYS_ZQMM2,
               a.repoFirst_SH,
               b.EN_JYS_RZHG1,
               a.repoFirst_SZ,
               b.EN_JYS_RZHG2,
               a.repoExpire_SH,
               b.EN_JYS_RZGH1,
               a.repoExpire_SZ,
               b.EN_JYS_RZGH2,
               a.reverseRepoFirst_SH,
               b.EN_JYS_RQHG1,
               a.reverseRepoFirst_SZ,
               b.EN_JYS_RQHG2,
               a.reverseRepoExpire_SH,
               b.EN_JYS_RQGH1,
               a.reverseRepoExpire_SZ,
               b.EN_JYS_RQGH2,
               a.unsecuredSettlement_SH,
               b.EN_JYS_FDBJS1,
               a.unsecuredSettlement_SZ,
               b.EN_JYS_FDBJS2,
               a.depositAndWithdraw,
               b.EN_XX_CKZQK,
               a.fundRedeemAmt,
               b.EN_WJS_JJSH,
               a.offlinePurchaseAmt,
               b.EN_WJS_WXSG,
               a.provisionMarginAdvanceFrozen,
               b.EN_BFJ_TQDJ,
               a.redeemAndBonusAdvanceFrozen,
               b.EN_SH_TQDJ,
               a.freeAdvanceFrozen,
               b.EN_FYTQDJ,
               a.cashManualUnfreeze,
               b.EN_ZJSGDJ,
               a.vatAndSurtax,
               b.EN_ZJJD,
               a.appbidsAndFetch,
               b.EN_DRSGDJ,
               a.firstMktNewBond,
               b.EN_YJXZ,
               a.agreementRepo,
               b.EN_XYHG,
               a.t0TradePosition,
               b.T0_POS,
               a.t0AvailableExcludeT1Change,
               b.T0_POS2,
               a.t1TradeAvailable,
               b.T1_POS
               from New_TouCunHuiZongBiao   a
               inner join Old_TouCunHuiZongBiao   b
               on a.productCode = b.VC_FUNDCODE
               and  COALESCE(a.UNITID,'a') = COALESCE(b.L_ASSET_ID,'a')
               where 1 = 1 
               and to_number(REPLACE(COALESCE(a.exchangePostition_SH,'0.00'),',' ))<> to_number(REPLACE(b.JYSTC_SH,','))
                or to_number(REPLACE(COALESCE(a.exchangePostition_SZ,'0.00'),',' ))<> to_number(REPLACE(b.JYSTC_SZ,','))
                or to_number(REPLACE(COALESCE(a.t0Position,'0.00'),',' ))<> to_number(REPLACE(b.T0_BALANCE,','))
                or to_number(REPLACE(COALESCE(a.t1Position,'0.00'),',' ))<> to_number(REPLACE(b.T1_BALANCE,','))
                or to_number(REPLACE(COALESCE(a.todayT0TradeIntention,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_ZQ,','))
                or to_number(REPLACE(COALESCE(a.todayT0FrontDeal,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_ZQ_T0CJ,','))
                or to_number(REPLACE(COALESCE(a.predayT1CashIncreament,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_ZQ_T1CJ,','))
                or to_number(REPLACE(COALESCE(a.todayReverseRepoExpire,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_RQGH,','))
                or to_number(REPLACE(COALESCE(a.todayBondCashInterest,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_DXDF,','))
                or to_number(REPLACE(COALESCE(a.nextDayRepoExpireFrozen,'0.00'),',' ))<> to_number(REPLACE(b.EN_YHJ_TQDJ,','))
                or to_number(REPLACE(COALESCE(a.insUsed_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_ZLZY1,','))
                or to_number(REPLACE(COALESCE(a.insUsed_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_ZLZY2,','))
                or to_number(REPLACE(COALESCE(a.stockWarrantTrading_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_QZMM1,','))
                or to_number(REPLACE(COALESCE(a.stockWarrantTrading_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_QZMM2,','))
                or to_number(REPLACE(COALESCE(a.bondTrading_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_ZQMM1,','))
                or to_number(REPLACE(COALESCE(a.bondTrading_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_ZQMM2,','))
                or to_number(REPLACE(COALESCE(a.repoFirst_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RZHG1,','))
                or to_number(REPLACE(COALESCE(a.repoFirst_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RZHG2,','))
                or to_number(REPLACE(COALESCE(a.repoExpire_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RZGH1,','))
                or to_number(REPLACE(COALESCE(a.repoExpire_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RZGH2,','))
                or to_number(REPLACE(COALESCE(a.reverseRepoFirst_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RQHG1,','))
                or to_number(REPLACE(COALESCE(a.reverseRepoFirst_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RQHG2,','))
                or to_number(REPLACE(COALESCE(a.reverseRepoExpire_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RQGH1,','))
                or to_number(REPLACE(COALESCE(a.reverseRepoExpire_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_RQGH2,','))
                or to_number(REPLACE(COALESCE(a.unsecuredSettlement_SH,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_FDBJS1,','))
                or to_number(REPLACE(COALESCE(a.unsecuredSettlement_SZ,'0.00'),',' ))<> to_number(REPLACE(b.EN_JYS_FDBJS2,','))
                or to_number(REPLACE(COALESCE(a.depositAndWithdraw,'0.00'),',' ))<> to_number(REPLACE(b.EN_XX_CKZQK,','))
                or to_number(REPLACE(COALESCE(a.fundRedeemAmt,'0.00'),',' ))<> to_number(REPLACE(b.EN_WJS_JJSH,','))
                or to_number(REPLACE(COALESCE(a.offlinePurchaseAmt,'0.00'),',' ))<> to_number(REPLACE(b.EN_WJS_WXSG,','))
                or to_number(REPLACE(COALESCE(a.provisionMarginAdvanceFrozen,'0.00'),',' ))<> to_number(REPLACE(b.EN_BFJ_TQDJ,','))
                or to_number(REPLACE(COALESCE(a.redeemAndBonusAdvanceFrozen,'0.00'),',' ))<> to_number(REPLACE(b.EN_SH_TQDJ,','))
                or to_number(REPLACE(COALESCE(a.freeAdvanceFrozen,'0.00'),',' ))<> to_number(REPLACE(b.EN_FYTQDJ,','))
                or to_number(REPLACE(COALESCE(a.cashManualUnfreeze,'0.00'),',' ))<> to_number(REPLACE(b.EN_ZJSGDJ,','))
                or to_number(REPLACE(COALESCE(a.vatAndSurtax,'0.00'),',' ))<> to_number(REPLACE(b.EN_ZJJD,','))
                or to_number(REPLACE(COALESCE(a.appbidsAndFetch,'0.00'),',' ))<> to_number(REPLACE(b.EN_DRSGDJ,','))
                or to_number(REPLACE(COALESCE(a.firstMktNewBond,'0.00'),',' ))<> to_number(REPLACE(b.EN_YJXZ,','))
                or to_number(REPLACE(COALESCE(a.agreementRepo,'0.00'),',' ))<> to_number(REPLACE(b.EN_XYHG,','))
                or to_number(REPLACE(COALESCE(a.t0TradePosition,'0.00'),',' ))<> to_number(REPLACE(b.T0_POS,','))
                or to_number(REPLACE(COALESCE(a.t0AvailableExcludeT1Change,'0.00'),',' ))<> to_number(REPLACE(b.T0_POS2,','))
                or to_number(REPLACE(COALESCE(a.t1TradeAvailable,'0.00'),',' ))<> to_number(REPLACE(b.T1_POS,','))
           """
    sql_TouCunHuiZongBiao_PK = """
        select 
            a.*
        from New_TouCunHuiZongBiao   a
        inner join Old_TouCunHuiZongBiao   b
            on a.productCode = b.VC_FUNDCODE
            and  COALESCE(a.UNITID,'a') = COALESCE(b.L_ASSET_ID,'a')
    """

    sql_JiaoYiSuoTouCunHeChaBiao = """
                   select 
               a.fundCode,
               a.assetID,
               a.assetName,
               b.vcAssetName,
               a.status,
               b.vcStatus,
               a.enBalance,
               b.enBalance,
               a.enAdjust,
               b.enAdjust,
               a.enAdjustedBalance,
               b.enAdjustedBalance,
               a.enRiskReserveRatio,
               b.enRiskReserveRatio,
               a.vcRemarks,
               b.vcRemarks,
               a.vcBalanceModifyLog,
               b.vcBalanceModifyLog
               from New_JiaoYiSuoTouCunHeChaBiao a
               inner join Old_JiaoYiSuoTouCunHeChaBiao b
               on a.fundCode = b.vcFundCode
               and a.assetID = b.lassetID
               where 1 = 1
               and a.assetName <> b.vcAssetName
               or a.status <> b.vcStatus
               or a.enBalance <> b.enBalance
               or a.enAdjust <> b.enAdjust
               or a.enAdjustedBalance <> b.enAdjustedBalance
               or a.enRiskReserveRatio <> b.enRiskReserveRatio
               or a.vcRemarks <> b.vcRemarks
               or a.vcBalanceModifyLog <> b.vcBalanceModifyLog
               or a.vcStatusModifyLog <> b.vcStatusModifyLog
               or a.ldate <> b.ldate
           """
    sql_JiaoYiSuoTouCunHeChaBiao_PK = """
        select 
            a.fundCode,
            a.assetID,
            a.assetName,
            b.vcAssetName,
            a.status,
            b.vcStatus,
            a.enBalance,
            b.enBalance,
            a.enAdjust,
            b.enAdjust,
            a.enAdjustedBalance,
            b.enAdjustedBalance,
            a.enRiskReserveRatio,
            b.enRiskReserveRatio,
            a.vcRemarks,
            b.vcRemarks,
            a.vcBalanceModifyLog,
            b.vcBalanceModifyLog
        from New_JiaoYiSuoTouCunHeChaBiao a
        inner join Old_JiaoYiSuoTouCunHeChaBiao b
            on a.fundCode = b.vcFundCode
            and a.assetID = b.lassetID
    """

    sql_BiaoZhunQuanJianKongBiao = """
                   select  
               a.vcfundcode,
               a.vccombino,
               a.vcReportCode,
               b.vcReportCode,
               a.lt1UsableRzAmount,
               b.lT1UsableRzAmount,
               a.lsequelGap,
               b.lSequelGap,
               a.cmarketNo,
               b.cMarketNo,
               a.lamount,
               b.lAmount,
               a.lrzAmount,
               b.lRzAmount,
               a.dusingRate,
               b.dUsingRate,
               a.lt1RzAmount,
               b.lT1RzAmount
               from New_BiaoZhunQuanJianKongBiao a
               inner join Old_BiaoZhunQuanJianKongBiao b
               on a.vcfundcode = b.vcfundcode
               and a.vccombino = b.vccombino
               and a.vcStockHolder = b.vcStockHolder
               where 1 = 1
               and a.vcReportCode <> b.vcReportCode
               or a.lt1UsableRzAmount <> b.lT1UsableRzAmount
               or a.lsequelGap <> b.lSequelGap
               or a.cmarketNo <> b.cMarketNo
               or a.lamount <> b.lAmount
               or a.lrzAmount <> b.lRzAmount
               or a.dusingRate <> b.dUsingRate
               or a.lt1RzAmount <> b.lT1RzAmount
           """
    sql_BiaoZhunQuanJianKongBiao_PK = """ 
        select  
               a.vcfundcode,
               a.vccombino,
               a.vcReportCode,
               b.vcReportCode,
               a.lt1UsableRzAmount,
               b.lT1UsableRzAmount,
               a.lsequelGap,
               b.lSequelGap,
               a.cmarketNo,
               b.cMarketNo,
               a.lamount,
               b.lAmount,
               a.lrzAmount,
               b.lRzAmount,
               a.dusingRate,
               b.dUsingRate,
               a.lt1RzAmount,
               b.lT1RzAmount
        from New_BiaoZhunQuanJianKongBiao a
        inner join Old_BiaoZhunQuanJianKongBiao b
             on a.vcfundcode = b.vcfundcode
            and a.vccombino = b.vccombino
            and a.vcStockHolder = b.vcStockHolder
        where 1 = 1
            and a.vcReportCode = b.vcReportCode
            and a.lt1UsableRzAmount = b.lT1UsableRzAmount
            and a.lsequelGap = b.lSequelGap
            and a.cmarketNo = b.cMarketNo
            and a.lamount = b.lAmount
            and a.lrzAmount = b.lRzAmount
            and a.dusingRate = b.dUsingRate
            and a.lt1RzAmount = b.lT1RzAmount
        """
    tables = ['TouCunHuiZongBiao', 'JiaoYiSuoTouCunHeChaBiao', 'BiaoZhunQuanJianKongBiao']
    print(f'开始核对：{tablename}')
    if tablename in tables:
        eval(f'New_{tablename}()')
        eval(f'Old_{tablename}()')
        # 查询主键相等记录数
        eval(f'cr.execute(sql_{tablename}_PK)')  # 主键关联一致的记录数
        PK_count = len(cr.fetchall())
        print(f"{tablename} 主键关联相等记录数：{PK_count}条")

        # 按照主键关联查询不一致的记录
        eval(f'cr.execute(sql_{tablename})')
        detail = cr.fetchall()
        title = [i[0] for i in cr.description]
        result = len(detail)
        if result == 0:
            print(f"{tablename} 新老数据一致，测试通过\n")
        else:
            gen_test_result(tablename, title, detail)
            print(f"{tablename} 有{result}条数据不一致，具体差异内容见Excel\n")
    else:
        print(f'表名入参错误,不在范围内：{tables}，请检查重新输入！\n')


if __name__ == '__main__':
    test_data('JiaoYiSuoTouCunHeChaBiao')
    test_data('BiaoZhunQuanJianKongBiao')
    test_data('TouCunHuiZongBiao')

运行结果如下：