记录python3查询mysql 并解析json字符串的过程

最新推荐文章于 2024-09-11 07:00:00 发布

济南水哥

最新推荐文章于 2024-09-11 07:00:00 发布

阅读量456

点赞数

分类专栏： python 文章标签： mysql json python

本文链接：https://blog.youkuaiyun.com/m0_37779977/article/details/120762979

版权

python 专栏收录该内容

3 篇文章

订阅专栏

有个需求，需要找出数据库表中保存报文的objectId，这个需要如果使用创建spring boot工程实现起来先对简单，为了减少代码量，这里用python脚本来实现。

运行环境为 python3.6 如果没有安装pymysql 模块需要先进行安装。

通过数据库查询出的报文格式如下：

{
	"createTime": "2021-07-30 21:12:47",
	"objectId": "22123sdsd-f041-40b9-9a7b-3223",
	"objectInfo": {
		"InDisk": [
			{
				"objectId": "44444-78f0-404e-8497-ddddd"
			}
		]
	},
	"taskId": "20210730211146949505"
}

python脚本如下：

#!/usr/bin/python3
 
import pymysql
import json
import datetime

db = pymysql.connect(host='ip',
                     user='username',port=3306,
                     passwd='password',
                     database='test') 
cursor = db.cursor()
sql=" SELECT id,taskid,rcontent  from  t_table "
try:
   cursor.execute(sql)
   results = cursor.fetchall()
   date_p = datetime.datetime.now().date()
   str_p = str(date_p)
   f = open("D:\\coding\\output-"+str_p+".txt", "w")
   for row in results:
      id = row[0]
      taskid = row[1]
      receivecontent = row[2]
      d = json.loads(rcontent)
      if (d.__contains__('objectId') and d['objectId'] is  not None):
       print(d['objectId'])
       f.write(d['objectId']+"\n")
      if (d.__contains__('objectInfo') and  d['objectInfo'] is  not None):
          if (d['objectInfo'].__contains__('InDisk') and  d['objectInfo']['InDisk'] is not None):
              neids = d['objectInfo']['InDisk']
              for neid in neids:
                  if (neid.__contains__('objectId') and  neid['objectId'] is  not None):
                      print(neid['objectId'])
                      f.write(d['objectId']+"\n")
except:
   print ("Error: unable to fetch data")
db.close()

此处只是将解析后的输出到文件，也可以存入中间表