python将json文件写入mysql

作者: 果果小姚 | 来源:发表于2020-07-02 10:47 被阅读0次

python将json文件写入mysql
python读取json
python3写入txt文件，中文乱码的问题
nodeJS读取json文件并写入txt或redis中
python写入json文件
搜企网爬虫作业
python作业-20170601
验证将csv写入数据库的程序
python中将字典转换为json文件
2018-07-25

公司之前产品的数据集需要手工导入，着实麻烦，需要用idea客户端用transport的方式导入。数据是json的格式

现在新实现集成了数据连接和数据集部分，这样就可以从mysql，oracle，postgressql等数据库中进行数据导入了。

比较费劲的地方是json类型格式比较复杂，需要覆盖的类型也比较多，在导入mysql时麻烦些。

使用的是jupyter，进行调试，需要先安装python3，然后直接pip进行安装jupyter就行

在cmd情况下，输入jupyter notebook，界面就可以调试了

json数据如下：一条很长，里面有基本类型，也覆盖了数据和嵌套类型

{"centerlat":"36.7","c_uuid":"914e674f-b3d1-4650-b916-9a9432f9a757","centerlong":"119.15","c_month_z": "12月", "c_month_e": "July", "c_province": "辽宁省", "c_province_lat_float": 40.9975197, "c_province_long_float": 122.9955469, "c_province_lat_str": "40.9975197", "c_province_long_str": "122.9955469", "c_city_suffix": "自治县", "c_suffix": "传媒有限公司", "c_am_pm": "PM", "c_phone_prefix": 186, "c_int": 888888, "c_long": 2018080100000966, "c_boolean": true, "c_text": "今天游戏个人发现.\n方面当前回复我的图片当前.不会提供一点.\n经济如此是否.工具显示用户设计功能由于不断.还有目前目前认为.\n关于程序汽车.\n一下推荐活动报告发现设备看到.服务标题如果他们搜索对于.\n经济回复其中情况但是一种.质量法律社会方面孩子行业.\n提供成功文章市场欢迎次数工作.过程当然主要最后基本朋友学校.可以或者处理不能次数更新.免费继续男人基本学习.", "c_date_time": "1980-07-08 12:24:19", "c_float": 8.888888, "c_double": 1.563625140598217, "a_string": ["07月", "July", "广西壮族自治区", "传媒有限公司", "PM"], "a_int": [5520, 1755, 9946], "a_boolean": [false, true, false], "a_long": [2018080100008286, 2018080100009688, 2018080100002045], "a_float": [-9.936871, 9.200286, 0.227174], "a_double": [0.948297853565107, 4.387693154988151, 7.837379360507783], "a_datetime": ["2012-10-15 22:51:14", "1992-08-31 00:35:26", "1986-12-09 04:22:25"], "a_string_x": ["08月", "07月", "04月", "05月", "03月", "February", "December", "June", "May", "February", "湖南省", "云南省", "西藏自治区", "陕西省", "西藏自治区", "网络有限公司", "网络有限公司", "信息有限公司", "信息有限公司", "科技有限公司", "PM", "PM", "PM", "PM", "PM"], "a_int_x": [8213, 5411, 4780, 1097, 3152, 8346, 5214, 2980, 9402, 6376, 73, 3217, 9447, 2519, 9497, 9462, 7484, 1183, 3740, 3622, 6836, 3490, 5658, 9821, 1414, 2810, 3655, 2052, 4547, 9179, 2921, 4317, 276, 4959, 7332], "a_boolean_x": [true, false, false, false, true, true, false, true, false, false, true, false, false, false, false, false, true, false, true, false, false, true, false, true, false, true, true, false, false, true], "a_long_x": [2018080100000618, 2018080100000192, 2018080100004174, 2018080100000438, 2018080100007279, 2018080100002698, 2018080100008121, 2018080100007587, 2018080100005749, 2018080100002189, 2018080100007644, 2018080100005060, 2018080100007225, 2018080100001408, 2018080100008636], "a_float_x": [-6.518344, 8.64907, 3.163606, -6.859527, 1.917594, 7.104616, -4.137235, 8.795582, -7.3253, 4.734343, 7.92539, 4.679045, -1.454179, -1.71782], "a_double_x": [1.973857836352327, 7.566745946916848, -1.13668647252004, 4.216995855720113, 3.701736841818147, 2.693629017280139, -9.749026781233644, 2.45982713302184, -4.292622725494931, -6.187975835974211, 9.78922588463684, -5.789005326877345, -1.68020969703246, -8.504570803301425], "a_datetime_x": ["1978-02-20 01:23:19", "1983-05-18 01:43:16", "1999-11-07 13:05:36", "2007-11-12 08:30:13", "1981-02-03 18:49:06", "1996-02-09 10:45:19", "2018-06-26 14:20:32", "1975-10-07 18:08:59", "2014-02-02 01:34:13", "1993-08-01 11:50:39", "2003-04-06 13:59:34", "2012-03-04 23:56:24", "1985-05-16 21:16:33", "2005-01-14 15:14:12", "2014-10-20 00:16:47", "2001-01-04 14:58:31", "1984-09-15 16:48:09", "2000-06-13 18:56:14", "2008-10-28 06:10:59", "2013-08-16 23:35:48", "1998-06-02 23:44:43", "1970-08-11 20:02:22", "1988-12-08 14:31:30", "2016-04-28 08:48:32", "1989-12-02 14:30:18"], "a_zero": [], "c_nested": [{"n_month_z": "03月", "n_month_e": "February", "n_province": "浙江省", "n_suffix": "网络有限公司", "n_am_pm": "PM", "n_phone_prefix": 138, "n_int": 7016, "n_boolean": false, "n_long": 2018080100009536, "n_string": "进入情况这里密码一直使用.出来根据感觉有些或者以下.类别地址以下一定系列发现.\n的是联系合作出来可以一直.单位那些大小决定.\n帮助浏览软件最后.如此不要很多.\n看到中文有关根据是否发布最新.评论有关为什分析一定规定.社区帮助地方上海.\n威望希望这里系统关系东西加入.继续就是全国其他不是东西.他们公司如此如何是一.由于大家我的操作产品.\n人员大学正在之后您的.建设生产那个以及北京学生支持已经.", "n_float": 0.258646, "n_double": 4.91129193319022, "n_date": "1970-01-28", "n_datetime": "1982-08-17 20:09:13"}, {"n_month_z": "06月", "n_month_e": "January", "n_province": "吉林省", "n_suffix": "科技有限公司", "n_am_pm": "AM", "n_phone_prefix": 136, "n_int": 4032, "n_boolean": true, "n_long": 2018080100006603, "n_string": "介绍设备责任文件组织基本服务知道.系列游戏经营北京开始搜索搜索.如此中心规定首页还是留言全国.\n内容注意过程位置希望都是实现.浏览看到美国.帖子北京价格认为工程当前.\n名称一直地方方面中国不会她的政府.问题功能应用其实.\n更新是一设备广告中国文件这些操作.相关无法以后准备发表已经方法.这种这样用户基本原因行业.\n必须参加问题状态只要.", "n_float": 9.939012, "n_double": -6.903178820786101, "n_date": "2019-02-15", "n_datetime": "1982-11-06 06:11:37"}, {"n_month_z": "08月", "n_month_e": "October", "n_province": "吉林省", "n_suffix": "信息有限公司", "n_am_pm": "AM", "n_phone_prefix": 137, "n_int": 1800, "n_boolean": true, "n_long": 2018080100001744, "n_string": "历史两个电子你们来源.质量控制科技.\n地址的话项目说明企业显示空间.或者文件市场其他如此阅读公司.\n现在今天如果.\n学习简介完成其实已经.社区地址有些的人发现电话.\n以及经验直接有些程序看到.应用新闻语言回复人民增加选择.\n经济世界用户得到.活动无法市场的是论坛.\n日期图片得到中心能够.他们政府同时那些只有.\n那么大学有限问题处理经营公司.电脑相关处理自己.", "n_float": 2.282995, "n_double": 0.832995965797904, "n_date": "1979-01-28", "n_datetime": "1972-04-13 18:58:02"}]}

python中使用pymyql进行数据库的连接

import json
import pymysql
#mysql的数据库连接信息
conn = pymysql.connect(
    #服务器地址
        host = '10.6.6.38',
    #端口号
        port = 3307,
    #用户名
        user = 'root',
    #密码
        passwd = 'xdata123',
    #数据库名称
        db = 'dpltest',
    #字符编码格式
        charset = 'utf8',
    )
#设置游标，进行数据库操作
cur = conn.cursor()

连接完成后，需要进行表的创建，不得不说，这个表着实。。

#sql = "CREATE TABLE view63_tbl12(centerlat decimal(10,7) DEFAULT NULL,c_uuid VARCHAR(36) DEFAULT NULL ,centerlong decimal(10,7) DEFAULT NULL,c_month_z varchar(6) DEFAULT NULL,c_month_e varchar(15) DEFAULT NULL,c_province varchar(25) DEFAULT NULL, c_province_lat_float float(15,7) DEFAULT NULL,c_province_long_float float(20,10) DEFAULT NULL,c_province_lat_str varchar(15) DEFAULT NULL,c_province_long_str varchar(20) DEFAULT NULL,c_city_suffix VARCHAR(30) DEFAULT NULL ,c_suffix VARCHAR(30) DEFAULT NULL ,c_am_pm VARCHAR(10) DEFAULT NULL,c_phone_prefix VARCHAR(15) DEFAULT NULL,c_int int(11) DEFAULT NULL ,c_long bigint(40) DEFAULT NULL ,c_boolean tinyint(1) DEFAULT NULL, c_text text,c_date_time datetime(3),c_float float(10,7) DEFAULT NULL ,c_double double(20,18) DEFAULT NULL ,a_string varchar(1024) DEFAULT NULL ,a_int varchar(1024) DEFAULT NULL ,a_boolean varchar(1024) DEFAULT NULL ,a_long varchar(1024) DEFAULT NULL ,a_float varchar(1024) DEFAULT NULL ,a_double varchar(1024) DEFAULT NULL ,a_datetime varchar(1024) DEFAULT NULL ,a_string_x varchar(1024) DEFAULT NULL ,a_int_x varchar(1024) DEFAULT NULL ,a_boolean_x varchar(1024) DEFAULT NULL ,a_long_x varchar(1024) DEFAULT NULL ,a_float_x varchar(1024) DEFAULT NULL ,a_double_x varchar(1024) DEFAULT NULL ,a_datetime_x varchar(1024) DEFAULT NULL ,a_zero varchar(1024) DEFAULT NULL,c_nested varchar(1024) DEFAULT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8;"

mysql中语句如下：


 CREATE TABLE IF NOT EXISTS `view63_tbl`(

   `centerlat` decimal(10,7) DEFAULT NULL,

   `c_uuid` VARCHAR(36) DEFAULT NULL ,

   `centerlong` decimal(10,7) DEFAULT NULL,

   `c_month_z`  varchar(6) DEFAULT NULL,

   `c_month_e` varchar(15) DEFAULT NULL,

   `c_province` varchar(25) DEFAULT NULL,

   `c_province_lat_float` float(15,7) DEFAULT NULL,

   `c_province_long_float` float(20,10) DEFAULT NULL,

   `c_province_lat_str` varchar(15) DEFAULT NULL,

   `c_province_long_str` varchar(20) DEFAULT NULL,

   `c_city_suffix` VARCHAR(30)  DEFAULT NULL ,

   `c_suffix` VARCHAR(30)  DEFAULT NULL ,

   `c_am_pm` VARCHAR(10)  DEFAULT NULL,

   `c_phone_prefix` VARCHAR(15)  DEFAULT NULL,

   `c_int` int(11) DEFAULT NULL ,

   `c_long` bigint(40) DEFAULT NULL ,

   `c_boolean` tinyint(1) DEFAULT NULL,

   `c_text` text,

   `c_date_time` datetime(3),

   `c_float` float(10,7) DEFAULT NULL ,

   `c_double` double(20,18) DEFAULT NULL ,

   `a_string` varchar(1024) DEFAULT NULL ,

   `a_int` varchar(1024) DEFAULT NULL ,

   `a_boolean` varchar(1024) DEFAULT NULL  ,

   `a_long` varchar(1024) DEFAULT NULL ,

   `a_float` varchar(1024) DEFAULT NULL ,

   `a_double` varchar(1024) DEFAULT NULL  ,

   `a_datetime` varchar(1024) DEFAULT NULL ,

   `a_string_x` varchar(1024) DEFAULT NULL ,

   `a_int_x` varchar(1024) DEFAULT NULL ,

   `a_boolean_x` varchar(1024) DEFAULT NULL ,

   `a_long_x` varchar(1024) DEFAULT NULL ,

   `a_float_x` varchar(1024) DEFAULT NULL ,

   `a_double_x` varchar(1024) DEFAULT NULL ,

   `a_datetime_x`varchar(1024) DEFAULT NULL ,

   `a_zero` varchar(1024) DEFAULT NULL ,

   `c_nested` varchar(1024) DEFAULT NULL

import json
import pymysql
#mysql的数据库连接信息
conn = pymysql.connect(
    #服务器地址
        host = '10.6.6.38',
    #端口号
        port = 3307,
    #用户名
        user = 'root',
    #密码
        passwd = 'xdata123',
    #数据库名称
        db = 'dpltest',
    #字符编码格式
        charset = 'utf8',
    )
#设置游标，进行数据库操作
cur = conn.cursor()
#创建表语句
#sql = "CREATE TABLE view63_tbl12(centerlat decimal(10,7) DEFAULT NULL,c_uuid VARCHAR(36) DEFAULT NULL ,centerlong decimal(10,7) DEFAULT NULL,c_month_z  varchar(6) DEFAULT NULL,c_month_e varchar(15) DEFAULT NULL,c_province varchar(25) DEFAULT NULL, c_province_lat_float float(15,7) DEFAULT NULL,c_province_long_float float(20,10) DEFAULT NULL,c_province_lat_str varchar(15) DEFAULT NULL,c_province_long_str varchar(20) DEFAULT NULL,c_city_suffix VARCHAR(30)  DEFAULT NULL ,c_suffix VARCHAR(30)  DEFAULT NULL ,c_am_pm VARCHAR(10)  DEFAULT NULL,c_phone_prefix VARCHAR(15)  DEFAULT NULL,c_int int(11) DEFAULT NULL ,c_long bigint(40) DEFAULT NULL ,c_boolean tinyint(1) DEFAULT NULL, c_text text,c_date_time datetime(3),c_float float(10,7) DEFAULT NULL ,c_double double(20,18) DEFAULT NULL ,a_string varchar(1024) DEFAULT NULL ,a_int varchar(1024) DEFAULT NULL ,a_boolean varchar(1024) DEFAULT NULL  ,a_long varchar(1024) DEFAULT NULL ,a_float varchar(1024) DEFAULT NULL ,a_double varchar(1024) DEFAULT NULL  ,a_datetime varchar(1024) DEFAULT NULL ,a_string_x varchar(1024) DEFAULT NULL ,a_int_x varchar(1024) DEFAULT NULL ,a_boolean_x varchar(1024) DEFAULT NULL ,a_long_x varchar(1024) DEFAULT NULL ,a_float_x varchar(1024) DEFAULT NULL ,a_double_x varchar(1024) DEFAULT NULL ,a_datetime_x varchar(1024) DEFAULT NULL ,a_zero varchar(1024) DEFAULT NULL,c_nested varchar(1024) DEFAULT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8;"
#cur.execute(sql)

使用有游标执行sql语句可完成cur.execute(sql)，mysql中的表结构需要跟json中的表结构一致才可以，也就是json中的key的值

创建好表以后，需要往表里面导入数据了，怎么导入呢，通过json文件方式进行导入

#打开文件
#设置读取的路径
path1 = "D:/work/python/aus_dpl_v62_td.json"
with open(path1, 'r', encoding='utf_8_sig') as f:

    ln=0

    #逐行读取

    for line in f.readlines():

        ln +=1

需要将读过来的数据转化成字典进行存储， dic = json.loads(line)

  #以，进行key的拼接，对应json中的字段

        keys = ', '.join(dic.keys())

json数据里面有些是list形式，这种无法导入，需要将其转化成str形式，才能导入

 # value=dic.values()
        # 循环 dic.values() 如果类型是list或dict 转为str 
        #print(type(dic.values()))
        print(keys)
        #将字典value信息存储成list
        valuesList = [dici for dici in dic.values()]
        #进行List列表的遍历
        for index1, value1 in enumerate(valuesList):
#             print(type(value1), value1)
        #如果是list,将其以str类型存储
            if isinstance(value1, list):
               # print(value1)
                valuesList[index1] = str(value1)

思路是将values进行类型循环，真个values的list进行遍历，如果是list类型，就将其转化为str存储
然后将其转化为元组形式存储
将value的值用，进行拼接，用于插入数据用

#将str类型转化成元组形式存储
        valuesTuple = tuple(valuesList)
#         print((valuesTuple))
       # strdata=json.dumps(data)
    #将value值进行拼接
        values = ', '.join(['%s']*len(dic))

进行插入数据的执行

table="view63_tbl"
        #构造sql插入语句
        sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)  
#         print(sql)
        #执行sql语句
        cur.execute(sql,valuesTuple)
        conn.commit()
conn.close()

整体代码如下：

import json
import pymysql
#mysql的数据库连接信息
conn = pymysql.connect(
    #服务器地址
        host = '10.6.6.38',
    #端口号
        port = 3307,
    #用户名
        user = 'root',
    #密码
        passwd = 'xdata123',
    #数据库名称
        db = 'dpltest',
    #字符编码格式
        charset = 'utf8',
    )
#设置游标，进行数据库操作
cur = conn.cursor()
#创建表语句
#sql = "CREATE TABLE view63_tbl12(centerlat decimal(10,7) DEFAULT NULL,c_uuid VARCHAR(36) DEFAULT NULL ,centerlong decimal(10,7) DEFAULT NULL,c_month_z  varchar(6) DEFAULT NULL,c_month_e varchar(15) DEFAULT NULL,c_province varchar(25) DEFAULT NULL, c_province_lat_float float(15,7) DEFAULT NULL,c_province_long_float float(20,10) DEFAULT NULL,c_province_lat_str varchar(15) DEFAULT NULL,c_province_long_str varchar(20) DEFAULT NULL,c_city_suffix VARCHAR(30)  DEFAULT NULL ,c_suffix VARCHAR(30)  DEFAULT NULL ,c_am_pm VARCHAR(10)  DEFAULT NULL,c_phone_prefix VARCHAR(15)  DEFAULT NULL,c_int int(11) DEFAULT NULL ,c_long bigint(40) DEFAULT NULL ,c_boolean tinyint(1) DEFAULT NULL, c_text text,c_date_time datetime(3),c_float float(10,7) DEFAULT NULL ,c_double double(20,18) DEFAULT NULL ,a_string varchar(1024) DEFAULT NULL ,a_int varchar(1024) DEFAULT NULL ,a_boolean varchar(1024) DEFAULT NULL  ,a_long varchar(1024) DEFAULT NULL ,a_float varchar(1024) DEFAULT NULL ,a_double varchar(1024) DEFAULT NULL  ,a_datetime varchar(1024) DEFAULT NULL ,a_string_x varchar(1024) DEFAULT NULL ,a_int_x varchar(1024) DEFAULT NULL ,a_boolean_x varchar(1024) DEFAULT NULL ,a_long_x varchar(1024) DEFAULT NULL ,a_float_x varchar(1024) DEFAULT NULL ,a_double_x varchar(1024) DEFAULT NULL ,a_datetime_x varchar(1024) DEFAULT NULL ,a_zero varchar(1024) DEFAULT NULL,c_nested varchar(1024) DEFAULT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8;"
#cur.execute(sql)
#json文件读取路径
path1 = "D:/work/python/aus_dpl_v62_td.json"
#打开文件
with open(path1, 'r', encoding='utf_8_sig') as f:
    ln=0
    #逐行读取
    for line in f.readlines():
        ln +=1
        #将数据转化为字典形式存储
        dic = json.loads(line)
        #keys=dic.keys()
        #以，进行key的拼接，对应json中的字段
        keys = ', '.join(dic.keys())
       # value=dic.values()
        # 循环 dic.values() 如果类型是list或dict 转为str 
        #print(type(dic.values()))
        print(keys)
        #将字典value信息存储成list
        valuesList = [dici for dici in dic.values()]
        #进行List列表的遍历
        for index1, value1 in enumerate(valuesList):
#             print(type(value1), value1)
        #如果是list,将其以str类型存储
            if isinstance(value1, list):
               # print(value1)
                valuesList[index1] = str(value1)
    #将str类型转化成元组形式存储
        valuesTuple = tuple(valuesList)
#         print((valuesTuple))
       # strdata=json.dumps(data)
    #将value值进行拼接
        values = ', '.join(['%s']*len(dic)) 
       # print(values)
    #设置表名称
        table="view63_tbl"
        #构造sql插入语句
        sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)  
#         print(sql)
        #执行sql语句
        cur.execute(sql,valuesTuple)
        conn.commit()
conn.close()

导入界面如下

image.png

python将json文件写入mysql
公司之前产品的数据集需要手工导入，着实麻烦，需要用idea客户端用transport的方式导入。数据是json的格...
python读取json
python操作json文件通过了两种方法：load()：用于读取json文件；dump()：用于写入json文件...
python3写入txt文件，中文乱码的问题
需求：将json写入txt文件，问题：中文乱码1.转json的时候，关闭ascii编码格式 2.写入文件
nodeJS读取json文件并写入txt或redis中
【1：读取json写入txt文件】json文件book.json js文件【2：读取json写入redis文件/...
python写入json文件
想要多条相同key的数据添加json中，先将数据存入到字典中，再append到列表中。最后存入json中。这样子...
搜企网爬虫作业
作业要求（1）csv文件数据写入（2）mysql 操作，python mysql操作这个需要安装mysql以及...
python作业-20170601
作业：（1）csv文件数据写入（2）mysql 操作，python mysql操作这个需要安装mysql以及p...
验证将csv写入数据库的程序
前两天的文章《利用python将csv文件写入数据库》记录了用python将多日的告警csv文件写入sqlite数...
python中将字典转换为json文件
python将字典转换为json文件
2018-07-25
关于Python2.7里把中文写入json文件 1.设置文件格式2.设置数据编码

python将json文件写入mysql

相关文章

python将json文件写入mysql

python读取json

python3写入txt文件，中文乱码的问题

nodeJS读取json文件并写入txt或redis中

python写入json文件

搜企网爬虫作业

python作业-20170601

验证将csv写入数据库的程序

python中将字典转换为json文件

2018-07-25

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读