def test_source_db_connection(self): connection = utils.get_oracle_connection_obj() config = utils.get_config_parser() enc = AESCipher( '[EX\xc8\xd5\xbfI{\xa2$\x05(\xd5\x18\xbf\xc0\x85)\x10nc\x94\x02)j\xdf\xcb\xc4\x94\x9d(\x9e' ) query = f"SELECT CONNECTION_STRING,USERNAME,PASSWORD_FILE FROM {config.get('Database', 'connection_tablename')}" source_db_df = pd.read_sql(query, con=connection) for index, row in source_db_df.iterrows(): password = enc.decrypt_file(row['PASSWORD_FILE']) connection = utils.get_oracle_connection_obj( row['USERNAME'], password, row['CONNECTION_STRING']) assert type( connection ) == cx_Oracle.Connection, "Username or password or connection string is incorrect"
def on_created(self, event): logging.info( f'event type: {event.event_type} path : {event.src_path}') try: copyfile( event.src_path, config.get('Directory', 'temp') + os.path.basename(event.src_path)) connection = utils.get_oracle_connection_obj() update_file_details( True, connection, config.get('Database', 'tablespace'), config.get('Database', 'file_details_tablename'), os.path.basename(event.src_path)) except Exception as ex: error.Errors(ex).errorrun() err, = ex.args copyfile( event.src_path, config.get('Directory', 'error') + os.path.basename(event.src_path)) update_file_details( False, connection, config.get('Database', 'tablespace'), config.get('Database', 'file_details_tablename'), os.path.basename(event.src_path), f"{err.code}:{err.message}")
def test_password_file(self): connection = utils.get_oracle_connection_obj() config = utils.get_config_parser() query = f"SELECT PASSWORD_FILE FROM {config.get('Database', 'connection_tablename')}" pwrd_df = pd.read_sql(query, con=connection) for index, row in pwrd_df.iterrows(): exists = os.path.isfile(row['PASSWORD_FILE']) assert exists, f"{row['PASSWORD_FILE']} file does not exists "
def test_source_tables(self): connection = utils.get_oracle_connection_obj() config = utils.get_config_parser() enc = AESCipher( '[EX\xc8\xd5\xbfI{\xa2$\x05(\xd5\x18\xbf\xc0\x85)\x10nc\x94\x02)j\xdf\xcb\xc4\x94\x9d(\x9e' ) query = f"SELECT CONNECTION_STRING,USERNAME,PASSWORD_FILE,DATABASE_NAME,TABLE_NAME FROM " \ f"{config.get('Database', 'extraction_tablename')} E JOIN " \ f"{config.get('Database', 'connection_tablename')} C ON E.CONNECTION_ID=C.CONNECTION_ID" join_df = pd.read_sql(query, con=connection) for index, row in join_df.iterrows(): password = enc.decrypt_file(row['PASSWORD_FILE']) connection = utils.get_oracle_connection_obj( row['USERNAME'], password, row['CONNECTION_STRING']) dbcur = connection.cursor() try: dbcur.execute( f"SELECT * FROM {row['DATABASE_NAME']}.{row['TABLE_NAME']}" ) except Exception as ex: err, = ex.args pytest.fail(err.message)
def test_oracle_table(self): connection = utils.get_oracle_connection_obj() config = utils.get_config_parser() tbl_list = (config.get('Database', 'extraction_tablename'), config.get('Database', 'connection_tablename')) dbcur = connection.cursor() try: for tbl in tbl_list: dbcur.execute("SELECT * FROM {}".format(tbl)) except Exception as ex: err, = ex.args pytest.fail(err.message)
def extract_source_data(extraction_id, enc): """ Extacts source and source database connection details from extraction_details and connection_detalis tables respectively and extracts source data and store it in provided delimited file. :param extraction_id: source extraction_id :param enc : AESCipher object """ try: logging.getLogger().setLevel(logging.INFO) connection = utils.get_oracle_connection_obj() config = utils.get_config_parser() extraction_query = get_select_query( config.get('Database', 'extraction_columnlist'), config.get('Database', 'tablespace'), config.get('Database', 'extraction_tablename'), "EXTRACTION_ID=" + extraction_id.__str__()) logging.info("Extraction_details query = " + extraction_query) extraction_details_df = pd.read_sql(extraction_query, con=connection) # Get connection details for given connection_id corresponding to extraction id connection_id = extraction_details_df.iloc[0]['CONNECTION_ID'] connection_query = get_select_query( config.get('Database', 'connection_columnlist'), config.get('Database', 'tablespace'), config.get('Database', 'connection_tablename'), "CONNECTION_ID=" + connection_id.__str__()) logging.info("Connection_details query = " + connection_query) connection_details_df = pd.read_sql(connection_query, con=connection) # create connection object for extraction table db password_file = connection_details_df.iloc[0]['PASSWORD_FILE'] password = enc.decrypt_file(password_file) connection_src_extraction = utils.get_oracle_connection_obj( connection_details_df.iloc[0]['USERNAME'], password, connection_details_df.iloc[0]['CONNECTION_STRING']) # Get current incremental column's max value from source data max_value = get_max(connection_src_extraction, extraction_details_df) source_extraction_query = get_select_query( extraction_details_df.iloc[0]['COLUMN_LIST'], extraction_details_df.iloc[0]['DATABASE_NAME'], extraction_details_df.iloc[0]['TABLE_NAME'], extraction_details_df.iloc[0]['INCREMENTAL_COLUMN'] + " > " + extraction_details_df.iloc[0]['INCREMENTAL_VALUE'].__str__() + " AND " + extraction_details_df.iloc[0]['INCREMENTAL_COLUMN'] + " <= " + max_value.__str__()) logging.info("Source extraction query = " + source_extraction_query) source_extraction_df = pd.read_sql(source_extraction_query, con=connection_src_extraction) logging.info("copying " + extraction_details_df.iloc[0]['FILENAME'] + " file to " + config.get('Directory', 'inbound') + " path") source_extraction_df.to_csv( config.get('Directory', 'inbound') + extraction_details_df.iloc[0]['FILENAME'], index=False, sep=extraction_details_df.iloc[0]['FILE_DELIMITER']) logging.info("Updating File_details oracle table") today = date.today().strftime("%Y-%m-%d") insert_file_details( connection, config.get('Database', 'tablespace'), config.get('Database', 'file_details_tablename'), f"0,'{connection_details_df.iloc[0]['SYSTEM']}', " f"'{extraction_details_df.iloc[0]['DATABASE_NAME']}', " f"'{extraction_details_df.iloc[0]['TABLE_NAME']}', " f"'{extraction_details_df.iloc[0]['FILENAME']}', " f"TO_DATE('{datetime.now().strftime('%Y%m%d%H%M%S')}','yyyymmddhh24miss')," f"{source_extraction_df.shape[0]}, 'False', 'False', 'None', date'{today}'," f" '{getpass.getuser()}'," f" date'{today}', '{getpass.getuser()}'") logging.info("Successfully extracted data for " + extraction_details_df.iloc[0]['TABLE_NAME'] + " table.") logging.info( "Updating incremental column's value in extraction_details") # #####Uncomment below code to update incremental_value column in excraction_details table ####### # update_extraction_details(connection, config.get('Database', 'tablespace'), # config.get('Database', 'extraction_tablename'), # extraction_id, max_value) logging.info( "####################### JOB COMPLETED SUCCESSFULLY ######################" ) except Exception as ex: # err, = ex.args error.Errors(ex).errorrun() # logging.error("Error code = ", err.code) # logging.error("Error Message = ", err.message) connection.close() connection_src_extraction.close() os._exit(1) connection.close() connection_src_extraction.close() return 0
def test_oracle_connection(self): connection = utils.get_oracle_connection_obj() assert type(connection) == cx_Oracle.Connection, "Username or password or connection string is incorrect for " \ "DLEXD database mentioned in application.properties file"