Example #1
0
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')
Example #2
0
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)