Insert a list of dicts as json into SQLite cell and get it back as it is-CodePudding

I need to insert into SQLite table into one cell a list of dicts and be able to get it back as it is, - a list of dicts. There is an option to insert it as a json string. The SQLite is accepting it, but returning a 'malformed node or string'. I´m using pycharm. Tried to use ast.literal_eval() to transform to list - the same error. And Why all that brackets for and the coma ata the end? Would apreciate any help.

import json, sqlite3, pprint

list1 = [{'id': '9', 'name': 'Buiding'},
         {'id': '10', 'name': 'Security'},
         {'id': '11', 'name': 'Mass Media'},
         {'id': '12', 'name': 'Consulting'},
         {'id': '13', 'name': 'Medical care'}]

conn = sqlite3.connect('test.db')
c = conn.cursor()

c.executescript("DROP TABLE IF EXISTS result; CREATE TABLE result (data json)")
c.execute("insert into result values (?)", [json.dumps(list1)])
conn.commit()

a = c.execute("select json_extract (data, '$') FROM result;").fetchall()
conn.close()

pprint.pprint(a)

[('[{"id":"9","name":"Buiding"},{"id":"10","name":"Security"}, 
{"id":"11","name":"Mass Media"},{"id":"12","name":"Consulting"}, 
{"id":"13","name":"Medical care"}]',)]

CodePudding user response：

Do not use json_extract! You insert the dict as string via JSON.dumps, you read it back as string into a str variable, and unpack via JSON.loads.

import json, sqlite3, pprint

list1 = [{'id': '9', 'name': 'Buiding'},
         {'id': '10', 'name': 'Security'},
         {'id': '11', 'name': 'Mass Media'},
         {'id': '12', 'name': 'Consulting'},
         {'id': '13', 'name': 'Medical care'}]

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.executescript("DROP TABLE IF EXISTS countries; CREATE TABLE result (data json)")
c.execute("insert into result(data) values (?)", [json.dumps(list1)])
conn.commit()

a = json.loads(c.execute("select data FROM result;").fetchone()[0])
conn.close()

pprint.pprint(a == list1)