"Python 3.6" python reads JSON data into MySQL (ii)

On the Internet to find a national and provincial latitude and longitude of the JSON file, you can also use the last method, parse the JSON keyword, construct SQL statement, insert the database.

The JSON file format is as follows:

[    {        "Name": "Beijing",        "Log": "116.46",        "Lat": "39.92",        "Children": [            { "Name": "Beijing", "Log": "116.46", "lat": "39.92" },            { "Name": "Pinggu", "Log": "117.1", "lat": "40.13" },            { "Name": "Miyun", "Log": "116.85", "lat": "40.37" },            { "Name": "Shunyi", "Log": "116.65", "lat": "40.13" },            { "Name": "Tongxian", "Log": "116.67", "lat": "39.92" },            { "Name": "Huairou", "Log": "116.62", "lat": "40.32" },            { "Name": "Daxing", "Log": "116.33", "lat": "39.73" },            { "Name": "Fangshan", "Log": "115.98", "lat": "39.72" },            { "Name": "Yanqing", "Log": "115.97", "lat": "40.47" },            { "Name": "Changping", "Log": "116.2", "lat": "40.22"}]},.................. {        "Name": "Hainan Province",        "Log": "110.35",        "Lat": "20.02",        "Children": [            { "Name": "Haikou", "Log": "110.35", "lat": "20.02" },            { "Name": "Qiongshan", "Log": "110.33", "lat": "19.98" },            { "Name": "Wenchang", "Log": "110.72", "lat": "19.61" },            { "Name": "Ting", "Log": "110.31", "lat": "19.68" },            { "Name": "Qionghai", "Log": "110.46", "lat": "19.25" },            { "Name": "Wanning", "Log": "110.39", "lat": "18.8" },            { "Name": "Tunchang", "Log": "110.1", "lat": "19.36" },            { "Name": "Chengmai", "Log": "A", "lat": "19.75" },            { "Name": "Dan County", "Log": "109.57", "lat": "19.52" },            { "Name": "Lingao", "Log": "109.69", "lat": "19.91" },            { "Name": "Baoting", "Log": "109.7", "lat": "18.64" },            { "Name": "White Sands", "Log": "109.44", "lat": "19.23" },            { "Name": "Qiongzhong", "Log": "109.83", "lat": "19.05" },            { "Name": "Lingshui", "Log": "110.02", "lat": "18.48" },            { "Name": "Cliff County", "Log": "109.5", "lat": "18.25" },            { "Name": "Ledong", "Log": "109.17", "lat": "18.73" },            { "Name": "Orient", "Log": "108.64", "lat": "19.09" },            { "Name": "Changjiang", "Log": "109.03", "lat": "19.25" }        ]    }]

First, you need to convert the JSON to dict and then remove the keywords from the JSON.

Considering the latitude and longitude of each province in the JSON, and nesting the latitude and longitude of the city in the province, it is planned to create the following table structure in the database:

"Province name" "Longitude" Dimension "City Name" "Longitude" dimension "

So, you need to merge the contents of the JSON, output a list, each element corresponds to the header above, and then take out each element as an input to the INSERT INTO statement.

Here's the code:

#Python 3.6#-*-coding:utf-8-*-__author__='Bh8ank'ImportJSONImportPymysqlconn=Pymysql.connect (Host='localhost',#MySQL server addressport=3306,#Port numberUser='Root',#User namePasswd='xxxxx',#Passworddb='Xdb',#Database namecharset='UTF8',#Connection code, fill in as needed) cur= Conn.cursor ()#Create and return cursors#create a header based on the contents of a fileSql_1 ="CREATE TABLE jingweidu (Prov varchar (+), log varchar (+), lat varchar (+), City VARCHAR (100), clog varchar, Clat VARCHAR (+));"#Cur.execute (Sql_1) #执行上述sql命令, first run, you need to execute the above statement to create the 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 =0 whileI <x:m=Tmp[i] E= [m['name'],m['Log'],m['lat']]    #print (E)j = Len (m['Children']) K=0 whileK <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)#execute the SQL command aboveK = k + 1Conn.commit ()Print("============") I= I+1conn.close ()

After execution, the effect is as follows:

"Python 3.6" python reads JSON data into MySQL (ii)