将一个键的字符串值提取到嵌套json中的另一个键/值中，并插入到sqldb中

Question

从嵌套的json文件中提取数据并插入到sqldb中。根据关键细节，我想获得以下信息并插入到sqldb中

json文件数据和密钥/值的示例如下

{
"response": {
"client_log": {
"data": [
{
"city": "LONDON",
"login": "AAAAAAAAAAAAAA",
"state": "MC",
"details": "Please find report below:rnrn------Report Information------rnrnEmail Id: [e-mail] Id: 1101ar12rnServ Num: 11111rnServ Details: Super-ArnState: LONDONrnCity: LONDONrnrn------Service Information------rnrnUser Name: John ClarkrnMobile Number: 000111222rnrn------Reported Form------rnrnForm-1: zzzzzrnType: 111rnrnRemarks: Remarks 123.",
"log_number": "1",
"department": "Sales",
"staff_id": "S123",
"staff_name": "EricY",
"timestamp": "2020-02-27 15:57:24"
},
{
"city": "SINGAPORE",
"login": "BBBBBBBBBBBBB",
"state": "XX",
"details": "Please find report below:rnrn------Report Information------rnrnEmail Id: [e-mail] Id: 903oa112rnServ Num: 12345rnServ Details: Super-BrnState: SydneyrnCity: Sydneyrnrn------Service Information------rnrnUser Name: PeterrnMobile Number: 333444555rnrn------Reported Form------rnrnForm-2: xxxxxxxxxxrnType: 111rnrnRemarks: Remarks 890.",
"log_number": "1",
"department": "Eng",
"staff_id": "S456",
"staff_name": "YongG",
"timestamp": "2020-02-27 15:57:24"
}
],
"query": "13"
},
"response_time": "0.723494",
"transaction_id": "909122",
"transaction_status": "OK",
}
}

这是我用来提取数据并插入到sql中的代码片段

myfile = 'sample.json'
with open(myfile, 'r') as f:
mydata = json.load(f)

sql = "INSERT INTO `table1` (`city`, `login`, `state`, `details`, `log_number`, `department`, `staff_id`, `staff_name`, `timestamp`) VALUES ( %(city)s, %(login)s, %(state)s, %(details)s, %(log_number)s, %(department)s, %(staff_id)s, %(staff_name)s, %(timestamp)s )"
cursor.executemany( sql, mydata['response']['client_log']['data'])
db.commit()
db.close()

从上面的代码中，我可以获得包含数据的键详细信息，但我获得的详细信息值是在sql上显示并插入到1大块数据中的(sql列详细信息(。。。非常感谢。

Answer 1

您可以使用split()和/或find()方法轻松解析与details键关联的str。逐行进行，假设:左边的所有内容都是关键，右边的所有内容就是值。

例如：

import json
myfile = 'sample.json'
with open(myfile, 'r') as f:
mydata = json.load(f)
for entry in mydata['response']['client_log']['data']:
parsed_details = {}
for line in entry['details'].split('rn'):
split = line.find(': ') # find() returns -1 if no match is found
if split != -1:
key = line[:split]
value = line[split+2:] # 2 = len(': ')
parsed_details[key] = value
entry['parsed_details'] = parsed_details

或

import json
myfile = 'sample.json'
with open(myfile, 'r') as f:
mydata = json.load(f)
for entry in mydata['response']['client_log']['data']:
parsed_details = {}
for line in entry['details'].split('rn'):
try:
key, value = line.split(': ', maxsplit=1)
parsed_details[key] = value
except ValueError:
# This error is only thrown when the line doesn't have ': ' in it, 
# which means there aren't enough values to unpack. It is safe to pass.
pass
entry['parsed_details'] = parsed_details

作为mydata['response']['client_log']['data']list的每一项的dict现在都有一个parsed_details密钥，其值是一个具有从details密钥中提取的密钥和值对的dict，如您在以下输出中所见：

mydata
Out[2]: 
{'response': {'client_log': {'data': [{'city': 'LONDON',
'login': 'AAAAAAAAAAAAAA',
'state': 'MC',
'details': 'Please find report below:rnrn------Report Information------rnrnEmail Id: [e-mail] Id: 1101ar12rnServ Num: 11111rnServ Details: Super-ArnState: LONDONrnCity: LONDONrnrn------Service Information------rnrnUser Name: John ClarkrnMobile Number: 000111222rnrn------Reported Form------rnrnForm-1: zzzzzrnType: 111rnrnRemarks: Remarks 123.',
'log_number': '1',
'department': 'Sales',
'staff_id': 'S123',
'staff_name': 'EricY',
'timestamp': '2020-02-27 15:57:24',
'parsed_details': {'Email Id': '[e-mail]',
'Serv Id': '1101ar12',
'Serv Num': '11111',
'Serv Details': 'Super-A',
'State': 'LONDON',
'City': 'LONDON',
'User Name': 'John Clark',
'Mobile Number': '000111222',
'Form-1': 'zzzzz',
'Type': '111',
'Remarks': 'Remarks 123.'}},
{'city': 'SINGAPORE',
'login': 'BBBBBBBBBBBBB',
'state': 'XX',
'details': 'Please find report below:rnrn------Report Information------rnrnEmail Id: [e-mail] Id: 903oa112rnServ Num: 12345rnServ Details: Super-BrnState: SydneyrnCity: Sydneyrnrn------Service Information------rnrnUser Name: PeterrnMobile Number: 333444555rnrn------Reported Form------rnrnForm-2: xxxxxxxxxxrnType: 111rnrnRemarks: Remarks 890.',
'log_number': '1',
'department': 'Eng',
'staff_id': 'S456',
'staff_name': 'YongG',
'timestamp': '2020-02-27 15:57:24',
'parsed_details': {'Email Id': '[e-mail]',
'Serv Id': '903oa112',
'Serv Num': '12345',
'Serv Details': 'Super-B',
'State': 'Sydney',
'City': 'Sydney',
'User Name': 'Peter',
'Mobile Number': '333444555',
'Form-2': 'xxxxxxxxxx',
'Type': '111',
'Remarks': 'Remarks 890.'}}],
'query': '13'},
'response_time': '0.723494',
'transaction_id': '909122',
'transaction_status': 'OK'}}

我对SQL不是很熟悉，所以我无法帮助您完成更新数据库的最后一步。