在网上找到一个包含全国各省市经纬度的json文件,也可以通过上次的办法,解析json关键字,构造SQL语句,插入数据库。
JSON文件格式如下:
[
{
"name": "北京市",
"log": "116.46",
"lat": "39.92",
"children": [
{ "name": "北京", "log": "116.46", "lat": "39.92" },
{ "name": "平谷", "log": "117.1", "lat": "40.13" },
{ "name": "密云", "log": "116.85", "lat": "40.37" },
{ "name": "顺义", "log": "116.65", "lat": "40.13" },
{ "name": "通县", "log": "116.67", "lat": "39.92" },
{ "name": "怀柔", "log": "116.62", "lat": "40.32" },
{ "name": "大兴", "log": "116.33", "lat": "39.73" },
{ "name": "房山", "log": "115.98", "lat": "39.72" },
{ "name": "延庆", "log": "115.97", "lat": "40.47" },
{ "name": "昌平", "log": "116.2", "lat": "40.22" }
]
},
……
……
……
{
"name": "海南省",
"log": "110.35",
"lat": "20.02",
"children": [
{ "name": "海口", "log": "110.35", "lat": "20.02" },
{ "name": "琼山", "log": "110.33", "lat": "19.98" },
{ "name": "文昌", "log": "110.72", "lat": "19.61" },
{ "name": "定安", "log": "110.31", "lat": "19.68" },
{ "name": "琼海", "log": "110.46", "lat": "19.25" },
{ "name": "万宁", "log": "110.39", "lat": "18.8" },
{ "name": "屯昌", "log": "110.1", "lat": "19.36" },
{ "name": "澄迈", "log": "110", "lat": "19.75" },
{ "name": "儋县", "log": "109.57", "lat": "19.52" },
{ "name": "临高", "log": "109.69", "lat": "19.91" },
{ "name": "保亭", "log": "109.7", "lat": "18.64" },
{ "name": "白沙", "log": "109.44", "lat": "19.23" },
{ "name": "琼中", "log": "109.83", "lat": "19.05" },
{ "name": "陵水", "log": "110.02", "lat": "18.48" },
{ "name": "崖县", "log": "109.5", "lat": "18.25" },
{ "name": "乐东", "log": "109.17", "lat": "18.73" },
{ "name": "东方", "log": "108.64", "lat": "19.09" },
{ "name": "昌江", "log": "109.03", "lat": "19.25" }
]
}]
首先,需要将json转为dict,然后取出json中的关键字。
考虑到json中每个省有经纬度,同时嵌套了省内城市的经纬度,因此这里计划在数据库中创建如下表结构:
【省名】【经度】【维度】【市名】【经度】【维度】
所以,需要将json中的内容整合拼接一下,输出一个list,每个元素对应上面的表头,然后取出每个元素,作为insert into语句的入参。
下面是代码:
#python 3.6
# -*- coding:utf-8 -*-
__author__ = 'BH8ANK'
import json
import pymysql
conn = pymysql.connect(
host='localhost', # mysql服务器地址
port=3306, # 端口号
user='root', # 用户名
passwd='xxxxx', # 密码
db='xdb', # 数据库名称
charset='utf8', # 连接编码,根据需要填写
)
cur = conn.cursor() # 创建并返回游标
# 根据文件内容创建表头
sql_1 = "CREATE TABLE jingweidu (prov VARCHAR(32),log VARCHAR(100),lat VARCHAR(100),city VARCHAR(100),clog VARCHAR(100),clat VARCHAR(100));"
#cur.execute(sql_1)#执行上述sql命令,首次运行时,需要执行上面的语句,用于创建table
a = open(r"D:\alldata.json", "r",encoding='UTF-8')
out = a.read()
tmp = json.dumps(out)
tmp = json.loads(out)
x = len(tmp)
# print(tmp)
# print(x)
i = 0
while i < x:
M = tmp[i]
E = [M['name'],M['log'],M['lat']]
# print(E)
j = len(M['children'])
k = 0
while k < j:
F = [M['children'][k]['name'],M['children'][k]['log'],M['children'][k]['lat'],]
H = E + F
# print(H[0])
sql_2 = "insert into jingweidu (prov,log,lat,city,clog,clat) values (" + "'"+H[0]+"'" +","+ "'"+H[1]+"'" + ","+"'"+H[2]+"'" + ","+"'"+H[3]+"'" + ","+"'"+H[4]+"'" + ","+"'"+H[5]+"'" + ");"
print(sql_2)
cur.execute(sql_2) # 执行上述sql命令
k = k + 1
conn.commit()
print("============")
i = i+1
conn.close()
执行完成后效果如下图: