[SOLVED] insert new line into duckdb table with a map column from python dict

insert new line into duckdb table with a map column from python dict

I have a duckdb table with a MAP(INT, MAP(TEXT, TEXT)) column.

I have a python dict with the same structure.

I want to insert a new line into the duckdb table putting the python dict as value of the map column.

How can it be done?

I expect something like

my_dict = {0: {'test_key': 'test_val'}}
with duckdb.connect(configs.db_path) as con:
    query = "INSERT INTO table_name (str_col, map_col) VALUES (?, ?)"
    con.execute(query, ['test_str', my_dict])

but what I've tried didn't worked

Solution

You have to add the MAP keyword before passing the dictionary to be inserted, like this:

con.execute("INSERT INTO tbl VALUES (MAP {'a': 'b'})")

For your particular case you can do this:

row = str(my_dict).replace("{", "MAP {")
query = f"INSERT INTO testtest (str_col, map_col) VALUES ('test_str', {row})"
con.execute(query)

This should work.