def load_csv_into_db(ld):
"""
Function to load tsv, lst, csv files onto the temporary tables.
Intermediate step to store tmp raw data before storing into the real tables.
:param ld: the file dowloaded from the FTP server provided by federfarma.co
:return: None
"""
if check_db_empty():
logger.info("___Ready to load lst file into table___")
if check_files(ld, 'lst') and check_tables():
for entry in ASSOCIATION_LIST:
try:
file = os.path.join(ld, '{0}.{1}'.format(entry[0], 'lst'))
copy_str = "\copy %s from %s CSV DELIMITER '~' HEADER;" % (entry[1], file)
command = 'export PGPASSWORD=%s\n psql -U %s -h %s -p %s -d %s -c "%s"' % \
(PGPASS, PGUSER, PGHOST, PGPORT, PGDBNAME, copy_str)
logger.info('___SQLExec: {0}___'.format(command))
subprocess.check_call(command, shell=True)
except subprocess.CalledProcessError:
logger.error("___Failed to invoke psql for: {0}___".format(entry))
logger.info("___Now truncate all the tmp tables!!!___")
truncate_tables()
raise psycopg2.DatabaseError
else:
logger.error('___Check Tables: {0} --- Check Files: {1}___'.format(check_tables(), check_files(ld, 'lst')))
raise Exception('___Tables or files not present in db or filesystem!!!___')
else:
logger.error('___DB is not empty!!!___')
raise Exception('Object DB', 'not empty')
def main():
if SemSyncManager().is_green():
ts = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
SemSyncManager().write_flags(1, 1, ts, '') # Checking the EXP_SEM_SYNC table to verify if i can get the files
FtpCrudStuff(HOST, USER, PASSWORD).dirlist()
FtpCrudStuff(HOST, USER, PASSWORD).getfiles(SAVE_TO, deleteremotefiles=False) # Get files from FTP server
ld = DirUtils(ROOT_DIR).get_last_created_dir()
truncate_tables()
if check_tables() and check_files(ld, 'zip'): # Go on if all tables and all files are present
if ZipUtils(ld).unzipfiles(): # Unzip all files whitin the last created dir
DirUtils(ld).delete_file_with_extension('.zip') # Delete all .zip files
for f in DirUtils(ld).listfiletype(['lst']): # I need to clead each file from spaces and strange stuff
# if f[0] in ['FARMACIE_UNIFARM.lst', 'FORNITORE_FARMACIA_UNIFARM.lst', 'CAUSALE_UNIFARM.lst']:
# cleanfile([os.path.join(ld, f[0])], '\s*~', False)
# else:
cleanfile('s/\s*~\s*/~/g', os.path.join(ld, f[0]))
try:
load_csv_into_db(ld) # Load .lst files into DB Postgres temporary tables
ts2 = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
SemSyncManager().write_flags(1, 0, ts, ts2) # Wrtie the rigth flag into EXP_SEM_SYNC table
ExpLogKeysManager().write_flag_import(synckey_list()) # Write the rigth flag into EXP_LOG_KEYS
store_sync_keys(synckey_list()) # Saving the acquired keys
flow_temp_data() # transfer the data from tmp tables to the real one
except (Exception, psycopg2.DatabaseError):
logger.error('___An error occurred during db import___')
SemSyncManager().write_flags(0, 1, '', '')
shutil.rmtree(ld, ignore_errors=True)
sys.exit(1)
else:
shutil.rmtree(ld, ignore_errors=True)
SemSyncManager().write_flags(0, 1, '', '')
logger.error('___All files was deleted and nothing loaded into temp DB! Read the log stack please!___')
sys.exit(1)
else:
SemSyncManager().write_flags(0, 1, '', '')
shutil.rmtree(ld, ignore_errors=True)
logger.error('___Check Tables: {0} --- Check Files: {1}___'.format(check_tables(), check_files(ld, 'zip')))
sys.exit(1)
else:
logger.error('___Unable to start import. Sem sync is not green!___')
sys.exit(1)