def manual_process(self, table, accdb_file, batch):
from KGlobal.sql import SQLConfig
tool.write_to_log("Processing '%s' file" % basename(accdb_file))
self.batch = batch
sql_config = SQLConfig(conn_type='accdb', accdb_fp=str(accdb_file))
self.db_connect(sql_config)
if self.acc_engine:
try:
if self.__accdb_profiles and table in self.__accdb_profiles.keys(
):
self.profile = self.__accdb_profiles[table]
else:
self.profile = None
self.acc_tbl_cols(table)
error = self.__validate(table)
if error:
self.__package_err(table, error)
else:
self.__upload_profile()
finally:
self.retire_acc_engine()
self.process_email(manual=True)
tool.write_to_log("File '%s' has finished being processed" %
basename(accdb_file))
tool.gui_console(turn_off=True)
def process_file(self, accdb_file):
from KGlobal.sql import SQLConfig
tool.write_to_log("Processing '%s' file" % basename(accdb_file))
self.batch = basename(dirname(dirname(accdb_file)))
sql_config = SQLConfig(conn_type='accdb', accdb_fp=str(accdb_file))
self.db_connect(sql_config)
if self.acc_engine:
try:
for table in self.acc_tables:
if self.__accdb_profiles and table in self.__accdb_profiles.keys(
):
self.profile = self.__accdb_profiles[table]
else:
self.profile = None
self.__processed_list(accdb_file, table)
self.acc_tbl_cols(table)
error = self.__validate(table)
if error:
self.__package_err(table, error)
else:
self.__upload_profile()
finally:
self.retire_acc_engine()
def __append_features(self):
tool.write_to_log("Appending features to %s" % self.profile['SQL_TBL'])
if self.val_sql_col('Edit_DT'):
query = '''
UPDATE {0}
SET
Edit_DT = GETDATE()
WHERE
Source_File = 'Updated Records {1}'
'''.format(self.profile['SQL_TBL'], self.batch)
sql.sql_execute(query_str=query, execute=True)
elif self.val_sql_col('Edit_Date'):
query = '''
UPDATE {0}
SET
Edit_Date = GETDATE()
WHERE
Source_File = 'Updated Records {1}'
'''.format(self.profile['SQL_TBL'], self.batch)
sql.sql_execute(query_str=query, execute=True)
path = join(sql_dir, self.profile['Acc_TBL'])
if exists(path):
for file in list(Path(path).glob('*.sql')):
with Lock(str(file), 'r') as f:
query = f.read()
sql.sql_execute(query_str=query, execute=True)
def download_df(self):
self.accdb_df = DataFrame()
if self.acc_engine and self.profile:
tool.write_to_log("Downloading '%s' to a dataframe" %
self.profile['Acc_TBL'])
query = '''
SELECT
[{0}],
'Updated Records {2}' As Source_File
FROM [{1}]
'''.format('], ['.join(self.profile['Acc_Cols_Sel']),
self.profile['Acc_TBL'], self.batch)
results = self.acc_engine.sql_execute(query_str=query)
if results.results:
self.accdb_df = results.results[0]
self.accdb_df.columns = self.profile['SQL_Cols_Sel'] + (
'Source_File', )
elif results.errors:
error_msg = "Error Code '{0}', {1}".format(
results.errors[0], results.errors[1])
self.record_results(self.profile['Acc_TBL'], error_msg, 0,
self.profile['SQL_TBL'])
def upload_df(self):
if self.profile and not self.accdb_df.empty:
tool.write_to_log("Uploading {0} records to {1}".format(
len(self.accdb_df), self.profile['SQL_TBL']))
table = self.profile['SQL_TBL'].split('.')
if self.profile['SQL_TBL_Trunc'] == 1:
query = "TRUNCATE TABLE %s" % self.profile['SQL_TBL']
sql.sql_execute(query_str=query, execute=True)
results = sql.sql_upload(dataframe=self.accdb_df,
table_name=table[1],
table_schema=table[0],
if_exists='append',
index=False)
if results.errors:
error_msg = "Error Code '{0}', {1}".format(
results.errors[0], results.errors[1])
self.record_results(self.profile['Acc_TBL'], error_msg, 0,
self.profile['SQL_TBL'])
else:
self.record_results(self.profile['Acc_TBL'], None,
len(self.accdb_df),
self.profile['SQL_TBL'])
self.__append_features()
def rem_proc_elements():
tool.write_to_log('Cleaning Process directory')
for filename in listdir(process_dir):
file_path = join(process_dir, filename)
try:
if isfile(file_path) or islink(file_path):
unlink(file_path)
elif isdir(file_path):
rmtree(file_path)
except Exception as e:
tool.write_to_log(
'Failed to delete %s. Reason: %s' % (file_path, e), 'warning')
pass
def setup_ftp(self):
tool.write_to_log('Navigating FTP & Setup Folders')
self.cwd('/To Granite')
self.__entries = list(self.mlsd())
self.__entries.sort(
key=lambda entry: ""
if entry[0].startswith('SDN') or entry[1]['type'] == 'dir' or
not (entry[0].endswith('.zip') or entry[0].endswith('.rar') or
entry[0].endswith('.7z') or entry[0].endswith('.accdb') or
entry[0].endswith('.mdb')) else entry[1]['modify'],
reverse=True)
self.__upload_dt = self.__entries[0][1]['modify'][0:8]
self.__upload_dest_sub_dir = join(process_dir, self.__upload_dt)
self.__upload_dest_dir = join(self.__upload_dest_sub_dir, 'Unzipped')
if not exists(self.__upload_dest_sub_dir):
makedirs(self.__upload_dest_sub_dir)
if not exists(self.__upload_dest_dir):
makedirs(self.__upload_dest_dir)
def process_email(self, manual=False):
if len(self.__email_success) > 0 or len(self.__email_failures) > 0:
tool.write_to_log('Sending email to distros')
from exchangelib import Message
self.__email = Message(account=self.__email_engine)
if self.__to_email:
self.__email.to_recipients = self.__gen_email_list(
self.__to_email)
if self.__cc_email:
self.__email.cc_recipients = self.__gen_email_list(
self.__cc_email)
if manual:
self.__email.subject = 'Manual STC Records Upload %s' % self.batch
else:
self.__email.subject = 'STC Updated Records %s' % self.batch
self.__email.body = self.__gen_body()
self.__email.send()
def __validate(self, table):
tool.write_to_log("Validating access table '%s'" % basename(table))
if not self.profile:
return [
1,
'Acc table %s profile was never established' % table,
'Please establish new profile'
]
if not self.__validate_cols(self.profile['Acc_Cols'], False):
return [
2,
'Acc table %s column validation failed' % table,
'Please re-do assignment'
]
if len(self.profile['Acc_Cols']) != len(self.acc_cols):
return [
3,
'Acc table %s has new columns unassigned' % table,
'Please assign'
]
if not self.val_sql_tbl(self.profile['SQL_TBL']):
return [
4,
'SQL table %s is not a valid table anymore' %
self.profile['SQL_TBL'], 'Please choose new sql table'
]
self.sql_tbl_cols(self.profile['SQL_TBL'])
if not self.__validate_cols(self.profile['SQL_Cols'], True):
return [
5,
'SQL table %s column validation failed' %
self.profile['SQL_TBL'], 'Please re-do assignment'
]
def __init__(self):
try:
FTP.__init__(self, local_config['FTP_Server'].decrypt())
try:
self.login(local_config['FTP_User'].decrypt(),
local_config['FTP_Pass'].decrypt())
except Exception as err:
self.quit()
tool.write_to_log(traceback.format_exc(), 'critical')
tool.write_to_log("Credentials - Error Code '{0}', {1}".format(
type(err).__name__, str(err)))
except Exception as err:
tool.write_to_log(traceback.format_exc(), 'critical')
tool.write_to_log("Connect - Error Code '{0}', {1}".format(
type(err).__name__, str(err)))
def __unzip_file(self, zipped_fp):
if zipped_fp.endswith('.zip'):
tool.write_to_log("Unzipping '%s' file" % basename(zipped_fp))
with ZipFile(zipped_fp, 'r') as zip_ref:
zip_ref.extractall(self.__upload_dest_dir)
elif zipped_fp.endswith('.rar'):
tool.write_to_log("Unrarring '%s' file" % basename(zipped_fp))
rar = RarFile(zipped_fp)
rar.extractall(self.__upload_dest_dir)
elif zipped_fp.endswith('.7z'):
tool.write_to_log("Un7zipping '%s' file" % basename(zipped_fp))
z = Popen('7z e "{0}" -o"{1}"'.format(zipped_fp,
self.__upload_dest_dir),
shell=True)
z.wait()
z.kill()
else:
tool.write_to_log("Migrating '%s' file" % basename(zipped_fp))
rename(zipped_fp, join(self.__upload_dest_dir,
basename(zipped_fp)))
def record_results(self, table, error_msg=None, records=0, to_table=None):
if table:
if error_msg:
if to_table:
tool.write_to_log(
"{0} => {1} Completed ({2})".format(
table, to_table, error_msg), 'warning')
else:
tool.write_to_log("{0} ({1})".format(table, error_msg),
'warning')
self.__email_failures[table] = [to_table, error_msg]
else:
tool.write_to_log("{0} => {1} Completed ({2} records)".format(
table, to_table, records))
self.__email_success[table] = [to_table, records]
def ftp_download(self):
for item in self.__entries:
if item[1]['modify'][0:8] == self.__upload_dt:
download_path = join(self.__upload_dest_sub_dir, item[0])
if not exists(download_path):
tool.write_to_log("Downloading '%s' from FTP" % item[0])
try:
self.retrbinary('RETR %s' % item[0],
open(download_path, 'wb').write,
8 * 1024)
self.__unzip_file(download_path)
except Exception as err:
if exists(download_path):
unlink(download_path)
tool.write_to_log(traceback.format_exc(), 'critical')
tool.write_to_log(
"Download - Error Code '{0}', {1}".format(
type(err).__name__, str(err)))
pass
else:
break
if files:
obj = AccToSQL()
for file in files:
obj.process_file(file)
obj.migrate_file(file)
obj.save_processed()
obj.process_email()
del obj
if __name__ == '__main__':
try:
check_processed()
if check_settings():
rem_proc_elements()
stc_ftp()
proc_accdbs()
rem_proc_elements()
else:
tool.write_to_log(
"Settings - Error Code 'Check_Settings', Settings was not establish thus program exit",
action="error")
except Exception as e:
tool.write_to_log(traceback.format_exc(), 'critical')
tool.write_to_log("Main Loop - Error Code '{0}', {1}".format(
type(e).__name__, str(e)))