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

Answer 1

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