def load_zip_city_data(zip_city_file, force_reload): """ Load data into the ZipCity table Args: zip_city_file: path/url to file to gather ZipCity data from force_reload: boolean to determine if reload should happen whether there are differences or not """ new_data = parse_zip_city_file(zip_city_file) diff_found = check_dataframe_diff(new_data, ZipCity, 'zip_city_id', ['zip_code']) if force_reload or diff_found: sess = GlobalDB.db().session logger.info( 'Differences found or reload forced, reloading zip_city table.') # delete any data in the ZipCity table sess.query(ZipCity).delete() # insert data into table num = insert_dataframe(new_data, ZipCity.__table__.name, sess.connection()) logger.info('{} records inserted to zip_city'.format(num)) sess.commit() else: logger.info('No differences found, skipping zip_city table reload.')
def load_zip_city_data(force_reload): """ Load data into the ZipCity table Args: force_reload: boolean to determine if reload should happen whether there are differences or not """ if CONFIG_BROKER["use_aws"]: s3_client = boto3.client('s3', region_name=CONFIG_BROKER['aws_region']) citystate_file = s3_client.generate_presigned_url('get_object', {'Bucket': CONFIG_BROKER['sf_133_bucket'], 'Key': "ctystate.txt"}, ExpiresIn=600) zip_city_file = urllib.request.urlopen(citystate_file) else: citystate_file = os.path.join(CONFIG_BROKER["path"], "dataactvalidator", "config", "ctystate.txt") zip_city_file = open(citystate_file) new_data = parse_zip_city_file(zip_city_file) diff_found = check_dataframe_diff(new_data, ZipCity, ['zip_city_id'], ['zip_code']) if force_reload or diff_found: sess = GlobalDB.db().session logger.info('Differences found or reload forced, reloading zip_city table.') # delete any data in the ZipCity table sess.query(ZipCity).delete() # insert data into table num = insert_dataframe(new_data, ZipCity.__table__.name, sess.connection()) logger.info('{} records inserted to zip_city'.format(num)) sess.commit() else: logger.info('No differences found, skipping zip_city table reload.')
def load_county_data(county_file, force_reload): """ Load data into the CountyCode table Args: county_file: path/url to file to gather County data from force_reload: boolean to determine if reload should happen whether there are differences or not """ new_data = parse_county_file(county_file) diff_found = check_dataframe_diff(new_data, CountyCode, 'county_code_id', ['county_number', 'state_code']) if force_reload or diff_found: sess = GlobalDB.db().session logger.info( 'Differences found or reload forced, reloading county_code table.') # delete any data in the CountyCode table sess.query(CountyCode).delete() # insert data into table num = insert_dataframe(new_data, CountyCode.__table__.name, sess.connection()) logger.info('{} records inserted to county_code'.format(num)) sess.commit() else: logger.info('No differences found, skipping county_code table reload.')
def load_state_data(force_reload): """ Load data into the States table Args: force_reload: boolean to determine if reload should happen whether there are differences or not """ start_time = datetime.now() state_file_url = '{}/state_list.csv'.format(CONFIG_BROKER['usas_public_reference_url']) with RetrieveFileFromUri(state_file_url, 'r').get_file_object() as state_file: new_data = parse_state_file(state_file) diff_found = check_dataframe_diff(new_data, States, ['states_id'], ['state_code']) if force_reload or diff_found: sess = GlobalDB.db().session logger.info('Differences found or reload forced, reloading states table.') # delete any data in the States table sess.query(States).delete() # insert data into table num = insert_dataframe(new_data, States.__table__.name, sess.connection()) logger.info('{} records inserted to states'.format(num)) sess.commit() update_external_data_load_date(start_time, datetime.now(), 'state_code') else: logger.info('No differences found, skipping states table reload.')
def load_frec(file_name, force_reload=False): """ Load FREC (high-level agency names) lookup table. Args: file_name: path/url to the file to be read force_reload: whether to reload regardless Returns: True if new data was loaded, False if the load was skipped """ sess = GlobalDB.db().session models = {frec.frec_code: frec for frec in sess.query(FREC)} # read FREC values from csv data = pd.read_csv(file_name, dtype=str) # clean data data = clean_data( data, FREC, {'frec': 'frec_code', 'cgac_agency_code': 'cgac_code', 'frec_entity_description': 'agency_name', 'frec_abbreviation': 'frec_abbreviation', 'frec_cgac_association': 'frec_cgac', 'icon_filename': 'icon_name'}, {'frec': {'keep_null': False}, 'cgac_code': {'pad_to_length': 3}, 'frec_code': {'pad_to_length': 4}} ) data['icon_name'] = data['icon_name'].apply(clean_col, args=False) # de-dupe data = data[data.frec_cgac == 'TRUE'] data.drop(['frec_cgac'], axis=1, inplace=True) data.drop_duplicates(subset=['frec_code'], inplace=True) # create foreign key dicts cgac_dict = {str(cgac.cgac_code): cgac.cgac_id for cgac in sess.query(CGAC).filter(CGAC.cgac_code.in_(data['cgac_code'])).all()} cgac_dict_flipped = {cgac_id: cgac_code for cgac_code, cgac_id in cgac_dict.items()} # compare to existing content in table def extract_cgac(row): return cgac_dict_flipped[row['cgac_id']] if row['cgac_id'] in cgac_dict_flipped else np.nan diff_found = check_dataframe_diff(data, FREC, ['frec_id', 'cgac_id'], ['frec_code'], lambda_funcs=[('frec_abbreviation', extract_abbreviation), ('agency_name', extract_name), ('cgac_code', extract_cgac)]) if force_reload or diff_found: # create foreign key dicts # insert to db delete_missing_frecs(models, data) update_frecs(models, data, cgac_dict) sess.add_all(models.values()) sess.commit() logger.info('%s FREC records inserted', len(models)) return True else: logger.info('No differences found, skipping frec table reload.') return False
def load_defc(force_reload=False): """ Loads the DEFC data. Args: force_reload: boolean to determine if reload should happen whether there are differences or not """ start_time = datetime.now() defc_file = os.path.join(CONFIG_BROKER['path'], 'dataactvalidator', 'config', 'def_codes.csv') try: # Update file from public S3 bucket def_codes_url = '{}/def_codes.csv'.format( CONFIG_BROKER['usas_public_reference_url']) r = requests.get(def_codes_url, allow_redirects=True) open(defc_file, 'wb').write(r.content) except Exception: pass logger.info('Loading defc data') with create_app().app_context(): data = pd.read_csv(defc_file, dtype=str) # Remove all invalid DEFCs that have been left in the file so USAS can continue to display them correctly data = data[data['Is Valid'] == 'true'] data = clean_data(data, DEFC, { 'defc': 'code', 'group_name': 'group' }, {}) diff_found = check_dataframe_diff(data, DEFC, ['defc_id'], ['code']) if force_reload or diff_found: sess = GlobalDB.db().session # delete any data in the DEFC table sess.query(DEFC).delete() # insert data into table num = insert_dataframe(data, DEFC.__table__.name, sess.connection()) logger.info('{} records inserted to defc'.format(num)) sess.commit() update_external_data_load_date(start_time, datetime.now(), 'defc') else: logger.info('No differences found, skipping defc table reload.')
def load_sql(cls, filename): """ Load SQL-based validation rules to db. """ with create_app().app_context(): sess = GlobalDB.db().session filename = os.path.join(cls.sql_rules_path, filename) # Initial load sql_data = pd.read_csv(filename, dtype=str, usecols=cls.headers) sql_data = clean_data( sql_data, RuleSql, {'rule_label': 'rule_label', 'rule_error_message': 'rule_error_message', 'query_name': 'query_name', 'expected_value': 'expected_value', 'category': 'category', 'file_type': 'file_type', 'target_file': 'target_file', 'rule_cross_file_flag': 'rule_cross_file_flag', 'severity_name': 'severity_name'}, {} ) # Processing certain values sql_data['rule_sql'] = sql_data['query_name'].apply(lambda name: cls.read_sql_str(name)) sql_data['file_id'] = sql_data['file_type'].apply(lambda type: FILE_TYPE_DICT.get(type, None)) if sql_data['file_id'].isnull().values.any(): raise Exception('Invalid file_type value found in sqlLoader. Must be one of the following: {}' .format(', '.join(list(FILE_TYPE_DICT.keys())))) sql_data['target_file_id'] = sql_data['target_file'].apply(lambda type: FILE_TYPE_DICT.get(type, None)) sql_data['rule_cross_file_flag'] = sql_data['rule_cross_file_flag'].apply(lambda flag: flag in ('true', 't', 'y', 'yes')) sql_data['rule_severity_id'] = sql_data['severity_name'].apply(lambda severity_name: RULE_SEVERITY_DICT.get(severity_name, None)) if sql_data['rule_severity_id'].isnull().values.any(): raise Exception('Invalid severity_name value found in sqlLoader Must be one of the following: {}' .format(', '.join(list(RULE_SEVERITY_DICT.keys())))) sql_data.drop(['file_type', 'severity_name', 'target_file'], axis=1, inplace=True) # Final check if we need to actually reload if check_dataframe_diff(sql_data, RuleSql, del_cols=['rule_sql_id', 'created_at', 'updated_at'], sort_cols=['rule_label', 'file_id', 'target_file_id']): # Delete and reload all records currently in table logger.info('Detected changes in {}, deleting RuleSQL and reloading'.format(cls.sql_rules_path)) sess.query(RuleSql).delete() insert_dataframe(sql_data, RuleSql.__table__.name, sess.connection()) sess.commit() else: logger.info('No changes detected since last load. Skipping.')
def load_cgac(file_name, force_reload=False): """ Load CGAC (high-level agency names) lookup table. Args: file_name: path/url to the file to be read force_reload: whether to reload regardless Returns: True if new data was loaded, False if the load was skipped """ sess = GlobalDB.db().session models = {cgac.cgac_code: cgac for cgac in sess.query(CGAC)} # read CGAC values from csv data = pd.read_csv(file_name, dtype=str) # clean data data = clean_data( data, CGAC, {'cgac_agency_code': 'cgac_code', 'agency_name': 'agency_name', 'agency_abbreviation': 'agency_abbreviation', 'icon_filename': 'icon_name'}, {'cgac_code': {'pad_to_length': 3}} ) data['icon_name'] = data['icon_name'].apply(clean_col, args=False) # de-dupe data.drop_duplicates(subset=['cgac_code'], inplace=True) # compare to existing content in table diff_found = check_dataframe_diff(data, CGAC, ['cgac_id'], ['cgac_code'], lambda_funcs=[('agency_abbreviation', extract_abbreviation), ('agency_name', extract_name)]) if force_reload or diff_found: delete_missing_cgacs(models, data) update_cgacs(models, data) sess.add_all(models.values()) sess.commit() logger.info('%s CGAC records inserted', len(models)) return True else: logger.info('No differences found, skipping cgac table reload.') return False
def load_cfda_program(base_path, load_local=False, local_file_name="cfda_program.csv"): """ Load cfda program. Args: base_path: directory that contains the cfda values files. load_local: boolean indicating whether to load from a local file or not local_file_name: the name of the file if loading locally """ local_now = datetime.now() if not load_local: logger.info("Fetching CFDA file from {}".format(S3_CFDA_FILE)) tmp_name = str(time.time()).replace(".", "") + "_cfda_program.csv" filename = os.path.join(base_path, tmp_name) r = requests.get(S3_CFDA_FILE, allow_redirects=True) open(filename, 'wb').write(r.content) else: filename = os.path.join(base_path, local_file_name) logger.info('Loading CFDA program file: ' + filename) model = CFDAProgram metrics_json = { 'script_name': 'load_cfda_data.py', 'start_time': str(local_now), 'new_records': 0 } def fix_program_number(row, decimals=3): multiplier = 10 ** decimals value = math.floor(row['program_number'] * multiplier + 0.5) / multiplier return str(value).ljust(6, '0') with create_app().app_context(): configure_logging() sess = GlobalDB.db().session import_data = pd.read_csv(filename, dtype=str, encoding='cp1252', na_filter=False) import_data = clean_data( import_data, model, DATA_CLEANING_MAP, {} ) import_data["published_date"] = format_date(import_data["published_date"]) import_data["archived_date"] = format_date(import_data["archived_date"]) table_name = model.__table__.name # Check if there is new data to load new_data = check_dataframe_diff(import_data, model, ['cfda_program_id'], ['program_number'], lambda_funcs=[('program_number', fix_program_number)]) if new_data: # insert to db sess.query(model).delete() num = insert_dataframe(import_data, table_name, sess.connection()) sess.commit() # If we've updated the data at all, update the external data load date update_external_data_load_date(local_now, datetime.now(), 'cfda') if not load_local: os.remove(filename) if new_data: logger.info('{} records inserted to {}'.format(num, table_name)) metrics_json['new_records'] = num else: logger.info("Skipped cfda load, no new data.") sys.exit(3) metrics_json['duration'] = str(datetime.now() - local_now) with open('load_cfda_data_metrics.json', 'w+') as metrics_file: json.dump(metrics_json, metrics_file)
def load_country_codes(base_path, force_reload=False): """ Load Country Codes into the database. Args: base_path: directory that contains the domain values files. force_reload: boolean to determine if reload should happen whether there are differences or not """ now = datetime.datetime.now() metrics_json = { 'script_name': 'load_country_codes.py', 'start_time': str(now), 'records_deleted': 0, 'records_provided': 0, 'duplicates_dropped': 0, 'records_inserted': 0 } with create_app().app_context(): sess = GlobalDB.db().session country_code_file = '{}/country_codes.csv'.format( CONFIG_BROKER['usas_public_reference_url']) logger.info( 'Loading country codes file from {}'.format(country_code_file)) # Get data from public S3 bucket r = requests.get(country_code_file, allow_redirects=True) filename = os.path.join(base_path, 'country_codes.csv') open(filename, 'wb').write(r.content) # Parse data data = pd.read_csv(filename, dtype=str) metrics_json['records_provided'] = len(data.index) data = clean_data( data, CountryCode, { 'country_code': 'country_code', 'country_name': 'country_name', '2_char_country_code': 'country_code_2_char', 'territory_or_freely_associated_state': 'territory_free_state' }, {}) # de-dupe data.drop_duplicates(subset=['country_code'], inplace=True) metrics_json['duplicates_dropped'] = metrics_json[ 'records_provided'] - len(data.index) # compare to existing content in table diff_found = check_dataframe_diff(data, CountryCode, ['country_code_id'], ['country_code'], lambda_funcs=[ ('territory_free_state', convert_territory_bool_to_str) ]) # insert to db if reload required if force_reload or diff_found: logger.info( 'Differences found or reload forced, reloading country_code table.' ) # if there's a difference, clear out the old data before adding the new stuff metrics_json['records_deleted'] = sess.query(CountryCode).delete() num = insert_dataframe(data, CountryCode.__table__.name, sess.connection()) metrics_json['records_inserted'] = num sess.commit() # Updating data load dates if the load successfully added new country codes update_external_data_load_date(now, datetime.datetime.now(), 'country_code') logger.info( '{} records inserted to country_code table'.format(num)) else: logger.info( 'No differences found, skipping country_code table reload.') # Delete file once we're done os.remove(filename) metrics_json['duration'] = str(datetime.datetime.now() - now) with open('load_country_codes_metrics.json', 'w+') as metrics_file: json.dump(metrics_json, metrics_file) logger.info('Script complete')
def load_sub_tier_agencies(file_name, force_reload=False): """ Load Sub Tier Agency (sub_tier-level agency names) lookup table. Args: file_name: path/url to the file to be read force_reload: whether to reload regardless Returns: True if new data was loaded, False if the load was skipped """ sess = GlobalDB.db().session models = {sub_tier_agency.sub_tier_agency_code: sub_tier_agency for sub_tier_agency in sess.query(SubTierAgency)} # read Sub Tier Agency values from csv data = pd.read_csv(file_name, dtype=str) condition = data['TOPTIER_FLAG'] == 'TRUE' data.loc[condition, 'PRIORITY'] = 1 data.loc[~condition, 'PRIORITY'] = 2 data.replace({'TRUE': True, 'FALSE': False}, inplace=True) # clean data data = clean_data( data, SubTierAgency, {'cgac_agency_code': 'cgac_code', 'subtier_code': 'sub_tier_agency_code', 'priority': 'priority', 'frec': 'frec_code', 'subtier_name': 'sub_tier_agency_name', 'is_frec': 'is_frec'}, {'cgac_code': {'pad_to_length': 3}, 'frec_code': {'pad_to_length': 4}, 'sub_tier_agency_code': {'pad_to_length': 4}} ) # de-dupe data.drop_duplicates(subset=['sub_tier_agency_code'], inplace=True) # create foreign key dicts cgac_dict = {str(cgac.cgac_code): cgac.cgac_id for cgac in sess.query(CGAC).filter(CGAC.cgac_code.in_(data['cgac_code'])).all()} cgac_dict_flipped = {cgac_id: cgac_code for cgac_code, cgac_id in cgac_dict.items()} frec_dict = {str(frec.frec_code): frec.frec_id for frec in sess.query(FREC).filter(FREC.frec_code.in_(data['frec_code'])).all()} frec_dict_flipped = {frec_id: frec_code for frec_code, frec_id in frec_dict.items()} # compare to existing content in table def int_to_float(row): return float(row['priority']) def extract_cgac(row): return cgac_dict_flipped[row['cgac_id']] if row['cgac_id'] in cgac_dict_flipped else np.nan def extract_frec(row): return frec_dict_flipped[row['frec_id']] if row['frec_id'] in frec_dict_flipped else np.nan diff_found = check_dataframe_diff(data, SubTierAgency, ['sub_tier_agency_id', 'cgac_id', 'frec_id'], ['sub_tier_agency_code'], lambda_funcs=[('priority', int_to_float), ('cgac_code', extract_cgac), ('frec_code', extract_frec)]) if force_reload or diff_found: delete_missing_sub_tier_agencies(models, data) update_sub_tier_agencies(models, data, cgac_dict, frec_dict) sess.add_all(models.values()) sess.commit() logger.info('%s Sub Tier Agency records inserted', len(models)) return True else: logger.info('No differences found, skipping subtier table reload.') return False