DuckDB中如何将INSERT...RETURNING结果直接插入临时映射表？

核心思路 #

DuckDB不支持executemany结合RETURNING返回结果的用法，但可以通过数据库端的SQL语句合并操作，将插入永久表、返回主键/业务键、写入映射表三个步骤在DuckDB内部完成，完全不需要把数据加载到Python内存中。

方案1：从临时表批量插入（推荐，适合大数据量） #

如果待插入数据已经在DuckDB临时表中，直接用WITH子句捕获INSERT...RETURNING的结果，再插入映射表：

示例代码 #

import duckdb

# 建立连接
conn = duckdb.connect('your_database.db')
cursor = conn.cursor()

# 假设已创建以下表：
# - temp_source: 存放待导入的临时数据（含业务键business_key）
# - permanent_table: 永久表，含自增主键id、业务键business_key及其他字段
# - temp_mapping: 临时映射表，用于存储永久表主键id与业务键business_key的对应关系

# 执行合并插入+映射写入
merge_sql = """
WITH inserted_records AS (
  INSERT INTO permanent_table (col1, col2, business_key)
  SELECT col1, col2, business_key FROM temp_source
  ON CONFLICT (business_key) DO NOTHING  -- 重复校验逻辑，根据需求调整
  RETURNING id, business_key
)
INSERT INTO temp_mapping (perm_id, business_key)
SELECT id, business_key FROM inserted_records;
"""

cursor.execute(merge_sql)
conn.commit()

这个方案的优势是所有数据处理都在DuckDB引擎内部完成，数万行数据不会进入Python内存，效率远高于先取回数据再插入的方式。

方案2：从Python批量数据导入（先导入临时表再映射） #

如果待插入数据在Python内存中（比如列表、DataFrame），先通过copy_from高效导入临时表，再用方案1的逻辑处理：

示例代码 #

import duckdb
import pandas as pd

# 假设待插入数据是Pandas DataFrame
data_df = pd.DataFrame({
    'col1': ['val1', 'val2', ...],
    'col2': ['val3', 'val4', ...],
    'business_key': ['bk1', 'bk2', ...]
})

conn = duckdb.connect('your_database.db')
cursor = conn.cursor()

# 创建临时表并导入数据（比executemany高效得多）
cursor.execute("CREATE TEMP TABLE temp_source (col1 VARCHAR, col2 VARCHAR, business_key VARCHAR);")
conn.copy_from(data_df, 'temp_source')

# 执行合并插入+映射写入（同方案1的SQL）
merge_sql = """
WITH inserted_records AS (
  INSERT INTO permanent_table (col1, col2, business_key)
  SELECT col1, col2, business_key FROM temp_source
  ON CONFLICT (business_key) DO NOTHING
  RETURNING id, business_key
)
INSERT INTO temp_mapping (perm_id, business_key)
SELECT id, business_key FROM inserted_records;
"""

cursor.execute(merge_sql)
conn.commit()

为什么executemany会报错？ #

DuckDB的executemany设计用于重复执行无返回结果的SQL语句（比如不带RETURNING的INSERT），不支持捕获每次执行的RETURNING结果并批量返回。而SQLite对executemany的RETURNING支持是其特有的实现，DuckDB目前没有这个特性，因此必须改用数据库端的SQL合并操作。

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