【python 3.6】python讀取json資料存入MySQL（二）

標籤：style   運行   檔案內容   port   children   color   平穀   print   exec   

在網上找到一個包含全國各省市經緯度的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 jsonimport pymysqlconn = 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命令，首次運行時，需要執行上面的語句，用於建立tablea = 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 = 0while 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+1conn.close()

 

 

 

執行完成後效果如：

【python 3.6】python讀取json資料存入MySQL（二）