先上代码
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# coding=utf-8
import pymysql
import requests
def get_infor(hospit_name, region="全国"): # return list
response = requests.get(
'https://restapi.amap.com/v5/place/text?parameters&key=【高德Key】&keywords=%s®ion=%s&types=【Pois code】' % (
hospit_name[1], region))
data_info = response.json()
# print(data_info)
data_results = data_info['pois']
json_key_name = ['name', 'address', 'pname', 'cityname', 'adname']
i = 0
for i in range(len(data_results)-1):
for key_name in json_key_name:
if not get_not_emtpy(data_results[i-1], key_name):
i += 1
else:
break
if i == len(data_results) - 1:
print('Don‘t find optimum result ,so select default number zero by list data!!!')
i = 0
try:
# todo
data_one_result = data_results[0]
except BaseException:
print('empty data! Next...')
return None
print('Count result size:', len(data_results))
print('select result number:', i)
print('Select result white Excel file:', data_results[i])
return data_one_result
def get_not_emtpy(result_json, key_name):
if result_json[key_name] is None:
return False
else:
return True
def read_target_data():
db = pymysql.connect(host="192.168.0.150", user="root", password="cd520888", database="open_cell")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM amap"
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results_sql_data = cursor.fetchall()
results_sql_arrays_data = []
for result_sql_data in results_sql_data:
results_sql_array_data = []
for i in result_sql_data:
results_sql_array_data.append(i)
results_sql_arrays_data.append(results_sql_array_data)
# print(results_sql_arrays_data)
return results_sql_arrays_data
except:
print("Error: unable to fetch data")
# 关闭数据库连接
db.close()
def batch_updata_data(result):
batch_updata_sql = "UPDATE amap SET name = (%s), address = (%s), pname = (%s), cityname = (%s), adname = (%s) WHERE id = (%s)"
results_update_sql_data = []
for i in result:
i.append(i[0])
del i[0]
del i[0]
results_update_sql_data.append(i)
db = pymysql.connect(host="***", user="***", password="***", database="***")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
cursor.executemany(batch_updata_sql, results_update_sql_data)
db.commit()
db.close()
print('done!')
if __name__ == '__main__':
sql_data = read_target_data()
n = -1 # 行数计数变量
cell_num = 0
# 重复分段,优化性能减少API消耗
a = []
x = []
for i in range(0, len(sql_data)):
if i + 1 < len(sql_data):
if sql_data[i][1] == sql_data[i + 1][1]:
x.append(sql_data[i])
else:
x.append(sql_data[i])
a.append(x)
x = []
else:
x.append(sql_data[len(sql_data) - 1])
a.append(x)
count = 0
for element in a:
count = count + 1
for count_this in range(count):
result_data = get_infor(a[count_this][0])
for result_data_for in range(len(a[count_this])):
n += 1
if result_data is not None:
sql_data[n][2] = result_data['name']
sql_data[n][3] = result_data['address']
sql_data[n][4] = result_data['pname']
sql_data[n][5] = result_data['cityname']
sql_data[n][6] = result_data['adname']
print('done,', len(sql_data) - 1 - n, 'left')
else:
print('failed', len(sql_data) - 1 - n, 'left')
batch_updata_data(sql_data)
'''python'''
目前这个版本经过了一定程度的上的性能优化
使用说明
1.填入高德地图开放平台Key
2.根据所需要转换的地理位置行业属性,按照高德提供的typs 表格数据,进行填写
3.填写数据库连接信息
数据库表结构
数据说明
请将要进行API调用的地理名称,导入到数据库old_name字段中。