inserting a dictionary to sqlite3 database - python

Question

I'm trying to store a dictionary in a SQLite database (python) and be able to pull it from the DB and use it like any other dict in python and It doesn't seem to work. I've tried using json.dumps() and still no success.

This is my table:

cursor.execute("""
CREATE TABLE IF NOT EXISTS users(
email TEXT PRIMARY KEY NOT NULL,
password TEXT NOT NULL,
ip_dict VARIANT NOT NULL);
""")

and I would like to insert a dict to the "ip_dict" column, for instance:

ip_dict = {"pc1": "192.168.1.2", "router": "192.168.1.1"}

i tried doing:

ip_dict = json.dumps(ip_dict)
cursor.execute(f"""
        INSERT INTO users(email,password,ip_dict)
        VALUES("[email protected]", "123456789", {ip_dict})
        """)

but no success. Thanks

Edit the question and add what "no success" means.

DinoCoderSaurus
– DinoCoderSaurus

2021-01-07 13:17:43 +00:00
Commented Jan 7, 2021 at 13:17 — DinoCoderSaurus
– DinoCoderSaurus, Commented Jan 7, 2021 at 13:17

Booboo · Accepted Answer · 2021-01-07 13:43:02Z

First, I would not overlay the dictionary with its string (i.e. JSON) representation; I would use separate variable just to keep things straight. But since you now have a string representation and you want to store that value in a TEXT column, nothing further needs to be done (I don't know what you were trying to accomplish by surrounding the string with {}):

ip_dict_json = json.dumps(ip_dict)
cursor.execute(f"""
        INSERT INTO users(email,password,ip_dict)
        VALUES('[email protected]', '123456789', ?)
        """, (ip_dict_json,))

Note that I am using single quotes to represent string literals as this is the more common and portable method to do so and that the string value for the dictionary is being passed as a parameter to a prepared statement.

Update

If you want to get more sophisticated, you could define the column type to be a special type such as dictionary and then specify an adapter and converter for this type:

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users(
    email TEXT PRIMARY KEY NOT NULL,
    password TEXT NOT NULL,
    ip_dict dictionary NOT NULL);
    """)

import json

sqlite3.register_adapter(dict, lambda d: json.dumps(d).encode('utf8'))
sqlite3.register_converter("dictionary", lambda d: json.loads(d.decode('utf8')))

cursor.execute(f"""
        INSERT INTO users(email,password,ip_dict)
        VALUES('[email protected]', '123456789', ?)
        """, (ip_dict,)) # passing a dictionary and not a string

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row[2]) # this is a dictionary and not a string

Collectives™ on Stack Overflow

inserting a dictionary to sqlite3 database - python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related