]>
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
10 # find config file and zipcode csv file
11 config_filename
= 'pypsd.yml'
12 if not os
. path
. exists ( config_filename
):
13 config_filename
= os
. path
. join ( '..' , config_filename
)
14 if not os
. path
. exists ( config_filename
):
15 print "We cannot locate the configuration file pypsd.yml"
16 print 'This script should be run from the root acid/ directory'
19 zipcode_csv_file
= 'weather-zipcodes.csv'
20 if not os
. path
. exists ( zipcode_csv_file
):
21 zipcode_csv_file
= os
. path
. join ( 'data' , 'weather-zipcodes.csv' )
22 if not os
. path
. exists ( zipcode_csv_file
):
23 zipcode_csv_file
= os
. path
. join ( 'pyva' , zipcode_csv_file
)
24 if not os
. path
. exists ( zipcode_csv_file
):
25 print "We cannot locate the zipcode csv file"
26 print 'weather-zipcodes.csv should be in the working dir, in data/ , or in pyva/data/'
30 drop_table
= '--drop' in sys
. argv
31 debug
= '--debug' in sys
. argv
34 with open ( config_filename
, "r" ) as config_fd
:
35 config
= yaml
. load ( config_fd
)
39 host
= config
. get ( 'database' ). get ( 'host' ),
40 user
= config
. get ( 'database' ). get ( 'user' ),
41 passwd
= config
. get ( 'database' ). get ( 'passwd' ),
42 db
= config
. get ( 'database' ). get ( 'db' ),
44 dbx
. autocommit ( True ) # no need to have transactions
47 # check if table exists
48 dbp
. execute ( "SELECT count(*) FROM information_schema.tables t WHERE t.table_name='zipcode_citystate';" )
49 if dbp
. fetchone ()[ 0 ] == 0 :
53 rows
= 0 # zero rows since table doesn't exist
55 # clearing/setting table information
57 # check if we should drop the table, to basically 'refresh'and start again
60 dbp
. execute ( "DROP TABLE zipcode_citystate;" )
61 print 'Current ZIP table dropped.'
65 print 'ZIP table does not exist, cannot drop.'
69 # count current rows, see if we have any info in there already
70 dbp
. execute ( "SELECT count(*) FROM zipcode_citystate;" )
71 rows
= int ( dbp
. fetchone ()[ 0 ])
73 print 'ZIP codes already exist in database. Please run:'
74 print sys
. argv
[ 0 ], '--drop'
75 print 'in order to clear the current ZIP table and populate with new ZIP codes'
78 print 'ZIP Table does not yet exist.'
79 except Exception , err
:
80 print 'Error setting up initial table information: {}' . format ( err
)
85 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));" )
86 print 'ZIP Table created.'
87 except Exception , err
:
88 print 'Error creating database table zipcode_citystate: {}' . format ( err
)
91 # load zipfile contents
93 with open ( zipcode_csv_file
, 'rb' ) as zipfile
:
94 print 'Populating ZIP Table'
97 first_line
= True # contains row info, not necessary and breaks our int/float/etc
105 line
= line
. replace ( ' \n ' , '' ). replace ( '"' , '' ). split ( ',' )
107 zipcode
, city
, state
, latitude
, longitude
, timezone
, dst
= line
110 dbp
. execute ( "INSERT INTO zipcode_citystate (zipcode, city, state) VALUES ( %s , %s , %s );" , [ int ( zipcode
), city
, state
])
112 print 'Inserted ZIP code' , zipcode
, ':' , city
, ':' , state
113 except Exception , err
:
114 print 'Error adding ZIP code to database: ({})' . format ( err
)
116 print zipcode
, city
, state
121 if current_row
% 1000 == 0 : # print every 1k rows
122 print current_row
, 'rows inserted'
124 print current_row
, 'rows inserted in total'