如何将Python字典插入含MAP(INT, MAP(TEXT, TEXT))列的DuckDB表？

直接传入Python嵌套字典无法被DuckDB正确解析为MAP(INT, MAP(TEXT, TEXT))类型，以下是几种可行的实现方式：

方法1：使用duckdb.Map构造嵌套MAP对象 #

显式将Python字典转换为DuckDB的Map类型，内层字典也需要同步转换：

import duckdb
from duckdb import Map

my_dict = {0: {'test_key': 'test_val'}}
# 转换嵌套结构为DuckDB兼容的Map类型
converted_map = Map({k: Map(v) for k, v in my_dict.items()})

with duckdb.connect(configs.db_path) as con:
    query = "INSERT INTO table_name (str_col, map_col) VALUES (?, ?)"
    con.execute(query, ['test_str', converted_map])

方法2：通过Pandas DataFrame插入 #

DuckDB可以自动识别Pandas DataFrame中的嵌套字典，并映射为对应的MAP类型：

import pandas as pd
import duckdb

my_dict = {0: {'test_key': 'test_val'}}
# 构造与表结构匹配的DataFrame
df = pd.DataFrame({'str_col': ['test_str'], 'map_col': [my_dict]})

with duckdb.connect(configs.db_path) as con:
    con.execute("INSERT INTO table_name SELECT * FROM df")

方法3：构造SQL MAP字面量 #

将Python字典转换为DuckDB支持的MAP字面量语法，直接写入SQL语句：

import duckdb

my_dict = {0: {'test_key': 'test_val'}}
# 生成DuckDB兼容的MAP字符串
map_segments = []
for outer_key, inner_dict in my_dict.items():
    inner_segments = ", ".join([f"'{k}', '{v}'" for k, v in inner_dict.items()])
    map_segments.append(f"{outer_key}, MAP({inner_segments})")
final_map_literal = f"MAP({', '.join(map_segments)})"

with duckdb.connect(configs.db_path) as con:
    query = f"INSERT INTO table_name (str_col, map_col) VALUES ('test_str', {final_map_literal})"
    con.execute(query)

内容的提问来源于stack exchange，提问作者user11696358