Python 位置名称通过高德API获取行政区划信息ByMySQL

先上代码

#!/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&region=%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字段中。