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

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

# coding = utf-8"""@autor: linuxdba"""import jsonimport mysql.connectorimport 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)