整体思路:
1,读取json文件
2,将数据格式化为dict,取出key,创建数据库表头
3,取出dict的value,组装成sql语句,循环执行
4,执行SQL语句
#python 3.6 # -*- coding:utf-8 -*- __author__ = 'BH8ANK' import json import pymysql conn = pymysql.connect( host = 'localhost',#mysql服务器地址 port = 3306,#端口号 user = 'root',#用户名 passwd = 'xxxxxx',#密码 db = 'xdb',#数据库名称 charset = 'utf8',#连接编码,根据需要填写 ) cur = conn.cursor()#创建并返回游标 #创建表头 sql = "CREATE TABLE daxue (code VARCHAR(32),charge VARCHAR(100),level VARCHAR(100),name VARCHAR(100),remark VARCHAR(100),prov VARCHAR(100));" cur.execute(sql)#执行上述sql命令 a = open(r"D:\alldata.json", "r",encoding='UTF-8') out = a.read() tmp = json.dumps(out) tmp = json.loads(out) num = len(tmp) i = 0 while i < num: code = tmp[i]['code'] charge = tmp[i]['charge'] level = tmp[i]['level'] name = tmp[i]['name'] remark = tmp[i]['remark'] prov = tmp[i]['prov'] value = [code,charge,level,name,remark,prov] sql_insert = "insert into daxue (code,charge,level,name,remark,prov) values (" + "'"+code+"'" +","+ "'"+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()
执行后效果
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:【python 3.6】python读取json数据存入MySQL(一) - Python技术站