def rowcount_test(self, source_id, source_pwd, target_id,target_pwd):
'''read config file for the table list'''
conf = ReadConfig()
table_list = conf.Read_table_list()
'''define source db,target db'''
source_db_node = conf.Read_source_db_node()
target_db_node = conf.Read_target_db_node()
rowcount_report = []
fail_count = 0
for table in table_list:
rowcount_result = dict()
source_table = table['SOURCE_SCHEMA'] + '.' + table['SOURCE_TABLE']
target_table = table['TARGET_SCHEMA'] + '.' + table['TARGET_TABLE']
source_sql = 'select count(*) as rowcount from {}'.format(source_table)
target_sql = 'select count(*) as rowcount from {}'.format(target_table)
'''connect to source and target DB to run the sql'''
'''run sql in target first'''
rs_source = db_connect.exec_sql_common(source_db_node, source_id, source_pwd, source_sql)
source_rowcount = rs_source[0].rowcount
rs_target = db_connect.exec_sql_common(target_db_node, target_id, target_pwd, target_sql)
target_rowcount = rs_target[0].rowcount
if source_rowcount == target_rowcount:
status = 'PASS'
else:
status = 'FAIL'
fail_count += 1
rowcount_result['SOURCE_TABLE'] = source_table
rowcount_result['TARGET_TABLE'] = target_table
rowcount_result['SOURCE_ROWCOUNT'] = source_rowcount
rowcount_result['TARGET_ROWCOUNT'] = target_rowcount
rowcount_result['STATUS'] = status
rowcount_report.append(rowcount_result)
with open("tmp/rowcountreport.csv", "w") as f:
f.write(str(rowcount_report))
if fail_count > 0:
print("The rowcount test passed,detail see the rowcount report")
else:
print("The rowcount test failed,detail see the rowcount report")
raise TestException.RowcountError()
def sample_data_test(self, id, pwd):
'''read conf'''
conf = ReadConfig()
table_list = conf.Read_table_list()
source_db_node = conf.Read_source_db_node()
target_db_node = conf.Read_target_db_node()
sample_data_report = []
failed_sample_report = []
fail_cell_count = 0
for table in table_list:
'''get the source target column mapping'''
source_target_column_mapping = self.generate_source_target_column_map(id, pwd,table['SOURCE_SCHEMA'],\
table['SOURCE_TABLE'], table['TARGET_SCHEMA'], table['TARGET_TABLE'])
source_table_nm = table['SOURCE_SCHEMA']+'.'+table['SOURCE_TABLE']
target_table_nm = table['TARGET_SCHEMA'] + '.' + table['TARGET_TABLE']
sample_source_condition = conf.Read_where_condition(table['SOURCE_TABLE'])
sample_target_condition = conf.Read_where_condition(table['TARGET_TABLE'])
source_sql = "select * from {} where {}".format(source_table_nm,sample_source_condition)
target_sql = "select * from {} where {}".format(target_table_nm,sample_target_condition)
rs_source = db_connect.exec_sql_common(source_db_node, id, pwd, source_sql)
rs_target = db_connect.exec_sql_common(target_db_node, id, pwd, target_sql)
source_row_count = len(rs_source)
target_row_count = len(rs_target)
source_column_count = len(rs_source[0])
target_column_count = len(rs_target[0])
print("source table: %s" % source_table_nm)
print("target table: %s" % target_table_nm)
'''step 1 compare the row number between source and target'''
if source_row_count == target_row_count:
print("The sample sql returns the same row count")
else:
print("The sample sql returns the different row count,the test validate failed")
raise TestException.SampleDataError
'''step 2 compare the column number between source and target'''
if source_column_count == target_column_count + 3:
print("The sample sql return the same column count")
else:
print("The sample sql returns the different row count,the test validate failed")
raise TestException.SampleDataError
'''step 3 loop to compare the result from source and target'''
for i in range(source_row_count):
for k, v in source_target_column_mapping.items():
sample_compare_dict = dict()
source_value = rs_source[i][k]
target_value = rs_target[i][v]
sample_compare_dict['SOURCE_TABLE_NM'] = source_table_nm
sample_compare_dict['SOURCE_COLUMN_NM'] = k
sample_compare_dict['SOURCE_COLUMN_VALUE'] = source_value
sample_compare_dict['TARGET_TABLE_NM'] = target_table_nm
sample_compare_dict['TARGET_COLUMN_NM'] = v
sample_compare_dict['TARGET_COLUMN_VALUE'] = target_value
sample_compare_dict['STATUS'] = ('PASS' if(source_value == target_value) else 'FAIL')
sample_data_report.append(sample_compare_dict)
if sample_compare_dict['STATUS'] == 'FAIL':
failed_sample_report.append[sample_compare_dict]
'''write failed record to failed file '''
with open("tmp/failed_sample_data_report.json",'w',encoding ='UTF-8') as f:
json.dump(failed_sample_report, f)
'''write all the record to report file'''
with open("tmp/sample_data_report.json",'w',encoding ='UTF-8') as f:
json.dump(sample_data_report, f)
if len(failed_sample_report) > 0:
print("There are some cell values not equal, the test failed,check the failed report file for detail")
raise TestException.SampleDataError()
else:
print("The sample data test run passed")
return 'PASS'
