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数据