这篇文章主要介绍“python向mysql中存储JSON的实现是怎样的”，有一些人在python向mysql中存储JSON的实现是怎样的的问题上存在疑惑，接下来小编就给大家来介绍一下相关的内容，希望对大家解答有帮助，有这个方面学习需要的朋友就继续往下看吧。

虽然把JSON数据存入mysql也是比较蛋疼，但是相比使用Nodejs嵌套处理多个mysql查询并拼接返回数据也算是没mongo时的一个折中方案了。

我使用python拼接了一个json格式的字符串，却遇到了一些问题

1，如果把json数据转成str存入，那么nodejs获取数据的时候就无法使用json格式了

处理方法就是

import json
data = json.dumps(data_dict, ensure_ascii=False)

通过dumps就可以把python的字典转化成JSON

转码后的JSON数据如下，可以到http://www.bejson.com/ 去验证JSON格式是否正确

{"tongji1": [{"sum_profit": 6032, "counts": 15, "win_counts": 8, "span": "09:15:00"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "09:45:00"}, {"sum_profit": 1542, "counts": 1, "win_counts": 1, "span": "10:15:00"}, {"sum_profit": 3084, "counts": 2, "win_counts": 2, "span": "10:45:00"}, {"sum_profit": 1122, "counts": 1, "win_counts": 1, "span": "11:15:00"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "13:15:00"}, {"sum_profit": -738, "counts": 1, "win_counts": 0, "span": "13:45:00"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "14:15:00"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "14:45:00"}], "tongji2": [{"sum_profit": 11042, "counts": 20, "win_counts": 12, "span": "1"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "16"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "31"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "6"}, {"sum_profit": 0, "counts": 0, "win_counts": 0, "span": "61"}], "tongji345": {"avg_lose": 8, "avg_win_span": 0, "avg_win": 1907, "avg_lose_span": 0, "avg_max_lose_day": 389, "avg_max_win_day": 813, "avg_trade_counts": 1}}

 

2，MySQLdb插入数据时的一些注意事项

sql = """insert into trades_info_f (id, data) values ('%s', '%s')""" % (id,data)

如上代码，使用""" """避免了JSON串的内部转义双引号，这样就可以存入数据库了

 

3，在Nodejs端获取json数据

exports.tongji = (req, res) ->
  openid = req.query.id

  sql = "SELECT data from trades_info_f WHERE id = '" + id + "'" 
  console.log sql 

  mysqldb.query sql, (err, rows, fields) ->
    console.log err if err 

    console.log rows
    console.log rows[0].data
    data = JSON.parse(rows[0].data)
    return res.jsonp {'status':0, 'message':'ok', 'data':data}

通过如下语句获取的数据rows需要进一步的处理

如下的是原始数据

[ { data: '{"tongji1": [{"sum_profit": 42174, "counts": 784, "win_counts": 398, "span": "09:15:00"}, {"sum_profit": 14647, "counts": 757, "win_counts": 377, "span": "09:45:00"}, {"sum_profit": 51188, "counts": 757, "win_counts": 375, "span": "10:15:00"}, {"sum_profit": 72475, "counts": 771, "win_counts": 409, "span": "10:45:00"}, {"sum_profit": 4820, "counts": 689, "win_counts": 338, "span": "11:15:00"}, {"sum_profit": 57657, "counts": 691, "win_counts": 346, "span": "13:15:00"}, {"sum_profit": 73766, "counts": 718, "win_counts": 388, "span": "13:45:00"}, {"sum_profit": 267, "counts": 681, "win_counts": 327, "span": "14:15:00"}, {"sum_profit": 12207, "counts": 582, "win_counts": 303, "span": "14:45:00"}], "tongji2": [{"sum_profit": 469066, "counts": 5528, "win_counts": 2807, "span": "1"}, {"sum_profit": -150245, "counts": 142, "win_counts": 45, "span": "16"}, {"sum_profit": -51352, "counts": 19, "win_counts": 5, "span": "31"}, {"sum_profit": -113061, "counts": 1452, "win_counts": 751, "span": "6"}, {"sum_profit": -23535, "counts": 107, "win_counts": 48, "span": "61"}], "tongji345": {"avg_lose": 3592, "avg_win_span": 4, "avg_win": 625, "avg_lose_span": 4, "avg_max_lose_day": -2760, "avg_max_win_day": 1977, "avg_trade_counts": 41}}' } ]

rows[0].data就获得了所需的str数据，之后使用JSON.parse()转换为JSON数据

{"tongji1": [{"sum_profit": 42174, "counts": 784, "win_counts": 398, "span": "09:15:00"}, {"sum_profit": 14647, "counts": 757, "win_counts": 377, "span": "09:45:00"}, {"sum_profit": 51188, "counts": 757, "win_counts": 375, "span": "10:15:00"}, {"sum_profit": 72475, "counts": 771, "win_counts": 409, "span": "10:45:00"}, {"sum_profit": 4820, "counts": 689, "win_counts": 338, "span": "11:15:00"}, {"sum_profit": 57657, "counts": 691, "win_counts": 346, "span": "13:15:00"}, {"sum_profit": 73766, "counts": 718, "win_counts": 388, "span": "13:45:00"}, {"sum_profit": 267, "counts": 681, "win_counts": 327, "span": "14:15:00"}, {"sum_profit": 12207, "counts": 582, "win_counts": 303, "span": "14:45:00"}], "tongji2": [{"sum_profit": 469066, "counts": 5528, "win_counts": 2807, "span": "1"}, {"sum_profit": -150245, "counts": 142, "win_counts": 45, "span": "16"}, {"sum_profit": -51352, "counts": 19, "win_counts": 5, "span": "31"}, {"sum_profit": -113061, "counts": 1452, "win_counts": 751, "span": "6"}, {"sum_profit": -23535, "counts": 107, "win_counts": 48, "span": "61"}], "tongji345": {"avg_lose": 3592, "avg_win_span": 4, "avg_win": 625, "avg_lose_span": 4, "avg_max_lose_day": -2760, "avg_max_win_day": 1977, "avg_trade_counts": 41}}

 OK，打开Postman，格式正确了

 

以上就是关于“python向mysql中存储JSON的实现是怎样的”的介绍了，感谢各位的阅读，希望这篇文章能帮助大家解决问题。如果想要了解更多知识，欢迎关注群英网络，小编每天都会为大家更新不同的知识。

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• Python中如何导入和应用zipfile模块对文件压缩

  本文主要介绍了Python实现批量压缩文件/文件夹zipfile的使用，文中通过示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

• Python Pycharm如何下载安装，有哪些使用技巧

  本文详细讲解了Python集成开发环境Pycharm的使用及技巧，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

• python解析之namedtuple函数的用法

  本篇文章给大家带来了关于Python的相关知识，主要介绍了python中namedtuple函数的用法解析，文章围绕主题展开详细的内容介绍，具有一定的参考价值，感兴趣的小伙伴可以参考一下。

• Python中的Plotly的应用有什么，具体怎么使用的

  对于Python语言来说，比较传统的数据可视化模块是Matplotlib，但它存在不够美观、静态性、不易分享等缺点，限制了Python在数据可视化方面的发展。为了解决这个问题，新型的动态可视化开源模块Plotly应运而生。本文将为大家详细介绍Plotly的用法，需要的可以参考一下

• Python里的包内导入的注意点有哪些

  这篇文章主要介绍了python中的单下划线与双下划线以及绝对导入与相对导入说明，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教