同上一篇,只是适配 CentOS+ python 2.7
#python 2.7
# -*- coding:utf-8 -*-
__author__ = 'BH8ANK'
import json
import pymysql
conn = pymysql.connect(
host = '10.0.0.14',#mysql服务器地址
port = 3306,#端口号
user = 'root',#用户名
passwd = 'xxxxoooo',#密码
db = 'xxxxx',#数据库名称
charset = 'utf8',#连接编码,根据需要填写
)
cur = conn.cursor()#创建并返回游标
#根据文件内容创建表头
sql = "CREATE TABLE daxue_info (codea VARCHAR(32),charge VARCHAR(100),level VARCHAR(100),name VARCHAR(100),remark VARCHAR(100),prov VARCHAR(100));"
cur.execute(sql)#执行上述sql命令
a = open(r"/root/alldata.json", "r")
out = a.read()
tmp = json.dumps(out)
tmp = json.loads(out)
num = len(tmp)
i = 0
while i < num:
codea = tmp[i]['codea']
charge = tmp[i]['charge']
level = tmp[i]['level']
name = tmp[i]['name']
remark = tmp[i]['remark']
prov = tmp[i]['prov']
value = [codea,charge,level,name,remark,prov]
sql_insert = "insert into daxue_info (codea,charge,level,name,remark,prov) values (" + "'"+codea+"'" +","+ "'"+charge+"'" + ","+"'"+level+"'" + ","+"'"+name+"'" + ","+"'"+remark+"'" + ","+"'"+prov+"'" + ");"
# sql_insert =("insert into daxue (code,charge,level,name,remark,prov) values (%s,%s,%s,%s,%s,%s);",value)
# sql_insert = sql_insert.encode("utf8")
#print(sql_insert)
cur.execute(sql_insert) # 执行上述sql命令
i = i+1
# print(num)
conn.commit()
conn.close()
json文件格式为:
[{
"codea": "4111010001",
"charge": "教育部",
"level": "本科",
"name": "北京大学",
"location": "北京市",
"remark": "",
"prov": "北京市"
}, {
"codea": "4111010002",
"charge": "教育部",
"level": "本科",
"name": "中国人民大学",
"location": "北京市",
"remark": "",
"prov": "北京市"
}, {
"codea": "4111010003",
"charge": "教育部",
"level": "本科",
"name": "清华大学",
"location": "北京市",
"remark": "",
"prov": "北京市"
}, {
"codea": "4111010004",
"charge": "教育部",
"level": "本科",
"name": "北京交通大学",
"location": "北京市",
"remark": "",
"prov": "北京市"
}, {
"codea": "4111010005",
"charge": "北京市",
"level": "本科",
"name": "北京工业大学",
"location": "北京市",
"remark": "",
"prov": "北京市"
}
]