Skip to content

Instantly share code, notes, and snippets.

@Ray901
Created March 16, 2018 07:21
Show Gist options
  • Save Ray901/f2c115e838b5143d5d7e7fc8765d83f5 to your computer and use it in GitHub Desktop.
Save Ray901/f2c115e838b5143d5d7e7fc8765d83f5 to your computer and use it in GitHub Desktop.
Use geocoder trans address to lan and lng
# 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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment