CODEWITHSUNDEEP
pythonsqlite
Sakthi Subramani
Sakthi Subramani

Reputation: 35

how to insert dictionary in sqlite3 using python

    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

Upvotes: 0

Views: 1187

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55600

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)

Upvotes: 0

Alex
Alex

Reputation: 516

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))

Upvotes: 1

Related Questions