# coding=utf-8

import geocoder
import pyodbc

updateNum = 10

# sql setting
local_server = ''
local_database = ''
local_username = ''
local_password = ''
local_driver = '{SQL Server}'

# connect sql and read data
connection = pyodbc.connect(
	'Driver='+ local_driver +';'
	'Server='+ local_server +';'
	'Database='+ local_database +';'
	'uid='+ local_username +';pwd=' + local_password)
cursor = connection.cursor()
SQLCommand = ("""
	SELECT TOP (%d) [CONT_TARGET_ID]
      ,[CONT_TARGET_ADDR]
      ,[CONT_TARGET_LAT]
      ,[CONT_TARGET_LON]
  FROM [DW_CRM].[dbo].[D_CONT_TARGET]
  WHERE CONT_TARGET_LAT IS NULL
""" % (updateNum))
cursor.execute(SQLCommand)
results = cursor.fetchall()
print "read sql end"

# update lat and lng
for dataRow in results:
	g = geocoder.google(dataRow[1],language = 'zh-TW')
	if g.lat is not None and g.lng is not None:
		SQLUpdate = ("""
		UPDATE [dbo].[D_CONT_TARGET]
		SET [CONT_TARGET_LAT] = %f
			,[CONT_TARGET_LON] = %f
		WHERE [CONT_TARGET_ID] = %d
		""" % (g.lat, g.lng, dataRow[0]))
		print SQLUpdate
		print "update lat and lng success"
		cursor.execute(SQLUpdate)
	else :
		print "update failed"
	print dataRow[0], g.address, g.county, g.latlng
connection.commit()
cursor.close()
connection.close()