python獲取全國(guó)最新省市區(qū)數(shù)據(jù)并存入表實(shí)例代碼
本文通過調(diào)取高德行政區(qū)劃查詢接口,獲取最新的數(shù)據(jù)信息(省、市、區(qū)、經(jīng)緯度、行政級(jí)別、城市編碼、行政編碼等),并通過mysql.connector存入mysql數(shù)據(jù)庫
表結(jié)構(gòu)設(shè)計(jì)如下:
CREATE TABLE `districts` ( `districtId` int(11) NOT NULL AUTO_INCREMENT, `districtPid` int(11) DEFAULT NULL COMMENT '上級(jí)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ū)劃級(jí)別', `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代碼如下:(需替換成自己的數(shù)據(jù)庫地址帳號(hào)密碼、及換入自己的高德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, '中華人民共和國(guó)', '100000', '116.3683244', '39.915085', 'country') provinces = ['北京市', '天津市', '河北省', '山西省', '內(nèi)蒙古自治區(qū)', '遼寧省', '吉林省', '黑龍江省', '上海市', '江蘇省', '浙江省', '安徽省', '福建省', '江西省', '山東省', '河南省', '湖北省', '湖南省', '廣東省', '廣西壯族自治區(qū)', '海南省', '重慶市', '四川省', '貴州省', '云南省', '西藏自治區(qū)', '陜西省', '甘肅省', '青海省', '寧夏回族自治區(qū)', '新疆維吾爾自治區(qū)', '臺(tái)灣省', '香港特別行政區(qū)', '澳門特別行政區(qū)'] 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)
結(jié)果如下:
表數(shù)據(jù)文件鏈接:
http://linuxdba.ltd/files/districts.sql 或者點(diǎn)擊這里本地下載
總結(jié)
到此這篇關(guān)于python獲取全國(guó)最新省市區(qū)數(shù)據(jù)并存入表的文章就介紹到這了,更多相關(guān)python獲取省市區(qū)數(shù)據(jù)內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!
版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。