Untitled
unknown
plain_text
2 years ago
15 kB
3
Indexable
import pandas as pd import mysql.connector import json from base64 import b64decode as base_b64decode from base64 import b64encode as base_b64encode releaseId = '275.2' releaseType = 'DEPLOYED' replicationTarget = 'TESTING' catalogId = '' opId = 'HOB' buId = 'DEFAULT' replicationJobId = '' json_file_path = "/app/scripts/PPM_Release_Management/Product_Catalog_ETL/config/ppm_reply.json" sql_log_file = "/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/ppm_reply.sql" query_count=0 def write_sql(query_info): with open(sql_log_file,"a") as log_file: log_file.write(query_info) log_file.write('\n') try: with open(json_file_path) as json_file: json_data = json.load(json_file) # For PPM_PC encrypt = json_data.get('PPM_PC', {}).get('ENCRYPT') host = json_data.get('PPM_PC', {}).get('DB_HOST') port = json_data.get('PPM_PC', {}).get('DB_PORT') user = json_data.get('PPM_PC', {}).get('DB_USER') schema = json_data.get(replicationTarget, {}).get('DB_SCHEMA') if encrypt == 'Y': password = base_b64decode(json_data.get('PPM_PC', {}).get('DB_PASSWORD')).decode('utf-8') else: password = json_data.get('PPM_PC', {}).get('DB_PASSWORD') cnx = mysql.connector.connect(user=user, password=password, host=host, port=port) cursor = cnx.cursor() primary_query = f"SELECT * FROM {schema}.etl_ppm_replication_master" cursor.execute(primary_query) query_info = f"-- ++++++++++++++++++++|PRIMARY_QUERY\n" query_info += f"-- #Query:\n{primary_query};\n" write_sql(query_info) rows = cursor.fetchall() columns = [desc[0] for desc in cursor.description] df = pd.DataFrame(rows, columns=columns) # replicationTarget encrypt_tar = json_data.get(replicationTarget, {}).get('ENCRYPT') host_tar = json_data.get(replicationTarget, {}).get('DB_HOST') port_tar = json_data.get(replicationTarget, {}).get('DB_PORT') user_tar = json_data.get(replicationTarget, {}).get('DB_USER') if encrypt_tar == 'Y': password = base_b64decode(json_data.get(replicationTarget, {}).get('DB_PASSWORD')).decode('utf-8') else: password = json_data.get(replicationTarget, {}).get('DB_PASSWORD') cnx_tar = mysql.connector.connect(user=user_tar, password=password, host=host_tar, port=port_tar) cursor_tar = cnx_tar.cursor() replaced_string="" if releaseType == 'DEPLOYED': order = ['MASTER-CHILD', 'AUDIT-CHILD', 'MASTER', 'AUDIT', 'RELEASE'] filtered_df = df.loc[ df['eprm_catalog'].isin(['PC']) & (df['eprm_enabled_flg'] == 'Y') & df['eprm_table_type'].isin( order)].copy() filtered_df['eprm_table_type'] = pd.Categorical(filtered_df['eprm_table_type'], categories=order, ordered=True) filtered_df = filtered_df.sort_values('eprm_table_type') for _, row in filtered_df.iterrows(): eprm_table_name = row['eprm_table_name'] eprm_join_cols_entity = row['eprm_join_cols_entity'] eprm_join_cols_reim = row['eprm_join_cols_reim'] eprm_table_alias = row['eprm_table_alias'] eprm_table_type = row['eprm_table_type'] eprm_parent_table_name=row['eprm_parent_table_name'] if eprm_table_type == 'AUDIT': eprm_table_col_pk = row['eprm_table_col_pk'] query = f"SELECT COUNT(*) FROM {schema}.{eprm_table_name} WHERE (" + eprm_table_col_pk + f") IN (SELECT entity_ref_nbr FROM {schema}.release_entity_inst_map WHERE release_id='" + releaseId + "' AND op_id='" + opId + "' AND bu_id='" + buId + "')" try: cursor_tar.execute(query) result = cursor_tar.fetchone() query_info = f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n" query_info += f"-- #Query: Result:{result[0]}\n" query_count +=1 query_info += query + ";\n" write_sql(query_info) print(f"Count for {eprm_table_name}: {result[0]} (audit)") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {err}") elif eprm_table_type == 'MASTER': clause_removed_reim_join = eprm_join_cols_reim.replace(" AND", "").replace("=", "") remove_string = [eprm_table_alias + "." + v for v in eprm_join_cols_entity.split(",")] if eprm_table_alias + ".version" not in remove_string and eprm_table_alias + ".version" in eprm_join_cols_reim: remove_string = remove_string + [eprm_table_alias + ".version"] eprm_join_cols_entity = eprm_join_cols_entity + ", version" reim_select_cols = clause_removed_reim_join for v in remove_string: reim_select_cols = reim_select_cols.replace(v, "").replace("reim.", "") reim_select_cols = reim_select_cols.replace(" ", ",") if eprm_table_alias + ".version" in remove_string and "reim.version" not in eprm_join_cols_reim: reim_select_cols = reim_select_cols + ",version" split_parts = reim_select_cols.split(',') replaced_parts = [] for part in split_parts: dot_index = part.find('.') if dot_index != -1: replaced_part = part[dot_index + 1:] replaced_parts.append(replaced_part) else: replaced_parts.append(part) replaced_string = ','.join(replaced_parts) secondary_query = f"SELECT COUNT(*) FROM {schema}.{eprm_table_name} WHERE (" + eprm_join_cols_entity + ") IN (SELECT " + replaced_string + f" FROM {schema}.release_entity_inst_map WHERE release_id='" + releaseId + "' AND op_id='" + opId + "' AND bu_id='" + buId + "')" try: cursor_tar.execute(secondary_query) result = cursor_tar.fetchone() query_info = f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n" query_info += f"-- #Query: Result:{result[0]}\n" query_info += secondary_query + ";\n" query_count +=1 write_sql(query_info) print(f"Count for {eprm_table_name}: {result[0]} (master)") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {err}") elif eprm_table_type == 'MASTER-CHILD': qry = f"select * from {schema}.etl_ppm_replication_master WHERE eprm_table_name="+"'"+eprm_parent_table_name+"'" cursor_tar.execute(qry) rows = cursor_tar.fetchall() columns = [desc[0] for desc in cursor_tar.description] df_child = pd.DataFrame(rows, columns=columns) filtered_df_child = df_child[ df_child['eprm_catalog'].isin(['PC']) & (df_child['eprm_enabled_flg'] == 'Y') & df_child['eprm_table_type'].isin( ['MASTER'])] for _, row in filtered_df_child.iterrows(): eprm_table_name_child = row['eprm_table_name'] eprm_join_cols_entity_child = row['eprm_join_cols_entity'] eprm_join_cols_reim_child = row['eprm_join_cols_reim'] eprm_table_type_child=row['eprm_table_type'] if eprm_table_type_child=='MASTER': print(eprm_join_cols_reim) split_conditions_reim = eprm_join_cols_reim.split('AND') where_clause_reim = [] for condition in split_conditions_reim: condition = condition.split('=')[1] condition = condition.split('.')[1] where_clause_reim.append(condition.strip()) where_clause_reim = ','.join(where_clause_reim) print(where_clause_reim) values_child = eprm_join_cols_entity_child.split('=') column_names_child = [value.split('.')[-1].strip() for value in values_child] where_clause_child = ', '.join(column_names_child) split_conditions = eprm_join_cols_reim_child.split(' AND ') result = [condition.split('=')[0].split('.')[-1] for condition in split_conditions] where_clause = ', '.join(result) final_query = f"SELECT COUNT(*) FROM {schema}.{eprm_table_name} WHERE ({where_clause_reim}) IN (select {where_clause_reim} from {schema}.{eprm_table_name_child} where ({where_clause_child}) IN (select "+where_clause+f" FROM {schema}.RELEASE_ENTITY_INST_MAP WHERE release_id='"+releaseId+"' AND op_id='"+opId+"' AND bu_id='"+buId+"'))" print(final_query) try: cursor_tar.execute(final_query) result = cursor_tar.fetchone() query_info = f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n" query_info += f"-- #Query: Result:{result[0]}\n" query_count +=1 query_info += final_query + ";\n" write_sql(query_info) print(f"Count for {eprm_table_name}: {result[0]}" + " ---------------------MASTER-AUDIT------------------") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {err}") elif eprm_table_type == 'AUDIT-CHILD': qry = f"select * from {schema}.etl_ppm_replication_master WHERE eprm_table_name=" + "'" + eprm_parent_table_name + "'" cursor_tar.execute(qry) rows = cursor_tar.fetchall() columns = [desc[0] for desc in cursor_tar.description] df_child = pd.DataFrame(rows, columns=columns) filtered_df_child = df_child[ df_child['eprm_catalog'].isin(['PC']) & (df_child['eprm_enabled_flg'] == 'Y') & df_child[ 'eprm_table_type'].isin( ['AUDIT'])] for _, row in filtered_df_child.iterrows(): eprm_table_name_child = row['eprm_table_name'] eprm_join_cols_entity_child = row['eprm_join_cols_entity'] eprm_join_cols_reim_child = row['eprm_join_cols_reim'] eprm_table_type_child = row['eprm_table_type'] if eprm_table_type_child == 'AUDIT': print(eprm_join_cols_reim) split_conditions_reim = eprm_join_cols_reim.split('AND') where_clause_reim = [] for condition in split_conditions_reim: condition = condition.split('=')[1] condition = condition.split('.')[1] where_clause_reim.append(condition.strip()) where_clause_reim = ','.join(where_clause_reim) print(where_clause_reim) values_child = eprm_join_cols_entity_child.split('=') column_names_child = [value.split('.')[-1].strip() for value in values_child] where_clause_child = ', '.join(column_names_child) split_conditions = eprm_join_cols_reim_child.split(' AND ') result = [condition.split('=')[0].split('.')[-1] for condition in split_conditions] where_clause = ', '.join(result) final_query = f"SELECT COUNT(*) FROM {schema}.{eprm_table_name} WHERE ({where_clause_reim}) IN (select {where_clause_reim} from {schema}.{eprm_table_name_child} where ({where_clause_child}) IN (select " + where_clause + f" FROM {schema}.RELEASE_ENTITY_INST_MAP WHERE release_id='"+releaseId+"' AND op_id='"+opId+"' AND bu_id='"+buId+"'))" print(final_query) try: cursor_tar.execute(final_query) result = cursor_tar.fetchone() query_info = f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n" query_info += f"-- #Query: Result:{result[0]}\n" query_count +=1 query_info += final_query + ";\n" write_sql(query_info) print( f"Count for {eprm_table_name}: {result[0]}" + " ---------------------AUDIT-CHILD------------------") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {err}") elif releaseType=='TESTING' or releaseType=='INPROGRESS': filtered_df = df[df['eprm_catalog'].isin(['PC']) & (df['eprm_enabled_flg'] == 'Y') & df['eprm_table_type'].isin(['AUDIT','RELEASE'])] for _, row in filtered_df.iterrows(): eprm_table_name = row['eprm_table_name'] eprm_table_col_pk=row['eprm_table_col_pk'] eprm_table_type = row['eprm_table_type'] if eprm_table_type == 'AUDIT': query = f"SELECT COUNT(*) FROM {schema}.{eprm_table_name} WHERE ("+eprm_table_col_pk+f") IN (SELECT entity_ref_nbr FROM {schema}.release_entity_inst_map WHERE release_id='"+releaseId+"' AND op_id='"+opId+"' AND bu_id='"+buId+"')" try: cursor_tar.execute(query) result = cursor_tar.fetchone() query_info = f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n" query_info += f"-- #Query: Result:{result[0]}\n" query_info += query + ";\n" query_count +=1 write_sql(query_info) print(f"Count for {eprm_table_name}: {result[0]}"+" inprogress-audit") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {err}") else: print("Release Type is not defined") except mysql.connector.Error as err: print(f"An error occurred: {err}") finally: if cursor_tar: cursor_tar.close() if cnx_tar: cnx_tar.close() if cursor: cursor.close() if cnx: cnx.close()
Editor is loading...