Census Loader


import numpy as np
import pandas as pd
import psycopg2
import os, os.path
import itertools

conn_string = "host='localhost' dbname='' user='' password='' port="
conn=psycopg2.connect(conn_string)
cur = conn.cursor()


#for each census folder
#create a template table for data insertion

#unzip every file
def unzip():
    os.chdir('/Users/ntapia/Downloads/2013_Tiger')
    folders = []
    for folder in os.listdir('.'):
        folders.append(folder)
    for folder in folders[1:]:
        os.chdir('/Users/ntapia/Downloads/2013_Tiger/' + folder)
        for item in os.listdir('.'):
            if 'zip' in item:
                print (item)
            os.system('unzip '+ item)

def add_table(x):
    os.chdir('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    print ('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    for file_ in os.listdir('.'):
            if '.shp' in file_ and 'xml' not in file_:
                cur.execute("drop table if exists %s;" % item)
                conn.commit()
                
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
            
                os.system('shp2pgsql -W "latin1" -s 4269 /Users/ntapia/Downloads/2013_Tiger/%s/%s %s | psql -h localhost -p 5432 -d census' % (x, file_, file_[:-4]))
                    
                cur.execute("CREATE TABLE %s (LIKE %s);" % (item, file_[:-4]))
                conn.commit()
                    
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
                
                
                
def load_data(x):
    os.chdir('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    for file_ in os.listdir('.'):
        if '.shp' in file_ and 'xml' not in file_:
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
            
                os.system('shp2pgsql -W "latin1" -s 4269 /Users/ntapia/Downloads/2013_Tiger/%s/%s %s | psql -h localhost -p 5432 -d census' % (x, file_, file_[:-4]))
                    
                cur.execute("insert into %s select * from %s;" % (item, file_[:-4]))
                conn.commit()
                    
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
    print ('done')
    

    
os.chdir('/Users/ntapia/Downloads/2013_Tiger')
census_folders = []
for table in os.listdir('.'):
     if '.DS' not in table:
         census_folders.append(table)

unzip()
            
for item in census_folders:
    add_table(item)

for item in census_folders:
    load_data(item)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s