sql中json数据的提取保存

sql中json数据的提取保存

1.需求描述

​ SQL库表中的字段包含json数据，先需要读取处理其中需要的数据，然后存入新表。

库表信息：如下图

需求信息：红框中的数据

2.数据构建

​ 为了完整实现json数据在SQL中的提取功能，首先要有数据。这里以rds(mysql)为例子，在库表中构建包含json数据的表字段。

import json
import pymysql

conn = pymysql.connect(
            host='rm-*****************do.mysql.rds.aliyuncs.com',  			  # 连接的数据库服务器主机名
            port=3310,  # 数据库端口号
            user='root',  # 数据库登录用户名
            passwd='我是密码',
            db='database',  # 数据库名称
            charset='utf8'  # 连接编码
        )
cur = conn.cursor()
ID = 101
data = {
    "resourceId": "S330109TAB012021031900007",
    "catalogId": "206879",
    "applyBasis": "线下测试",
    "createTime": "2021-09-07",
    "callbackData": {'grantTime': "2021-09-07 16:33:56", "tableView": "test112"}
}

# 将 Python 字典类型转换为 JSON 对象
json_str = json.dumps(data)
sql1 = "insert into json_data(id,content) values (%s,%s);"
r = cur.execute(sql1, (ID, json_str))
conn.commit()  # commit：增、删、改都需要提交事务
cur.close()
conn.close()
print('受影响的行数：%s'%r)

3.Json类型数据的提取令存

3.1 代码分析

import json
import pymysql

class Jsonselect_After_Insert:

    def connerct_mysql(self):
        conn = pymysql.connect(
                    host='rm-*******do.mysql.rds.aliyuncs.com',  
                    port=3310,  # 数据库端口号
                    user='root',  # 数据库登录用户名
                    passwd='我是密码',
                    db='otherdatabase',  # 数据库名称
                    charset='utf8'  # 连接编码
        )
        cur = conn.cursor()
        return conn, cur

    def Json_Select(self,cur):
        # 将 Python 字典类型转换为 JSON 对象
        sql1 = "select content from json_data limit 2;"
        cur.execute(sql1)
        result = cur.fetchall()

        ks = []
        vs = []

        for i in range(len(result)):
            keys = []
            values = []
            for key, value in json.loads(list(result[i])[0]).items():
                if key == 'resourceId':
                    keys.append(key)
                    values.append(value)
                elif key == 'catalogId':
                    keys.append(key)
                    values.append(value)
                elif key == 'applyBasis':
                    keys.append(key)
                    values.append(value)
                elif key == 'createTime':
                    keys.append(key)
                    values.append(value)
                elif key == 'callbackData':
                    for k in value:
                        keys.append(k)
                        values.append(value[k])
                else:
                    continue
            vs.append(values)
            if keys in ks:
                continue
            else:
                ks.append(keys)
        print(f'{ks}')
        # print('\n'+f'{vs}')
        return ks, vs

    def Select_After_Insert(self, conn, cur, ks, vs):
        arr =','.join(ks[0])
        num = 0
        sum = 0
        for i in vs:
            print(i)
            sql = f"insert into data_from_json ({arr}) values(%s,%s,%s,%s,%s,%s);"
            r = cur.execute(sql, tuple(vs[num]))
            conn.commit()  # commit：增、删、改都需要提交事务
            num = num + 1
            sum = sum + r
        conn.close()
        print('数据插入成功！'+'\n'+'受影响的行数：%s' % sum)

if __name__ == '__main__':
    Jai = Jsonselect_After_Insert()             # 创建Jai对象
    conn, cur = Jai.connerct_mysql()            # 连接数据库
    ks, vs = Jai.Json_Select(cur)               # 读取Json数据
    Jai.Select_After_Insert(conn, cur, ks, vs)  # 需要数据写入数据库

3.2 结果呈现

4.jsonselect_Onecount

4.1 单key值需求

需求如下：把''projectName"的value值提取处理，查看其值都有哪些信息，即获得去重后的列表。

4.2 代码编辑

import json
import pymysql

class Jsonselect_Onecount:

    def connerct_mysql_213(self):
        conn213 = pymysql.connect(
                    host='127.0.0.1',  # 连接的数据库服务器主机名
                    port=3306,  # 数据库端口号
                    user='*********',  # 数据库登录用户名
                    passwd='*********',
                    db='digital_operation_platform',  # 数据库名称
                    charset='utf8'  # 连接编码
        )
        cur213 = conn213.cursor()
        return conn213, cur213


    def Json_Select(self, cur213):
        # 将 Python 字典类型转换为 JSON 对象
        sql_213 = '''select data_json from irs_resources_apply limit 800;
                    '''

        r = cur213.execute(sql_213)
        result = cur213.fetchall()

        ks = []
        vs = []

        for i in range(len(result)):
            keys = []
            values = []
            for key, value in json.loads(list(result[i])[0]).items():
                if key == 'applyElement':  #
                    for k, v in value[0].items():
                        if k == 'projectName':
                            keys.append(k)
                            values.append(v)
                        else:
                            continue
                else:
                    continue
            # 获取values列表最后的一个值
            if len(values) > 0:
                ks.append(keys[-1])
                vs.append(values[-1])
            else:
                continue

        # print('\n' + f'{vs}')

        # 列表去重
        ks_distinct = set(ks)
        vs_distinct = set(vs)
        print('\n' + f'key值列表为：{ks_distinct}')
        print('\n'+'去重后的列表为：' + '\n' + f'{vs_distinct}' + '\n' + f'vs_distinct的长度为：{len(vs_distinct)}')

        cur213.close()
        conn213.close()
        print('受影响的行数：%s' % r)


if __name__ == '__main__':
    Jai = Jsonselect_Onecount()             # 创建Jai对象
    conn213, cur213 = Jai.connerct_mysql_213()  # 连接数据库
    Jai.Json_Select(cur213)          # 读取Json数据

4.3 结果呈现