python獲取全國最新省市區(qū)數(shù)據(jù)并存入表實例代碼

CREATE TABLE `districts` (
  `districtId` int(11) NOT NULL AUTO_INCREMENT,
  `districtPid` int(11) DEFAULT NULL COMMENT '上級ID',
  `name` varchar(32) DEFAULT NULL COMMENT '行政區(qū)名稱',
  `citycode` varchar(6) DEFAULT NULL COMMENT '城市編碼',
  `adcode` varchar(6) DEFAULT NULL COMMENT '城市區(qū)域編碼',
  `lng` float(13,10) DEFAULT NULL COMMENT '經(jīng)度',
  `lat` float(13,10) DEFAULT NULL COMMENT '緯度',
  `level` varchar(10) DEFAULT NULL COMMENT '行政區(qū)劃級別',
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`districtId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

# coding = utf-8
"""
@autor: linuxdba
"""
import json
import mysql.connector
import requests
 
header = {
    'User-Agent': 'Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Mobile Safari/537.36'
}
 
mydb = mysql.connector.connect(host='ip.....', user='username.....', password='pwd.....', port=3306,
                               database='test')
mycursor = mydb.cursor()
 
insert_sql = "INSERT INTO `districts` ( `districtpid`,`name`, `adcode`, `lng`, `lat`, `level`) VALUES (%s,%s, %s, %s, %s, %s);"
insert_city_sql = "INSERT INTO `districts` ( `districtpid`,`name`, `citycode`,`adcode`, `lng`, `lat`, `level`) VALUES (%s, %s, %s, %s, %s, %s, %s);"
 
select_sql = 'select districtId from districts where adcode = %s'
 
 
def updatedb(sql, *args):
    try:
        mycursor.execute(sql, args)
        mydb.commit()
    except Exception as e:
        print(e)
 
 
def selectdb(sql, *args):
    mycursor.execute(sql, args)
    return mycursor
 
 
updatedb(insert_sql, 0, '中華人民共和國', '100000', '116.3683244', '39.915085', 'country')
provinces = ['北京市', '天津市', '河北省', '山西省', '內(nèi)蒙古自治區(qū)', '遼寧省', '吉林省', '黑龍江省', '上海市', '江蘇省', '浙江省', '安徽省', '福建省', '江西省',
             '山東省', '河南省', '湖北省', '湖南省', '廣東省', '廣西壯族自治區(qū)', '海南省', '重慶市', '四川省', '貴州省', '云南省', '西藏自治區(qū)', '陜西省', '甘肅省',
             '青海省', '寧夏回族自治區(qū)', '新疆維吾爾自治區(qū)', '臺灣省', '香港特別行政區(qū)', '澳門特別行政區(qū)']
for i in provinces:
    code_url = 'https://restapi.amap.com/v3/config/district?key=d6efa016ea883e15a0782f939d6a805ekeywords={}subdistrict=2'.format(
        i)
    res = requests.get(code_url, headers=header)
    print(code_url)
    print(res.text)
    province = json.loads(res.text)['districts']
    adcode = province[0]['adcode']
    pname = province[0]['name']
    center = province[0]['center']
    pcitycode = province[0]['citycode']
    level = province[0]['level']
    lng = province[0]['center'].split(',')[0]
    lat = province[0]['center'].split(',')[1]
    city_list = province[0]['districts']
 
    # print(pname, pcitycode, adcode, lng, lat, level)
    updatedb(insert_sql, 1, pname, adcode, lng, lat, level)
    districtpid = selectdb(select_sql, adcode).fetchone()[0]
    # print(city_list)
    for city in city_list:
        citycode = city['citycode']
        adcode = city['adcode']
        name = city['name']
        level = city['level']
        lng = city['center'].split(',')[0]
        lat = city['center'].split(',')[1]
        district_list = city['districts']
        # print(name, citycode, adcode, lng, lat, level)
        updatedb(insert_city_sql, districtpid, name, citycode, adcode, lng, lat, level)
        citypid = selectdb(select_sql, adcode).fetchone()[0]
        for district in district_list:
            citycode = district['citycode']
            adcode = district['adcode']
            name = district['name']
            level = district['level']
            lng = district['center'].split(',')[0]
            lat = district['center'].split(',')[1]
            district_list = district['districts']
            if level in ['district']:
                updatedb(insert_city_sql, citypid, name, citycode, adcode, lng, lat, level)
            print(name, citycode, adcode, lng, lat, level)