]>
jfr.im git - irc/rizon/acid.git/blob - pyva/populate-zipcodes.py
2 # PyPsd's Internets bot's zip code importer, for the weather commands
3 # This is run separately to PyPsd itself, before if you want weather ZIP codes to work
11 # find config file and zipcode csv file
12 config_filename
= 'config.ini'
13 if not os
. path
. exists ( config_filename
):
14 config_filename
= os
. path
. join ( '..' , config_filename
)
15 if not os
. path
. exists ( config_filename
):
16 print "We cannot locate the pypsd/pyva config.ini file"
17 print 'This script should be run from the root acid/ directory'
20 zipcode_csv_file
= 'weather-zipcodes.csv'
21 if not os
. path
. exists ( zipcode_csv_file
):
22 zipcode_csv_file
= os
. path
. join ( 'data' , 'weather-zipcodes.csv' )
23 if not os
. path
. exists ( zipcode_csv_file
):
24 zipcode_csv_file
= os
. path
. join ( 'pyva' , zipcode_csv_file
)
25 if not os
. path
. exists ( zipcode_csv_file
):
26 print "We cannot locate the zipcode csv file"
27 print 'weather-zipcodes.csv should be in the working dir, in data/ , or in pyva/data/'
31 drop_table
= '--drop' in sys
. argv
32 debug
= '--debug' in sys
. argv
35 config
= ConfigParser
. ConfigParser ()
36 config
. readfp ( codecs
. open ( config_filename
, 'r' , 'utf8' ))
40 host
= config
. get ( 'database' , 'host' ),
41 user
= config
. get ( 'database' , 'user' ),
42 passwd
= config
. get ( 'database' , 'passwd' ),
43 db
= config
. get ( 'database' , 'db' ),
45 dbx
. autocommit ( True ) # no need to have transactions
48 # check if table exists
49 dbp
. execute ( "SELECT count(*) FROM information_schema.tables t WHERE t.table_name='zipcode_citystate';" )
50 if dbp
. fetchone ()[ 0 ] == 0 :
54 rows
= 0 # zero rows since table doesn't exist
56 # clearing/setting table information
58 # check if we should drop the table, to basically 'refresh'and start again
61 dbp
. execute ( "DROP TABLE zipcode_citystate;" )
62 print 'Current ZIP table dropped.'
66 print 'ZIP table does not exist, cannot drop.'
70 # count current rows, see if we have any info in there already
71 dbp
. execute ( "SELECT count(*) FROM zipcode_citystate;" )
72 rows
= int ( dbp
. fetchone ()[ 0 ])
74 print 'ZIP codes already exist in database. Please run:'
75 print sys
. argv
[ 0 ], '--drop'
76 print 'in order to clear the current ZIP table and populate with new ZIP codes'
79 print 'ZIP Table does not yet exist.'
80 except Exception , err
:
81 print 'Error setting up initial table information: {}' . format ( err
)
86 dbp
. execute ( "CREATE TABLE IF NOT EXISTS zipcode_citystate (zipcode INT(5), city VARCHAR(15) NOT NULL, state VARCHAR(2) NOT NULL, UNIQUE KEY (zipcode));" )
87 print 'ZIP Table created.'
88 except Exception , err
:
89 print 'Error creating database table zipcode_citystate: {}' . format ( err
)
92 # load zipfile contents
94 with open ( zipcode_csv_file
, 'rb' ) as zipfile
:
95 print 'Populating ZIP Table'
98 first_line
= True # contains row info, not necessary and breaks our int/float/etc
106 line
= line
. replace ( ' \n ' , '' ). replace ( '"' , '' ). split ( ',' )
108 zipcode
, city
, state
, latitude
, longitude
, timezone
, dst
= line
111 dbp
. execute ( "INSERT INTO zipcode_citystate (zipcode, city, state) VALUES ( %s , %s , %s );" , [ int ( zipcode
), city
, state
])
113 print 'Inserted ZIP code' , zipcode
, ':' , city
, ':' , state
114 except Exception , err
:
115 print 'Error adding ZIP code to database: ({})' . format ( err
)
117 print zipcode
, city
, state
122 if current_row
% 1000 == 0 : # print every 1k rows
123 print current_row
, 'rows inserted'
125 print current_row
, 'rows inserted in total'