这篇文章主要介绍“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 PIL图片裁剪功能怎样做，代码是什么

  这篇文章主要介绍了Python PIL图片如何按比例裁剪，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

• BeautifulSoup4模块是什么，如何安装使用

  本文主要介绍了python beautifulsoup4模块详情,BeautifulSoup4是一款python解析库,主要用于解析HTML和XML,在爬虫知识体系中解析 HTML 会比较多一些，下文更多相关内容，需要的小伙伴可以参考一下

• 如何用Python实现表情包生成文字，思路及代码是什么

  这篇文章主要介绍了python实战之用emoji表情生成文字,文中有非常详细的代码示例,对正在学习python的小伙伴们有很好地帮助,需要的朋友可以参考下

• Python中yield的作用是什么，使用写法是怎样

  yield的作用返回一个可以用来迭代（for循环）的生成器，它的应用场景通常为一个需要返回一系列值的，含有循环的函数中。

• python运算符重载是什么意思？怎样实现？

  python运算符重载是什么意思？运算符重载也就是为运算符定义方法，而重载是赋予新的含义同一个运算符可以有不同的功能。一些新手对于重载不是很理解，下面给就大家介绍一下python运算符重载。