如何用psycopg2.extras.execute_values()插入含序列字段的多条记录？

问题出在你把nextval('name_of_sequence')当成字符串参数传了，psycopg2会把它当作普通字符串值处理，自然匹配不上bigint类型的id列。下面是几种靠谱的解决方法：

方法1：直接在INSERT语句里指定序列函数 #

这是最常用也最简洁的方式，把nextval直接写进SQL语句，参数列表只传其他字段的值：

from psycopg2.extras import execute_values

# 构造SQL语句，id字段直接用nextval生成
insert_stmt = "INSERT INTO your_table (id, col1, col2) VALUES (nextval('name_of_sequence'), %s, %s)"
# 参数列表只包含col1、col2的值，不用管id
data_list = [
    ("string_val_1", 100),
    ("string_val_2", 200),
    ("string_val_3", 300)
]

execute_values(cursor, insert_stmt, data_list)
conn.commit()

方法2：如果id是自增主键，直接省略id字段 #

如果你的id列定义成了SERIAL、BIGSERIAL或者GENERATED AS IDENTITY，PostgreSQL会自动关联序列，这时候连nextval都不用写，直接忽略id字段插入即可：

insert_stmt = "INSERT INTO your_table (col1, col2) VALUES (%s, %s)"
data_list = [
    ("string_val_1", 100),
    ("string_val_2", 200)
]

execute_values(cursor, insert_stmt, data_list)
conn.commit()

方法3：动态传入SQL表达式（特殊场景） #

如果需要动态指定序列（比如不同行用不同序列），可以用psycopg2的sql模块构造SQL，把序列函数作为SQL表达式传入，避免被当作字符串处理：

from psycopg2 import sql
from psycopg2.extras import execute_values

# 动态构造包含序列函数的SQL语句
insert_stmt = sql.SQL("INSERT INTO your_table (id, col1, col2) VALUES ({seq_func}, %s, %s)").format(
    seq_func=sql.SQL("nextval('name_of_sequence')")
)
data_list = [
    ("string_val_1", 100),
    ("string_val_2", 200)
]

# 注意这里要把SQL对象转成字符串传入execute_values
execute_values(cursor, insert_stmt.as_string(cursor.connection), data_list)
conn.commit()

核心原因 #

psycopg2的参数化查询会对传入的参数做转义处理以保证安全，所以你直接传的nextval('...')字符串会被当成普通文本，而非SQL函数调用。只有把函数写进原始SQL语句里，PostgreSQL才会解析并执行它生成序列值。

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