Untitled
unknown
plain_text
a year ago
5.6 kB
1
Indexable
Never
import pandas as pd import mysql.connector import json from base64 import b64decode as base_b64decode import logging import datetime import os from sqlalchemy import create_engine as sqlalchemy_create_engine from pandas import read_sql as pd_read_sql import sys releaseId = '275.2' releaseType = 'TESTING' replicationTarget = 'SOURCE' catalogId = '' opId = 'HOB' buId = 'DEFAULT' replicationJobId = 'REP_990_234' json_file_path = "/app/scripts/PPM_Release_Management/Product_Catalog_ETL/config/ppm_reply.json" sql_log_file = f"/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/{replicationJobId}ppm_reply.sql" log_file = f'/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/{replicationJobId}ppm_reply.log' # sql_log_file = os.get_path("PPM_PC_LOG") + "/" + replicationJobId + "_ppm_pc_replication_delete.sql" # json_file_path = os.get_path("PPM_PC_CONFIG") + "/ppm_pc_replication.json" # log_file = os.get_path("PPM_PC_LOG") + "/" + replicationJobId + "_ppm_pc_replication_delete.log" release_type = releaseType.casefold() STATUS = replicationTarget STATUS_MESSAGE = "success" # Set up logging logging.basicConfig( filename=log_file, level=logging.INFO, format='%(asctime)s - %(message)s', datefmt='%Y-%m-%d %H:%M:%S' ) query_count = 0 # Open SQL log file for writing sql_log_file = open(sql_log_file, "w") # Function to write SQL query to the log file def write_sql(query_info): sql_log_file.write(query_info) sql_log_file.write('\n') try: # Function to establish a database connection def connect_to_database(json_data, replicationTarget): try: encrypt = json_data.get(replicationTarget, {}).get('ENCRYPT') host = json_data.get(replicationTarget, {}).get('DB_HOST') port = json_data.get(replicationTarget, {}).get('DB_PORT') user = json_data.get(replicationTarget, {}).get('DB_USER') db_type = json_data.get(replicationTarget, {}).get('DB_TYPE') schema = json_data.get(replicationTarget, {}).get('DB_SCHEMA') if encrypt == 'Y': password = b64decode(json_data.get(replicationTarget, {}).get('DB_PASSWORD')).decode('utf-8') else: password = json_data.get(replicationTarget, {}).get('DB_PASSWORD') if db_type == 'MYSQL': cnx = mysql.connector.connect(user=user, password=password, host=host, port=port) cursor = cnx.cursor() logging.info(f"Connected to MySQL database server {replicationTarget}: {host}:{port}") elif db_type == 'ORACLE': import oracledb oracle_mode = oracledb.is_thin_mode() print("Oracle mode: %s" % oracle_mode) if oracle_mode: oracledb.init_oracle_client() print("Enabled python-oracledb Thick mode") else: print("Default python-oracledb Thick mode") cnx_text = ('oracle://%s:%s@%s:%s/?service_name=%s' % (user, password, host, port, schema)) cnx = create_engine(cnx_text, encoding="utf8").raw_connection() cursor = cnx.cursor() return cnx, cursor, schema except Exception as dbexp: print("Error - {} . Line No - {} ".format(str(dbexp), str(sys.exc_info()[-1].tb_lineno))) try: # Read JSON data from file with open(json_file_path) as json_file: json_data = json.load(json_file) except FileNotFoundError: print("File not found: " + json_file_path) try: # Connect to PPM_PC database conn_ppm, cursor_ppm, schema_ppm = connect_to_database(json_data, 'PPM_PC') # Fetch data from the etl_ppm_replication_master table primary_query = f"SELECT * FROM {schema_ppm}.etl_ppm_replication_master" df = pd_read_sql(primary_query, con=conn_ppm) columns = df.columns.tolist() rows = df.values.tolist() if len(rows) == 0: STATUS = "Error" STATUS_MESSAGE = "No records present in etl_ppm_replication_master table" # replicationTarget connection_tar, cursor_tar, schema_tar = connect_to_database(json_data, replicationTarget) # cursor_tar = connection_tar.cursor() except Exception as e: print("An Error occured:", str(e)) replaced_string = "" try: # Deleting records if releaseType is deployed or cancelled for MASTER-CHILD,AUDIT-CHILD,MASTER,AUDIT,RELEASE if release_type == 'deployed' or release_type == 'cancelled': logging.info(f"processing - {releaseType}") order = ['MASTER-CHILD', 'AUDIT-CHILD', 'MASTER', 'AUDIT', 'RELEASE'] logging.info("order of execution - 'MASTER-CHILD', 'AUDIT-CHILD', 'MASTER', 'AUDIT', 'RELEASE'") filtered_df = df.loc[ df['eprm_catalog'].isin(['PC', 'RELEASE']) & (df['eprm_enabled_flg'].isin(['Y'])) & df[ 'eprm_table_type'].isin( order)].copy() # filtered_df.sort_values(by='eprm_seq_nbr', ascending=True, inplace=True) filtered_df['eprm_table_type'] = pd.Categorical(filtered_df['eprm_table_type'], categories=order, ordered=True) filtered_df.sort_values(by=['eprm_table_type', 'eprm_seq_nbr'], ascending=[True, True], inplace=True)