将json文件转换为MySQL数据

问题

如何将json文件转换为MySQL数据

解决方案

将json数据转换成insert语句，再将sql文件导入至MySQL数据库

import json

data = []
with open('BaseCourseInfo.json',encoding='utf-8') as f:
    for line in f:
        data.append(json.loads(line))

import datetime
import random
str = "\r\n"
for item in data:
    #print json.dumps(item)

    # if('_class' not in item):
    #
    #     str = str + "('%s','%s','%s','%s');\r\n" % (item['_id'], item['_name'], tt, item['_identify'])
    # else:
    # timeStamp = int(item['_class_time']['$date']['$numberLong'])
    # dateArray = datetime.datetime.fromtimestamp(timeStamp/1000, pytz.timezone('UTC'))
    # otherStyleTime = dateArray.strftime("%Y-%m-%d %H:%M:%S")
    # print(otherStyleTime)
    for i in item['_stu_set']:
        str = str + "insert into basestudents(base_course_id,stu_id,grade) values "
        str = str + "('%s','%s', '%s');\r\n" % (item['_id']['$oid'],i['_id'],random.randint(0,100))

import codecs
file_object = codecs.open('tencent.sql', 'w' ,"utf-8")
file_object.write(str)
file_object.close()
print("success")

操作之后，所有数据类型都是字符型。如果数据库中某字段是数字型，可以先将其设置为字符型，待数据导入后再恢复成数字型