Untitled
plain_text
a month ago
11 kB
1
Indexable
Never
# ================================================================================================================================================================================ # PPM PRODUCT CATALOG (PC) REPLICATION - CE CUSTOM TABLES REPLICATE # DATE AUTHOR VER CHANGE DESCRIPTION # -------- --------- ----- ------------------ # 21.08.23 Veera 1.0 The below script replicates ppm table records dynamically from "etl_ppm_replication_master" "PC_EXT" # # # ================================================================================================================================================================================ import json import logging import mysql.connector import pandas as pd import os import sys from base64 import b64decode as base_b64decode from pandas import read_sql as pd_read_sql from sqlalchemy import create_engine from io import TextIOBase as io_TextIOBase from json import load as json_load # define Python user-defined exceptions class Error(Exception): """Base class for other exceptions""" pass # define Python user-defined exceptions class ETL_PPM_REPLICATION_MASTER_ERROR(Error): pass class DB_CONNECTION_ERROR(Error): pass # Function to write SQL query to the log file def write_sql(sql_log_file, query_info): sql_log_file.write(query_info) sql_log_file.write('\n') source = 'SOURCE' # Function to establish a database connection def setDbConnection(logging, json_data, serverInfo): from sqlalchemy import create_engine from base64 import b64decode as base_b64decode try: cnx = cursor = schema = db_type = None encrypt = json_data.get(serverInfo, {}).get('ENCRYPT') host = json_data.get(serverInfo, {}).get('DB_HOST') port = json_data.get(serverInfo, {}).get('DB_PORT') user = json_data.get(serverInfo, {}).get('DB_USER') db_type = json_data.get(serverInfo, {}).get('DB_TYPE') schema = json_data.get(serverInfo, {}).get('DB_SCHEMA') if encrypt == 'Y': password = base_b64decode(json_data.get(serverInfo, {}).get('DB_PASSWORD')).decode('utf-8') else: password = json_data.get(serverInfo, {}).get('DB_PASSWORD') if db_type in ('MYSQL', 'MARIA'): import mysql.connector cnx = mysql.connector.connect(user=user, password=password, host=host, port=port, database=schema) cursor = cnx.cursor() elif db_type == 'ORACLE': import oracledb oracle_mode = oracledb.is_thin_mode() if oracle_mode: oracledb.init_oracle_client() print("Enabled 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 logging.info(f"Connected to database server {serverInfo}: {host}:{port}/{schema}") except mysql.connector.Error as dberr: logging.error("DATABASE CONNECTION ERROR") logging.error("Error - {} . Line No - {} ".format(str(dberr), str(sys.exc_info()[-1].tb_lineno))) cnx = cursor = schema = None except Exception as dbexp: logging.error("DATABASE CONNECTION EXCEPTION") logging.error("Error - {} . Line No - {} ".format(str(dberr), str(sys.exc_info()[-1].tb_lineno))) cnx = cursor = schema = None return cnx, cursor, schema, db_type def main(args, sql_log_file, logging, json_data): # Connect to PPM_PC database conn_ppm, cursor_ppm, schema_ppm, user_ppm, password_ppm, host_ppm, port_ppm = setDbConnection(logging,json_data, 'PPM_PC') # Connect to source database conn_source, cursor_source, schema_source, _, _, _, _ = setDbConnection(logging,json_data, source) # Fetch data from the etl_ppm_replication_master table primary_query = f"SELECT * FROM {schema_ppm}.etl_ppm_replication_master WHERE eprm_catalog='PC_EXT' AND eprm_enabled_flg='Y'" df = pd_read_sql(primary_query, con=conn_ppm) # Connect to target database replicationTarget_EXT = replicationTarget + '_EXT' db_type = json_data.get(replicationTarget_EXT, {}).get('DB_TYPE') if db_type == 'MYSQL': _, _, schema_target, user_target, password_target, host_target, port_target = setDbConnection(logging,json_data, replicationTarget_EXT) target_engine = create_engine( f"mysql+mysqlconnector://{user_target}:{password_target}@{host_target}:{port_target}/{schema_target}") else: _, _, schema_target, user_target, password_target, host_target, port_target = setDbConnection(json_data, replicationTarget_EXT) oracle_dsn = f"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={host_target})(PORT={port_target})))(CONNECT_DATA=(SERVICE_NAME={schema_target})(SID={sid})))" # Add SID here dsn_kwargs = { 'user': user_target, 'password': password_target, 'dsn': oracle_dsn, 'encoding': 'UTF-8', } try: import cx_Oracle target_connection = cx_Oracle.connect(**dsn_kwargs) target_cursor = target_connection.cursor() except ImportError: logging.error("cx_Oracle library not found. Make sure it's installed to establish Oracle connections.") raise except cx_Oracle.DatabaseError as ex: logging.error(f"Error while connecting to Oracle database: {ex}") raise for _, row in df.iterrows(): eprm_table_name = row['eprm_table_name'].lower() # Convert table name to lowercase if eprm_table_name != 'pkg_prd_fed_ext_attrs': source_query = f"SELECT * FROM {schema_source}.{eprm_table_name} WHERE release_id='{releaseId}' AND op_id='{opId}' AND bu_id='{buId}'" try: source_df = pd_read_sql(source_query, con=conn_source) if 'updated_by' in source_df: source_df['updated_by'] = replicationJobId if not source_df.empty: source_df.to_sql(eprm_table_name, con=target_engine, if_exists='append', index=False) write_sql( f"-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ STATUS|{replicationTarget}| TABLE| {eprm_table_name}\n") write_sql(f"-- #Query: Inserted {len(source_df)} record(s) and updated 'created_by'\n") print(f"Inserting records into {eprm_table_name}") logging.info(f"Inserted {len(source_df)} record(s) into {eprm_table_name} and updated 'created_by'") except mysql.connector.Error as err: print(f"Error occurred while executing the query: {source_query}: {err}") logging.info(f"Error occurred while executing the query: {source_query}: {err}") if __name__ == '__main__': import logging from configparser import ConfigParser as conf_ConfigParser import argparse statFile = "" try: parser = argparse.ArgumentParser(description="PPM Product Catalog Replication Script") parser.add_argument('--releaseId', required=True, help="Release ID") parser.add_argument('--releaseType', required=True, help="Release Type") parser.add_argument('--replicationTarget', required=True, help="Replication Target") parser.add_argument('--opId', required=True, help="Operation ID") parser.add_argument('--buId', required=True, help="Business Unit ID") parser.add_argument('--replicationJobId', required=True, help="Replication Job ID") args = parser.parse_args() replicationJobId = args.replicationJobId # File based variables json_file_path = "/app/scripts/PPM_Release_Management/Product_Catalog_ETL/config/ppm_pc_replication.json" conf_file_path = "/app/scripts/PPM_Release_Management/Product_Catalog_ETL/config/ppm_pc_replication.conf" sql_log_file = f"/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/{replicationJobId}_ppm_pc_replication_insert.sql" log_file = f'/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/{replicationJobId}_ppm_pc_replication_insert.log' statFile = f'/app/scripts/PPM_Release_Management/Product_Catalog_ETL/logs/{replicationJobId}_ppm_pc_replication_insert.status' # Status file for reading in main shell script print("statFile: %s" % statFile) statFile = open(statFile, "w") # Core ppm_pc_replication Configuration if not os.path.exists(conf_file_path): print("CONFIGURATION FILE: ", conf_file_path) raise FileNotFoundError("CONFIGURATION FILE MISSING") CONFIG = conf_ConfigParser() CONFIG.read(conf_file_path) print(CONFIG.sections()) # Set up logging logging.basicConfig( filename=log_file, level=CONFIG.get('CONFIG_LOGGING', 'LOG_LEVEL', raw=True), format=CONFIG.get('CONFIG_LOG_FORMAT', 'LOG_FORMAT_DISP', raw=True), datefmt=CONFIG.get('CONFIG_LOG_FORMAT', 'LOG_FORMAT_DATE', raw=True) ) logging.info('LOGGER initiated') # Read JSON data from file if not os.path.exists(json_file_path): logging.error("CREDENTIAL FILE MISSING") logging.error("CREDENTIAL FILE: %s" % json_file_path) raise FileNotFoundError("CREDENTIAL FILE MISSING") with open(json_file_path) as json_file: json_data = json_load(json_file) # Open SQL log file for writing sql_log_file = open(sql_log_file, "w") if main(args, sql_log_file, logging, json_data): print("Inserting data") statFile.write("SUCCESS") else: statFile.write("FAILED") sql_log_file.close() except FileNotFoundError as ferr: print("Error - {} . Line No - {} ".format(str(ferr), str(sys.exc_info()[-1].tb_lineno))) statFile.write("FAILED") except Exception as err: print("Error - {} . Line No - {} ".format(str(err), str(sys.exc_info()[-1].tb_lineno))) statFile.write("FAILED") if isinstance(statFile, io_TextIOBase): statFile.close() for above code iam getting below error Error - not enough values to unpack (expected 7, got 4) . Line No - 214