自动化测试验证工具(Python实现)

最新推荐文章于 2025-07-04 19:42:48 发布

幸运的小新粉

最新推荐文章于 2025-07-04 19:42:48 发布

阅读量740

点赞数

CC 4.0 BY-SA版权

分类专栏：数据随笔 python 文章标签：自动化 python 校验数据

本文链接：https://blog.csdn.net/qq_41902618/article/details/108539097

python 同时被 2 个专栏收录

26 篇文章

订阅专栏

数据随笔

22 篇文章

订阅专栏

本文介绍了一种使用Python和SQL进行数据比对的方法，主要针对两个数据表在特定日期和字段上的数据一致性检查。通过定义一个RunScript类，实现了从数据库获取表信息，构建SQL查询语句，执行数据比对并记录差异的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sys, os
from utils.logutil import register_loger
from utils.mysqlutil import MySQLUtil
from utils.confutil import ConfUtil


class RunScript(object):
    """
    统一作为执行sql脚本和python脚本的入口
    table1:         比对数据的表A
    table2:         比对数据的表B
    datecolumn:     批次字段
    etldate:        批次字段时间
    compcollist:    比对上的字段集合

    """

    def __init__(self, table1, table2, datecolumn, etldate, compcollist):
        self.table1 = table1
        self.table2 = table2
        self.datecolumn = datecolumn
        self.etldate = etldate
        self.compcollist = compcollist
        self.conn = MySQLUtil(ConfUtil().get_default_dbname())
        self.loger = register_loger(self.__class__.__name__)
        self.script_type = ""

    def run(self):
        try:
            self.conn.start_trans()
            self.loger.debug(self.table1, self.table2, self.datecolumn, self.etldate, self.compcollist)
            # 拿到 table1 中的字段
            sql = """
                  select distinct COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME = :TABLE
                        AND COLUMN_NAME NOT IN ('ID', :datecolumn ,'{compcollist}')
                      """.format(compcollist="','".join(compcollist))
            param = {"TABLE": self.table1, "datecolumn": self.datecolumn}
            tablecols = self.conn.execute(sql, param)
            # 拼装比对的字段
            compedstrs = ' '.join(['and a.{compcol} = b.{compcol} '.format(compcol=compcol) for compcol in compcollist])

            # 循环比较数据是否一致
            for col in tablecols:
                sql = """
                      SELECT COUNT(1) FROM  {table1} a INNER JOIN {table2} b 
                      ON 1=1
                      AND a.{datecolumn} = :ENDDATA
                      and a.{datecolumn} = b.{datecolumn}
                      {compedstr}
                      and a.{comcol} != b.{comcol}
                      """.format(table1=self.table1,table2=self.table2,datecolumn=datecolumn,comcol=col[0],compedstr = compedstrs)
                param = { "ENDDATA": self.etldate}
                diffcnt = self.conn.execute(sql, param).next()[0]
                if diffcnt > 0:
                    # 写入数据库
                    sql = """
                        INSERT INTO compare_table_date_log(TABLE1,TABLE2,COLNAME,diffcnt)
                            VALUES(:TABLE1,:TABLE2,:colname,:diffcnt)
                          """
                    param = {"TABLE1": self.table1, "TABLE2": self.table2, "colname": col[0], "diffcnt": diffcnt}
                    self.conn.execute(sql, param)
                    self.conn.commit()

        except Exception as e:
            msg = "任务[{0} {1}]执行失败,报错原因:[{2}]".format(self.table1, self.etldate, str(e))
            print(msg)
            self.loger.error(msg)
            # raise e


if __name__ == "__main__":
    if len(sys.argv) < 2:
        useage = """python3 {py_comp} table1 table2 [date_column] [ETL_DATE] [compared_column,]
    {doc}
    例如:
    python3 {py_script} table1 table2 enddate 20200331 COMCODE dataflag
    """.format(py_comp=os.path.basename(__file__), doc=RunScript.__doc__)
        print(useage)
        sys.exit(1)

    table1 = sys.argv[1]
    table2 = sys.argv[2]
    datecolumn = sys.argv[3]
    etldate = sys.argv[4]
    compcollist = sys.argv[5:]
    executor = RunScript(table1, table2, datecolumn, etldate, compcollist)
    executor.run()