#!/usr/bin/python2.4
#
# Connect to PostgresDB table containing 'a full address';
# Use geocoder library with Google API to update DB row with geocoder values
#
# Before running:
# pip install psycopg2
# pip install geocoder

import psycopg2
import psycopg2.extras
import geocoder

try:
    conn = psycopg2.connect("dbname='location_db' user='postgres' host='localhost' password='postgres'")
except:
    print "I am unable to connect to the database"

cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

try:
    cur.execute("""SELECT id, full_address from location_table order by id asc""")
except:
    print "I can't SELECT from location_table"

rows = cur.fetchall()
counter = 1
for row in rows:

    # # Geocoding an address
    g = geocoder.google(row['full_address'], components="country:US", long_name=True)

    print row['id'], ":", g

    updates = {
        'g_street_name' : g.road_long,
        'g_street_number' : g.street_number,
        'g_city' : g.locality,
        'g_zip' : g.postal,
        'g_apt' : g.subpremise,
        'g_neighborhood' : g.neighborhood,
        'g_lat' : g.lat,
        'g_lng' : g.lng
    }
    sql_template = "UPDATE location_table SET ({}) = %s WHERE id = {};"
    sql = sql_template.format(', '.join(updates.keys()), row['id'])
    params = (tuple(updates.values()),)
    cur.execute(sql, params)
    conn.commit()