how to insert dictionary in sqlite3 using python

Question

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

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

If you really want to store dictionaries/JSON, there are better databases to use than relational/columnar ones — OneCricketeer
– OneCricketeer, Commented Oct 5, 2021 at 18:31

Alex · Accepted Answer · 2021-10-05 18:04:37Z

1

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

answered Oct 5, 2021 at 18:04

Alex

5264 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

snakecharmerb · Accepted Answer · 2021-10-06 06:38:41Z

0

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)

answered Oct 6, 2021 at 6:38

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Collectives™ on Stack Overflow

how to insert dictionary in sqlite3 using python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related