コード例 #1
0
ファイル: extract.py プロジェクト: cmc333333/hmda_tools
def extract_counties(db):
    counties= db.execute('select county_fips_code, state_fips_code, name as county_name, state.abbr as state_abbr from county, state where state_fips_code=fips_code')
    for county in counties:
        county_fips, state_fips, county_name, state_abbr = county
        destination_dir='extracts/county/%s' % state_abbr
        mkdir_p(destination_dir)
        outfile_name = destination_dir +'/' + clean_filename(county_name) + '.csv'
        print "writing county %s(%s) to %s"%  (county_name, state_abbr, outfile_name)
        with open(outfile_name, 'w') as outfile:
            writer=csv.writer(outfile)
            hmda_records=db.execute('select * from hmda where state_code= %s and county_code = %s' % (state_fips, county_fips))
            writer.writerow(HMDA_COLUMNS)
            for row in hmda_records:
                writer.writerow(row)
コード例 #2
0
        reader = csv.reader(csvfile, dialect='excel-tab')
        headers = map(string.strip, reader.next())

        for row in reader:
            row = csv_row_to_dict(headers, row)
            geoid = row['fipscd']
            state_fips = int(geoid[0:2])
            county_fips = int(geoid[2:5])

            if row['CBSA']:
                conn.execute(county.update().
                             where(county.c.state_fips_code==state_fips).
                             where(county.c.county_fips_code==county_fips).
                             values(cbsa_code=int(row['CBSA'])))

    
if __name__ == "__main__":
    mkdir_p('tmp')
    gaz_zip_file = 'tmp/Gaz_counties_national.zip'
    gaz_txt_file = 'Gaz_counties_national.txt'
    crosswalk_file = 'tmp/crosswalk.txt'
   
    create_database(args.conn_str)
    download_gazetteer(gaz_zip_file)
    unzip_gazetteer(gaz_zip_file, gaz_txt_file)
    insert_gaz_data(gaz_txt_file)
    os.remove(gaz_txt_file)

    download_crosswalk(crosswalk_file)
    insert_crosswalk_data(crosswalk_file)
コード例 #3
0
ファイル: extract.py プロジェクト: cmc333333/hmda_tools
            writer=csv.writer(outfile)
            hmda_records=db.execute('select * from hmda where state_code= %s and county_code = %s' % (state_fips, county_fips))
            writer.writerow(HMDA_COLUMNS)
            for row in hmda_records:
                writer.writerow(row)
        

def extract_msas(db):
    for cbsa in db.cbsa.all():
        outfile_name=clean_filename(cbsa.name)
        print "writing MSA %s to %s.csv" % (cbsa.name, outfile_name)
        with open('extracts/msa/%s.csv' % outfile_name, 'w') as outfile:
            writer=csv.writer(outfile)
            hmda_records=db.execute('select * from hmda where msa_md = %s' % cbsa.cbsa_code)
            writer.writerow(HMDA_COLUMNS)
            for row in hmda_records:
                writer.writerow(row)



if __name__ == '__main__':
    
    parser = argparse.ArgumentParser(description='extract CSV for every MSA, county, and State')
    parser.add_argument('conn_str', help='connection string for the database')
    args = parser.parse_args()
    db = sqlsoup.SQLSoup(args.conn_str)
    mkdir_p('extracts/county')
    extract_counties(db)
    mkdir_p('extracts/msa')
    extract_msas(db)