从嵌套的json文件中提取数据并插入sql db。 从关键细节中,我想获得以下信息并插入sql db强>
json文件数据和键/值示例如下
{
"response": {
"client_log": {
"data": [
{
"city": "LONDON",
"login": "AAAAAAAAAAAAAA",
"state": "MC",
"details": "Please find report below:\r\n\r\n------Report Information------\r\n\r\nEmail Id: user1@gmail.com\r\nServ Id: 1101ar12\r\nServ Num: 11111\r\nServ Details: Super-A\r\nState: LONDON\r\nCity: LONDON\r\n\r\n------Service Information------\r\n\r\nUser Name: John Clark\r\nMobile Number: 000111222\r\n\r\n------Reported Form------\r\n\r\nForm-1: zzzzz\r\nType: 111\r\n\r\nRemarks: 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:\r\n\r\n------Report Information------\r\n\r\nEmail Id: user2@gmail.com\r\nServ Id: 903oa112\r\nServ Num: 12345\r\nServ Details: Super-B\r\nState: Sydney\r\nCity: Sydney\r\n\r\n------Service Information------\r\n\r\nUser Name: Peter\r\nMobile Number: 333444555\r\n\r\n------Reported Form------\r\n\r\nForm-2: xxxxxxxxxx\r\nType: 111\r\n\r\nRemarks: 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列详细信息)。。。多谢各位
您可以使用
split()
和/或find()
方法轻松解析与details
键关联的str
。一行一行地走,假设:
左边的所有内容都是键,右边的所有内容都是值例如:
或
作为}的每一项的
mydata['response']['client_log']['data']
{dict
现在有一个parsed_details
键,其值是一个dict
,其中的键和值对是从details
键中提取的,正如您在这个输出中看到的:我对SQL不是很熟悉,所以我无法帮助您完成更新数据库的最后一步
相关问题 更多 >
编程相关推荐