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

#!/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()

升级点：支持通过配置