我正在努力寻找一种方法，将这个json blob读取到每个记录的元组中，数据库的作者将数据作为json blob写入。然而，当我试图从数据库中提取数据时，我找不到任何关于如何将此blob数据解码为元组的示例。我在下面分享的是sqlite数据库的实际转储，该数据库的结构是blob。我需要把它读成元组，这样我就可以使用单独的值。
def query():
dbconn = sqlite3.connect('inbox.db3')
cursor = dbconn.cursor()
cursor.execute("SELECT *,oid FROM inbox_v1")
incoming_msg = (cursor.fetchall())
print(incoming_msg)
dbconn.commit()
dbconn.close()
[(10, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY YES I LV MY RIG ON 24/7 ITS OUT IN THE BARN AND I JUST REMOTE INTO IT FROM MY PHONE OCCASSIONALLY. ","TO":"WA8WQU","UTC":"2020-09-25 10:43:02","_ID":"101731382614"},"type":"DELIVERED","value":""}', 10), (18, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"HIT TERRY I DONT CURRENTLY HAVE A 2M ANTENNA SETUP BUT WILL TRY TO HIT IT WITH MY HT LATER TODAY","TO":"WA8WQU","UTC":"2020-09-25 17:19:39","_ID":"101755179584"},"type":"DELIVERED","value":""}', 18), (19, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"FB ON THE TURBO MODES I DID NOT NOT KNOW THE HD ALSO INCLUDED THE MSG ID THAT IS SUPER COOL! ","TO":"WA8WQU","UTC":"2020-09-25 17:20:21","_ID":"101755221063"},"type":"DELIVERED","value":""}', 19), (27, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"I'M DOING A LAMB DELIVERY TODAY WILL BE OFFGRID FOR A WHILE","TO":"WA8WQU","UTC":"2020-09-26 15:30:43","_ID":"101835043995"},"type":"DELIVERED","value":""}', 27), (28, '{"params":{"CMD":" MSG TO:","DIAL":3578000,"FREQ":3579117,"FROM":"WA8WQU","OFFSET":1117,"PATH":"WA8WQU","SNR":-7,"SUBMODE":0,"TDRIFT":0.20000000298023224,"TEXT":"TEST.","TO":"WA8WQU","UTC":"2020-09-27 10:41:43","_ID":"101904134116"},"type":"DELIVERED","value":""}', 28), (65, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM BRYAN, MY QRZ PAGE IS QUITE OLD NEEDS AN UPDATE HI HI NEVER NOTICED THE FEATURE OF MESSAGE ID IN THE HB ACK, VERY COOL FEATURE. HI OF 65 TODAY HERE, HV A GR8 DAY 73 DE DAN","TO":"KF1D","UTC":"2020-10-13 10:41:30","_ID":"103286490204"},"type":"DELIVERED","value":""}', 65), (66, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY, A BIT STORMY LAST NIGHT! CONNECTORS ARRIVING TODAY SO I CAN CUT COAX AND GET MY 2M SSB ANT WORKING. 73 DAN","TO":"WA8WQU","UTC":"2020-10-13 10:43:31","_ID":"103286611370"},"type":"DELIVERED","value":""}', 66), (67, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TOM CHILLY 44F THIS MORN. HI OF 65F STILL GUD WRKING WEATHER! LOVE THE MESSAGE ID IN THE HB ACK FEATURE, VERY COOL! 73 DAN","TO":"K1TWH","UTC":"2020-10-13 10:48:27","_ID":"103286907291"},"type":"DELIVERED","value":""}', 67), (73, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TOM FB ON THE RAIN WE GOT A LITTLE AND NOW ITS COOLING TO MORE NORMAL FALL WEATHER. COLORS ARE AT PEAK HERE NOW. 73 DE DAN","TO":"K1TWH","UTC":"2020-10-14 11:39:52","_ID":"103376392311"},"type":"DELIVERED","value":""}', 73), (74, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY HOPE ALL IS WELL ON YOUR SIDE, HOPE TODAY I CAN FIX MY COAX WOES! SMOKING RIBS FOR SUPPER, CAN'T WAIT! 73 DE DAN. ","TO":"WA8WQU","UTC":"2020-10-14 11:41:13","_ID":"103376473008"},"type":"DELIVERED","value":""}', 74), (85, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GE TERRY, SLOWLY GETTING THROUGH THE WINTER READINESS HERE, VRY LITTLE TIME FOR RADIO BUT THOUGHT I'D CHECK IN! 73 FOR NOW","TO":"WA8WQU","UTC":"2020-10-19 23:29:23","_ID":"103850963343"},"type":"STORE","value":""}', 85), (86, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"HI TOM, BEEN BURIED WITH WORK AND GETTING FARM READY FOR WINTER , NOT MY FAVORITE TIME OF YR! 73 FOR NOW DE DAN","TO":"K1TWH","UTC":"2020-10-19 23:31:16","_ID":"103851076419"},"type":"DELIVERED","value":""}', 86), (89, '{"params":{"CMD":" MSG ","DIAL":3578000,"FREQ":3579566,"FROM":"KD2CNC","OFFSET":1566,"PATH":"KD2CNC","SNR":-5,"SUBMODE":0,"TDRIFT":1.100000023841858,"TEXT":"GM DAN, FB ON YOUR JS8 ADD-ON! LOOKING FORWARD TO SEEING IT! TTYL 73 <DARREN","TO":"W5DMH","UTC":"2020-10-21 12:12:42","_ID":"103983240096"},"type":"UNREAD","value":""}', 89)]
参见以下
import json
data = [(10, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY YES I LV MY RIG ON 24/7 ITS OUT IN THE BARN AND I JUST REMOTE INTO IT FROM MY PHONE OCCASSIONALLY. ","TO":"WA8WQU","UTC":"2020-09-25 10:43:02","_ID":"101731382614"},"type":"DELIVERED","value":""}', 10), (18, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"HIT TERRY I DONT CURRENTLY HAVE A 2M ANTENNA SETUP BUT WILL TRY TO HIT IT WITH MY HT LATER TODAY","TO":"WA8WQU","UTC":"2020-09-25 17:19:39","_ID":"101755179584"},"type":"DELIVERED","value":""}', 18), (19, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"FB ON THE TURBO MODES I DID NOT NOT KNOW THE HD ALSO INCLUDED THE MSG ID THAT IS SUPER COOL! ","TO":"WA8WQU","UTC":"2020-09-25 17:20:21","_ID":"101755221063"},"type":"DELIVERED","value":""}', 19), (27, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"I'M DOING A LAMB DELIVERY TODAY WILL BE OFFGRID FOR A WHILE","TO":"WA8WQU","UTC":"2020-09-26 15:30:43","_ID":"101835043995"},"type":"DELIVERED","value":""}', 27), (28, '{"params":{"CMD":" MSG TO:","DIAL":3578000,"FREQ":3579117,"FROM":"WA8WQU","OFFSET":1117,"PATH":"WA8WQU","SNR":-7,"SUBMODE":0,"TDRIFT":0.20000000298023224,"TEXT":"TEST.","TO":"WA8WQU","UTC":"2020-09-27 10:41:43","_ID":"101904134116"},"type":"DELIVERED","value":""}', 28), (65, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM BRYAN, MY QRZ PAGE IS QUITE OLD NEEDS AN UPDATE HI HI NEVER NOTICED THE FEATURE OF MESSAGE ID IN THE HB ACK, VERY COOL FEATURE. HI OF 65 TODAY HERE, HV A GR8 DAY 73 DE DAN","TO":"KF1D","UTC":"2020-10-13 10:41:30","_ID":"103286490204"},"type":"DELIVERED","value":""}', 65), (66, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY, A BIT STORMY LAST NIGHT! CONNECTORS ARRIVING TODAY SO I CAN CUT COAX AND GET MY 2M SSB ANT WORKING. 73 DAN","TO":"WA8WQU","UTC":"2020-10-13 10:43:31","_ID":"103286611370"},"type":"DELIVERED","value":""}', 66), (67, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TOM CHILLY 44F THIS MORN. HI OF 65F STILL GUD WRKING WEATHER! LOVE THE MESSAGE ID IN THE HB ACK FEATURE, VERY COOL! 73 DAN","TO":"K1TWH","UTC":"2020-10-13 10:48:27","_ID":"103286907291"},"type":"DELIVERED","value":""}', 67), (73, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TOM FB ON THE RAIN WE GOT A LITTLE AND NOW ITS COOLING TO MORE NORMAL FALL WEATHER. COLORS ARE AT PEAK HERE NOW. 73 DE DAN","TO":"K1TWH","UTC":"2020-10-14 11:39:52","_ID":"103376392311"},"type":"DELIVERED","value":""}', 73), (74, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GM TERRY HOPE ALL IS WELL ON YOUR SIDE, HOPE TODAY I CAN FIX MY COAX WOES! SMOKING RIBS FOR SUPPER, CAN'T WAIT! 73 DE DAN. ","TO":"WA8WQU","UTC":"2020-10-14 11:41:13","_ID":"103376473008"},"type":"DELIVERED","value":""}', 74), (85, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"GE TERRY, SLOWLY GETTING THROUGH THE WINTER READINESS HERE, VRY LITTLE TIME FOR RADIO BUT THOUGHT I'D CHECK IN! 73 FOR NOW","TO":"WA8WQU","UTC":"2020-10-19 23:29:23","_ID":"103850963343"},"type":"STORE","value":""}', 85), (86, '{"params":{"CMD":" MSG ","DIAL":0,"FREQ":0,"FROM":"W5DMH","OFFSET":0,"PATH":"W5DMH","SNR":0,"SUBMODE":0,"TDRIFT":0,"TEXT":"HI TOM, BEEN BURIED WITH WORK AND GETTING FARM READY FOR WINTER , NOT MY FAVORITE TIME OF YR! 73 FOR NOW DE DAN","TO":"K1TWH","UTC":"2020-10-19 23:31:16","_ID":"103851076419"},"type":"DELIVERED","value":""}', 86), (89, '{"params":{"CMD":" MSG ","DIAL":3578000,"FREQ":3579566,"FROM":"KD2CNC","OFFSET":1566,"PATH":"KD2CNC","SNR":-5,"SUBMODE":0,"TDRIFT":1.100000023841858,"TEXT":"GM DAN, FB ON YOUR JS8 ADD-ON! LOOKING FORWARD TO SEEING IT! TTYL 73 <DARREN","TO":"W5DMH","UTC":"2020-10-21 12:12:42","_ID":"103983240096"},"type":"UNREAD","value":""}', 89)]
data1 =[(d[0],json.loads(d[1]),d[2]) for d in data]
print(data1[0])
另一个选项-创建dict
data2 ={d[0]: json.loads(d[1]) for d in data}
print(data2[89])