def create_lookups(sess):
    ' Create the lookups used for FPDS derivations.\n\n        Args:\n            sess: connection to database\n\n        Returns:\n            Dictionaries of sub tier agencies by code, country names by code, county names by state code + county\n            code, county codes by state code + county name, state name by code, and executive compensation data by\n            DUNS number\n    '
    sub_tiers = sess.query(SubTierAgency).all()
    sub_tier_list = {}
    for sub_tier in sub_tiers:
        sub_tier_list[sub_tier.sub_tier_agency_code] = sub_tier
    countries = sess.query(CountryCode).all()
    country_list = {}
    for country in countries:
        country_list[country.country_code] = country.country_name
    county_by_name = {}
    county_by_code = {}
    state_code_list = {}
    state_codes = sess.query(States.state_code, func.upper(States.state_name).label('state_name')).all()
    for state_code in state_codes:
        county_by_name[state_code.state_code] = {}
        county_by_code[state_code.state_code] = {}
        state_code_list[state_code.state_code] = state_code.state_name
    county_codes = sess.query(CountyCode.county_number, CountyCode.state_code, func.upper(CountyCode.county_name).label('county_name')).all()
    for county_code in county_codes:
        county_name = county_code.county_name.replace(' (CA)', '').strip()
        county_by_code[county_code.state_code][county_code.county_number] = county_name
        if re.match('^[A-Z\\s]+$', county_code.county_name):
            county_by_name[county_code.state_code][county_name] = county_code.county_number
    exec_comp_dict = {}
    duns_list = sess.query(DUNS).filter(DUNS.high_comp_officer1_full_na.isnot(None)).all()
    for duns in duns_list:
        exec_comp_dict[duns.awardee_or_recipient_uniqu] = {'officer1_name': duns.high_comp_officer1_full_na, 'officer1_amt': duns.high_comp_officer1_amount, 'officer2_name': duns.high_comp_officer2_full_na, 'officer2_amt': duns.high_comp_officer2_amount, 'officer3_name': duns.high_comp_officer3_full_na, 'officer3_amt': duns.high_comp_officer3_amount, 'officer4_name': duns.high_comp_officer4_full_na, 'officer4_amt': duns.high_comp_officer4_amount, 'officer5_name': duns.high_comp_officer5_full_na, 'officer5_amt': duns.high_comp_officer5_amount}
    del duns_list
    return (sub_tier_list, country_list, state_code_list, county_by_name, county_by_code, exec_comp_dict)
Create the lookups used for FPDS derivations.

Args:
    sess: connection to database

Returns:
    Dictionaries of sub tier agencies by code, country names by code, county names by state code + county
    code, county codes by state code + county name, state name by code, and executive compensation data by
    DUNS number
def create_lookups(sess):
    ' Create the lookups used for FPDS derivations.\n\n        Args:\n            sess: connection to database\n\n        Returns:\n            Dictionaries of sub tier agencies by code, country names by code, county names by state code + county\n            code, county codes by state code + county name, state name by code, and executive compensation data by\n            DUNS number\n    '
    sub_tiers = sess.query(SubTierAgency).all()
    sub_tier_list = {}
    for sub_tier in sub_tiers:
        sub_tier_list[sub_tier.sub_tier_agency_code] = sub_tier
    countries = sess.query(CountryCode).all()
    country_list = {}
    for country in countries:
        country_list[country.country_code] = country.country_name
    county_by_name = {}
    county_by_code = {}
    state_code_list = {}
    state_codes = sess.query(States.state_code, func.upper(States.state_name).label('state_name')).all()
    for state_code in state_codes:
        county_by_name[state_code.state_code] = {}
        county_by_code[state_code.state_code] = {}
        state_code_list[state_code.state_code] = state_code.state_name
    county_codes = sess.query(CountyCode.county_number, CountyCode.state_code, func.upper(CountyCode.county_name).label('county_name')).all()
    for county_code in county_codes:
        county_name = county_code.county_name.replace(' (CA)', ).strip()
        county_by_code[county_code.state_code][county_code.county_number] = county_name
        if re.match('^[A-Z\\s]+$', county_code.county_name):
            county_by_name[county_code.state_code][county_name] = county_code.county_number
    exec_comp_dict = {}
    duns_list = sess.query(DUNS).filter(DUNS.high_comp_officer1_full_na.isnot(None)).all()
    for duns in duns_list:
        exec_comp_dict[duns.awardee_or_recipient_uniqu] = {'officer1_name': duns.high_comp_officer1_full_na, 'officer1_amt': duns.high_comp_officer1_amount, 'officer2_name': duns.high_comp_officer2_full_na, 'officer2_amt': duns.high_comp_officer2_amount, 'officer3_name': duns.high_comp_officer3_full_na, 'officer3_amt': duns.high_comp_officer3_amount, 'officer4_name': duns.high_comp_officer4_full_na, 'officer4_amt': duns.high_comp_officer4_amount, 'officer5_name': duns.high_comp_officer5_full_na, 'officer5_amt': duns.high_comp_officer5_amount}
    del duns_list
    return (sub_tier_list, country_list, state_code_list, county_by_name, county_by_code, exec_comp_dict)