这篇文章主要介绍“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进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• matlab中导出图片不清晰该怎么办

  写文章的时候有时需要matlab导出清晰的图片,如果直接用figure里面的保存的话不够清晰,下面这篇文章主要给大家介绍了关于如何从Matlab中导出清晰的结果图片的相关资料,需要的朋友可以参考下

• python实现链表倒转怎样做，代码是什么

  这篇文章主要为大家详细介绍了python递归实现链表快速倒转，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

• 用python批量移动文件的方法是什么？

  这篇文章给大家分享的是有关python如何批量移动文件的内容，小编觉得是比较实用的，下面就通过一个实例给大家介绍一下，感兴趣的朋友就继续往下看吧。

• Python如何对文件重命名，具体操作是什么

  这篇文章主要介绍了Python如何对文件进行重命名，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

• pyscript在网页怎样写Python程式，有什么要注意

  本文主要介绍了使用pyscript在网页中撰写Python程式的方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧