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

猜你喜欢

• Django如何实现自动发现路由，方法是什么

  这篇文章主要为大家介绍了Django自动发现所有路由的方法，具有一定的参考价值，感兴趣的小伙伴们可以参考一下，希望能够给你带来帮助<BR>

• Python教程：手把手学会使用isdigit()

  这篇文章主要介绍了Python中的内置函数isdigit()，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

• python怎样实现多线程，你知道哪一些方法？

  python中如何实现多线程？线程是指进程内的一个执行单元,也是进程内的可调度实体，这篇文章主要给大家介绍的是python 实现多线程的三种方法，对新手学习和理解多线程有一定参考价值，下面我们一起来看看吧。

• pivot函数是什么，具体用法是怎样的

  最近在做基于python的数据分析工作,引用第三方数据分析库pandas,所以下面这篇文章主要给大家介绍了关于pandas应用实例之pivot函数的相关资料,文中通过图文介绍的非常详细,需要的朋友可以参考下

• 怎么用Python从一个概率分布进行随机采样

  这篇文章主要介绍了通过几个机器学习中最常用的概率分布为例，来看看如何从一个概率分布中采样，文章中的代码对我们的工作或学习具有一定价值，感兴趣的朋友可以了解一下