python获取全国最新省市区数据并存入表实例代码

本文通过调取高德行政区划查询接口，获取最新的数据信息（省、市、区、经纬度、行政级别、城市编码、行政编码等），并通过mysql.connector存入mysql数据库

表结构设计如下：

CREATE TABLE `districts` (
  `districtId` int(11) NOT NULL AUTO_INCREMENT,
  `districtPid` int(11) DEFAULT NULL COMMENT \'上级ID\',
  `name` varchar(32) DEFAULT NULL COMMENT \'行政区名称\',
  `citycode` varchar(6) DEFAULT NULL COMMENT \'城市编码\',
  `adcode` varchar(6) DEFAULT NULL COMMENT \'城市区域编码\',
  `lng` float(13,10) DEFAULT NULL COMMENT \'经度\',
  `lat` float(13,10) DEFAULT NULL COMMENT \'纬度\',
  `level` varchar(10) DEFAULT NULL COMMENT \'行政区划级别\',
  `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

python代码如下：(需替换成自己的数据库地址帐号密码、及换入自己的高德key值)

# 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 = [\'北京市\', \'天津市\', \'河北省\', \'山西省\', \'内蒙古自治区\', \'辽宁省\', \'吉林省\', \'黑龙江省\', \'上海市\', \'江苏省\', \'浙江省\', \'安徽省\', \'福建省\', \'江西省\',
             \'山东省\', \'河南省\', \'湖北省\', \'湖南省\', \'广东省\', \'广西壮族自治区\', \'海南省\', \'重庆市\', \'四川省\', \'贵州省\', \'云南省\', \'西藏自治区\', \'陕西省\', \'甘肃省\',
             \'青海省\', \'宁夏回族自治区\', \'新疆维吾尔自治区\', \'台湾省\', \'香港特别行政区\', \'澳门特别行政区\']
for i in provinces:
    code_url = \'https://restapi.amap.com/v3/config/district?key=d6efa016ea883e15a0782f939d6a805e&keywords={}&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)

结果如下：

表数据文件链接：

http://linuxdba.ltd/files/districts.sql  或者点击这里本地下载

总结