def merge_view(dev, qa, uat, sheet, prod='none'):
dev = dev.fillna('null')
qa = qa.fillna('null')
uat = uat.fillna('null')
gap = pd.merge(dev, qa, on=['Type', 'name'], how='outer')
gap = pd.merge(gap, uat, on=['Type', 'name'], how='outer')
if isinstance(prod, str):
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
col[2] = str(col[2]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
col[3] = str(col[3]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
col[4] = str(col[4]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
if col[2] == col[3] == col[4]:
gap = gap.drop(index)
else:
prod = prod.fillna('null')
gap = pd.merge(gap, prod, on=['Type', 'name'], how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
col2 = str(col[2]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
col3 = str(col[3]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
col4 = str(col[4]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
col5 = str(col[5]).replace('[', '').replace(']', '').replace(
'\r', '').replace('\t', '').replace('\n', '').replace(' ', '')
if col2 == col3 == col4 == col5:
gap = gap.drop(index)
gap = gap.fillna('null')
gap = gap.reset_index(drop=True)
for index, col in gap.iterrows():
for i in range(0, len(col), 1):
sheet.cell(row=index + 2, column=i + 1).value = col[i]
if i == 2:
sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="C28EEA")
elif i == 3:
sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="DAC45E")
elif i == 4:
sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="68AE59")
elif i == 5:
sheet.cell(row=index + 2, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="5B7190")
def search_empty_tables(table_list, not_validate_list) -> list:
with UseSqlserverDB(TARGET_DB) as cursor:
generate_empty_validation_sql = "SELECT NAME, 'SELECT TOP 2 * FROM ' + NAME + ' WITH(NOLOCK) ORDER BY 1 ASC;' AS SQL_TEXT FROM sysobjects WHERE xtype = 'U' AND uid = 1 AND ((name LIKE 'D[_]%' OR name LIKE 'B[_]%' OR name LIKE 'R[_]%' OR name LIKE 'F[_]%' OR name LIKE 'RPT[_]%') and name <> 'D_AUDIT_LOG') ORDER BY name"
rs_table_list = query(cursor, generate_empty_validation_sql)
not_empty_list = []
empty_table_counter = 0
for item in rs_table_list:
table_name = item[0]
sql_text = item[1]
if not_validate_list:
if table_name in not_validate_list:
continue
if table_list:
if table_name not in table_list:
continue
else:
if identify_backup_tables(table_name.lower()):
not_validate_list.append(table_name)
continue
else:
if identify_backup_tables(table_name.lower()):
not_validate_list.append(table_name)
continue
rs_table_data = query(cursor, sql_text)
if rs_table_data:
if len(rs_table_data) == 1 and (
str(table_name).startswith("D_")
or str(table_name).startswith("R_")
) and rs_table_data[0][0] == -1:
msg = "\033[32m" + table_name + "\033[0m only has -1 key row, please check."
add_msg('1 empty_table', table_name, '0', msg)
empty_table_counter += 1
elif (str(table_name).startswith("B_")
or str(table_name).startswith("F_")
) and rs_table_data[0][0] == -1:
msg = "\033[32m" + table_name + "\033[0m HAS -1 key row, which should NOT. please check."
add_msg('1 empty_table', table_name, '0', msg)
else:
not_empty_list.append(table_name)
else:
empty_table_counter += 1
msg = "\033[32m" + table_name + " \033[0mis empty, please check."
add_msg('1 empty_table', table_name, '0', msg)
search_empty_result = [
not_empty_list, empty_table_counter, not_validate_list
]
return search_empty_result
def merge_ddl(dev, qa, uat, sheet, prod='none'):
dev = dev.fillna('null')
qa = qa.fillna('null')
uat = uat.fillna('null')
gap = pd.merge(dev, qa, on=['table_name', 'column_name'], how='outer')
gap = pd.merge(gap, uat, on=['table_name', 'column_name'], how='outer')
if isinstance(prod, str):
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
if col[3] == col[8] == col[13] and col[4] == col[9] == col[14] \
and col[5] == col[10] == col[15] and col[6] == col[11] == col[16]:
'''Don't compare postion col[2] == col[7] == col[12] and '''
gap = gap.drop(index)
else:
prod = prod.fillna('null')
gap = pd.merge(gap,
prod,
on=['table_name', 'column_name'],
how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
if col[3] == col[8] == col[13] == col[18] and col[4] == col[9] == col[14] == col[19] \
and col[5] == col[10] == col[15] == col[20] and col[6] == col[11] == col[16] == col[21]:
'''Don't compare postion col[2] == col[7] == col[12] == col[17] and'''
gap = gap.drop(index)
gap = gap.sort_values(by=['table_name', 'column_name'])
gap = gap.reset_index(drop=True)
for index, col in gap.iterrows():
for i in range(0, len(col), 1):
sheet.cell(row=index + 3, column=i + 1).value = col[i]
if i in (2, 3, 4, 5, 6):
sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="C28EEA")
elif i in (7, 8, 9, 10, 11):
sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="DAC45E")
elif i in (12, 13, 14, 15, 16):
sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="68AE59")
elif i in (17, 18, 19, 20, 21):
sheet.cell(row=index + 3, column=i + 1).fill = sty.PatternFill(
fill_type='solid', fgColor="5B7190")
def merge_sp(db1, db2, sheet):
db1 = db1.fillna('null')
db2 = db2.fillna('null')
gap = pd.merge(db1, db2, on = ['Type','name'], how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(str(col[0]).lower()):
gap = gap.drop(index)
sp_a = str(col[2]).replace('[','').replace(']','').replace('\r','').replace('\t','').replace('\n','').replace(' ','').upper()
sp_b = str(col[3]).replace('[','').replace(']','').replace('\r','').replace('\t','').replace('\n','').replace(' ','').upper()
if sp_a == sp_b:
gap = gap.drop(index)
gap = gap.fillna('null')
gap = gap.reset_index(drop=True)
for index, col in gap.iterrows():
for i in range(0, len(col), 1):
sheet.cell(row=index+2,column=i+1).value = col[i]
if i == 2:
sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA")
elif i == 3:
sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E")
elif i == 4:
sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="68AE59")
def merge_ddl(db1, db2, sheet):
db1 = db1.fillna('null')
db2 = db2.fillna('null')
gap = pd.merge(db1, db2, on = ['table_name','column_name'], how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
continue
#print(col[0]+":"+col[1])
if col[3] == col[8] and col[4] == col[9] \
and col[5] == col[10] and col[6] == col[11] :
'''Don't compare postion col[2] == col[7] '''
gap = gap.drop(index)
gap = gap.sort_values(by = ['table_name','column_name'])
gap = gap.reset_index(drop=True)
for index, col in gap.iterrows():
for i in range(0, len(col), 1):
sheet.cell(row=index+3,column=i+1).value = col[i]
if i in (2,3,4,5,6):
sheet.cell(row=index+3,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA")
elif i in (7,8,9,10,11):
sheet.cell(row=index+3,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E")
def search_empty_tables(cursor, table_list) -> list:
generate_empty_validation_sql = "SELECT NAME, 'SELECT TOP 2 * FROM ' + NAME + ' WITH(NOLOCK) ORDER BY 1 ASC;' AS SQL_TEXT FROM sysobjects WHERE xtype = 'U' AND uid = 1 AND ((name LIKE 'D[_]%' OR name LIKE 'B[_]%' OR name LIKE 'R[_]%' OR name LIKE 'F[_]%' OR name LIKE 'RPT[_]%') and name <> 'D_AUDIT_LOG') ORDER BY name"
rs_table_list = query(cursor, generate_empty_validation_sql)
not_empty_list = []
empty_table_counter = 0
not_validate_list = []
for item in rs_table_list:
table_name = item[0]
sql_text = item[1]
if identify_backup_tables(table_name.lower()):
not_validate_list.append(table_name)
continue
if table_list:
if table_name not in table_list:
continue
rs_table_data = query(cursor, sql_text)
if rs_table_data:
if len(rs_table_data) == 1 and (str(table_name).startswith("D_")
or str(table_name).startswith("R_")
) and rs_table_data[0][0] == -1:
print("\033[32m" + table_name +
"\033[0m only has -1 key row, please check.")
else:
not_empty_list.append(table_name)
else:
empty_table_counter += 1
print("\033[32m" + table_name + " \033[0mis empty, please check.")
search_empty_result = [
not_empty_list, empty_table_counter, not_validate_list
]
return search_empty_result
def merge_index(db1, db2, sheet):
db1 = db1.fillna('null')
db2 = db2.fillna('null')
gap = pd.merge(db1, db2, on = ['table_nm','index_nm'], how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
gap = gap.drop(index)
continue
if col[2] == col[3]:
gap = gap.drop(index)
gap = gap.fillna('null')
gap = gap.reset_index(drop=True)
for index, col in gap.iterrows():
for i in range(0, len(col), 1):
sheet.cell(row=index+2,column=i+1).value = col[i]
if i == 2:
sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="C28EEA")
elif i == 3:
sheet.cell(row=index+2,column=i+1).fill=sty.PatternFill(fill_type='solid',fgColor="DAC45E")