【Python提取MySQL中的JSON数据】

一、背景

问卷的数据保存在MySQL的question_item表中，需要从表中提取出问卷的【题目】、【选项】、【类型】。

表内容如下：

表中data为JSON结构，示例如下：

{
  "ident": 20677939******3249,
  "options": [
    {
      "ident": 20677939******3250,
      "key": "男的",
      "value": "1",
      "child": null
    },
    {
      "ident": 20677939******7552,
      "key": "女的",
      "value": "2",
      "child": null
    },
    {
      "ident": 20677939******7553,
      "key": "不晓得",
      "value": "3",
      "child": null
    }
  ],
  "other": false,
  "otherTips": null
}

二、Python提取JSON数据

Python编写两个方法：

        execute_sql()   #  执行SQL语句获取基础数据

        extract_data()  #  提取字典数据存入列表

先从question_item表中查询出基础数据，再遍历提取。

import json
import traceback
from pprint import pprint
import pymysql


def execute_sql(dbinfo, sql):
    try:
        conn = pymysql.connect(**dbinfo)  # 连接数据库
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 数据库游标
        cursor.execute(sql)  # 执行sql语句
        results = cursor.fetchall()  # 查询结果
        conn.close()  # 关闭数据库连接
        return results
    except Exception as err:
        info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
        print(info)


def extract_data(data):
    ex_data = []
    for i in data:
        for index, k in enumerate(i):
            if k == 'data':
                for j in json.loads(i[k])['options']:
                    dict_data = {'题目': i['label'], '选项': j['key']}
                    if i['type'] == 1:
                        dict_data['题型'] = '单选'
                    elif i['type'] == 2:
                        dict_data['题型'] = '多选'
                    ex_data.append(dict_data)
    return ex_data


# 数据库信息
db_info = {
    'host': "192.168.1.1",
    'port': 3306,
    'user': "test",
    'passwd': "test",
    'db': "test_db",
    'charset': "utf8"
}
# 查询SQL语句
s = 'select label,data,type from question_item where form_ident =20677939******1234;'
# 执行SQL，查询基础数据
datas = execute_sql(db_info, s)
# 遍历基础数据，提取字典数据存入列表
ed = extract_data(datas)
pprint(ed)

运行结果：

三、SQL查询JSON数据

上面的方法始终要先去数据库查询数据，那么能不能直接就一步通过SQL查询出来呢？

搜索一番资料后，发现MySQL中有一个json_table()函数，可将JSON数据转换为表格形式。

实现如下：

SELECT t1.label '题目', t2.k '选项',( CASE t1.type WHEN 1 THEN '单选' WHEN 2 THEN '多选' ELSE '其他' END ) '题型' 
FROM
	question_item t1
	INNER JOIN json_table (
	t1.DATA,
	'$.options[*]' COLUMNS ( k VARCHAR ( 255 ) path '$.key' )) t2 
WHERE
	t1.form_ident = 20677939******1234;

运行结果：