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"