#!/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()
自动化测试验证工具(Python实现)
最新推荐文章于 2024-05-27 14:27:19 发布