class Reconciliator:
""" Reconciliation executor """
def __init__(self):
# Unique table name for the parallel processing
self.conf_reader = ConfigReader('./conf/db.ini')
self.storage_table = 'storage_' + str(int(time.time()))
self.psa = PostgreSQLAdapter(
storage_table=self.storage_table,
schema_raw=self.conf_reader.get_attr('transaction_db_raw'),
schema_target=self.conf_reader.get_attr('reconciliation_db'),
schema_db_clean=self.conf_reader.get_attr('transaction_db_clean'))
self.csv = CsvAdapter(
storage_table=self.storage_table,
schema_target=self.conf_reader.get_attr('reconciliation_db'),
file_name_raw=self.conf_reader.get_attr('file_name_raw'),
file_name_hash=self.conf_reader.get_attr('file_name_hash'))
def storage_preparing(self):
""" Database preparing """
self.psa.storage_create()
def postgresql_adapter_run(self):
""" Postgre side preparing """
self.psa.adapter_run_main()
@m.timing
def csv_adapter_run(self):
""" CSV side preparing """
self.csv.run_reading()
self.csv.bulk_copy_to_db()
@m.timing
def get_report(self):
""" Return the detailed report """
self.psa.get_discrepancy_report()
def reconcillation_run(self):
""" Comparison the data from two sources """
self.psa.save_clean_data()
self.psa.drop_storage()
def start_all(self):
""" Run all steps """
self.storage_preparing()
self.csv_adapter_run()
self.postgresql_adapter_run()
self.get_report()
self.reconcillation_run()
class GenerateTestData:
""" The main class for creating a dummy data """
def __init__(self):
self.conf_reader = ConfigReader('./conf/db.ini')
self.data_file = self.conf_reader.get_attr('file_name_raw')
self.schema_raw = self.conf_reader.get_attr('transaction_db_raw')
self.raw_table_name = 'transaction_log'
self.raw_full_table_name = '.'.join(
[self.schema_raw, self.raw_table_name])
self.database = PostgreSQLCommon()
@m.wrapper(m.entering, m.exiting)
def create_db_schema(self):
""" Execute a script schema_transaction.sql """
sql_script_path = 'sql/schema_transaction.sql'
if os.path.exists(sql_script_path):
with open('sql/schema_transaction.sql', 'r') as file:
sql_command = file.read()
try:
self.database.execute(sql_command)
m.info('SQL script %s has been successfully executed!' %
sql_script_path)
except psycopg2.Error as err:
m.error('OOps! Script executing FAILED! Reason: %s' %
str(err.pgerror))
else:
m.error('Oops! No such file %s' % sql_script_path)
raise FileNotFoundError
@staticmethod
def create_folder(dir_name):
""" Create a folder for the csv-data keeping """
try:
if not os.path.exists(dir_name):
os.makedirs(dir_name)
m.info('Directory %s has been created!' % dir_name)
except Exception as err:
m.error('Oops! Folder creating FAILED! Reason: %s' % err)
@m.timing
@m.wrapper(m.entering, m.exiting)
def create_csv_file(
self,
num_rows,
):
""" Create csv file """
csv_creator = TestDataCreator(num_rows, self.conf_reader,
self.data_file)
csv_creator.run_csv_writing()
@m.timing
@m.wrapper(m.entering, m.exiting)
def bulk_copy_to_db(self):
""" Insert data into DB """
columns = ('transaction_uid', 'account_uid', 'transaction_date',
'type_deal', 'transaction_amount')
try:
with open(self.data_file, 'r') as csv_file:
rows = self.database.bulk_copy(csv_file,
self.raw_full_table_name,
columns)
m.info('Have been added %s rows into %s' %
(rows, self.raw_full_table_name))
except psycopg2.Error as err:
m.error('OOps! Bulk copy process FAILED! Reason: %s' % err.pgerror)
@m.timing
@m.wrapper(m.entering, m.exiting)
def random_delete_rows(self):
""" Random deleting some rows from the table """
sql_command = sql.SQL("""
delete from {0}.{1}
where ctid = any(array(
select ctid
from {0}.{1}
tablesample bernoulli (1)
))""").format(sql.Identifier(self.schema_raw),
sql.Identifier(self.raw_table_name))
try:
rows = self.database.execute(sql_command)
m.info('Has been deleted [%s rows] from table %s' %
(rows, self.raw_table_name))
except psycopg2.Error as err:
m.error('Oops! Delete random rows has been FAILED. Reason: %s' %
err.pgerror)
@m.timing
@m.wrapper(m.entering, m.exiting)
def random_update_rows(self):
""" Random update some rows from the table """
sql_command = sql.SQL("""
update {0}.{1}
set transaction_amount = round(random()::numeric, 2)
where ctid = any(array(
select ctid
from {0}.{1}
tablesample bernoulli (1) ))""").format(
sql.Identifier(self.schema_raw),
sql.Identifier(self.raw_table_name))
try:
rows = self.database.execute(sql_command)
m.info('Has been updated [%s rows] from table %s' %
(rows, self.raw_table_name))
except psycopg2.Error as err:
m.error('Oops! Delete random rows has been FAILED. Reason: %s' %
err.pgerror)
@m.timing
def run(self, num_rows):
""" Run the proceess """
m.info('START!')
self.create_db_schema()
self.create_folder('data')
self.create_csv_file(num_rows)
self.bulk_copy_to_db()
self.random_delete_rows()
self.random_update_rows()
m.info('END!')