how to insert dictionary in sqlite3 using python-CodePudding

    a="1"
    b="2"
    c="3"
    d="4"
    user1 = {"id": 100, "name": "Rumpelstiltskin", "dob": "12/12/12", "item": [{"name": "sakthi"}]}


    con = sqlite3.connect(database=r'data.db')
    cur = con.cursor()

    cur.execute("INSERT INTO e_transations (eid,sl,id,datee,ent) VALUES (?,?,?,?,?)",(a,b,c,d,user1,))

    con.commit()

how to insert dictionary in sqlite3 using python

user1 was save to single column and single row

EX : ( 1 2 3 4 {"id": 100, "name": "Rumpelstiltskin", "dob": "12/12/12", "item": [{"name": "sakthi"}]})

save above format in sqlite3

CodePudding user response：

You can't do that. The closest you can do is to serialise the dictionary to a string using json.dumps and then store the resulting string into the column in sqlite3.

import json
serialised = json.dumps(<dictionary here>)

...
cur.execute("INSERT INTO e_transations (eid, sl, id, datee, ent) VALUES (?, ?, ?, ?, ?)", (a, b, c, d, serialised))

CodePudding user response：

You can use the "named" parameter substitution style to obtain SQL statement values from a dictionary. Instead of using ?, use :key where key is the key in the dictionary.

data = {'foo': 1, 'bar': 2, 'baz': 3}

with sqlite3.connect(':memory:') as conn:
    conn.execute("""CREATE TABLE t (foo integer, bar integer, baz integer)""")
    conn.execute("""INSERT INTO t (foo, bar, baz) VALUES (:foo, :bar, :baz)""", data)