Psycopg3高效批量插入与冲突处理：executemany的正确姿势

---

本文旨在解决psycopg3中`executemany`方法批量插入多行数据时，针对`values %s`占位符与`on conflict`子句结合使用时遇到的常见`programmingerror`。我们将探讨如何正确构建包含多个列的`values`子句，提供两种解决方案：一种是基于字符串拼接的动态占位符生成，另一种是利用`psycopg.sql`模块进行更安全、更专业的sql语句组合，确保数据高效插入并妥善处理冲突。

Psycopg3中executemany批量插入的挑战

在Psycopg3中，executemany方法是实现批量数据插入的推荐方式，它能够高效地执行多条相似的SQL语句。然而，与Psycopg2的execute_values不同，直接将SQL语句中的VALUES子句简单地写为VALUES %s，并期望它能自动展开为多列占位符，会导致ProgrammingError: the query has 1 placeholder but X parameters were passed。这是因为Psycopg3要求VALUES子句中的占位符数量必须与要插入的列数精确匹配。

例如，对于一个包含7列的表，如果尝试使用如下SQL和数据：

sql = """
INSERT INTO activities (type_, key_, a, b, c, d, e)
VALUES %s
ON CONFLICT (key_) DO UPDATE
SET
    a = EXCLUDED.a,
    b = EXCLUDED.b,
    c = EXCLUDED.c,
    d = EXCLUDED.d,
    e = EXCLUDED.e
"""
values = [['type', 'key', None, None, None, None, None]] # 实际数据，每行7个元素
# cursor.executemany(sql, values)

执行时会抛出ProgrammingError，因为VALUES %s只提供了一个占位符，而values列表中的每个子列表却提供了7个参数。为了解决这个问题，我们需要确保VALUES子句包含与列数相匹配的占位符。

解决方案一：动态构建VALUES子句 (字符串拼接)

最直接的方法是根据要插入的列数，动态生成形如(%s, %s, ..., %s)的VALUES子句。这种方法简单易懂，适用于SQL结构相对固定的场景。

核心思路：

1. 获取数据列表中每行元素的数量，这代表了要插入的列数。
2. 生成与列数相同数量的%s占位符，并用逗号连接。
3. 将这些占位符用括号括起来，形成完整的VALUES子句。
4. 将这个动态生成的VALUES子句替换到原始SQL模板中。

示例代码：

import psycopg

# 假设这是你的原始SQL模板，其中包含一个占位符用于VALUES子句
# 注意：这里我们使用一个格式化字符串占位符 {} 来替换 VALUES 子句
base_sql_template = """
INSERT INTO activities (type_, key_, a, b, c, d, e)
VALUES {}
ON CONFLICT (key_) DO UPDATE
SET
    a = EXCLUDED.a,
    b = EXCLUDED.b,
    c = EXCLUDED.c,
    d = EXCLUDED.d,
    e = EXCLUDED.e
"""

# 待插入的数据，每个子列表代表一行，包含7个元素
values_to_insert = [
    ['type1', 'key1', 1, 2, 3, 4, 5],
    ['type2', 'key2', 6, 7, 8, 9, 10],
    ['type3', 'key3', None, None, None, None, None]
]

if not values_to_insert:
    print("没有数据可插入。")
else:
    # 1. 获取列数（取第一行数据的长度）
    num_columns = len(values_to_insert[0])

    # 2. 生成占位符字符串，例如：'%s, %s, %s'
    placeholders = ', '.join(['%s'] * num_columns)

    # 3. 将占位符用括号括起来，形成 VALUES 子句，例如：'(%s, %s, %s)'
    values_clause = f"({placeholders})"

    # 4. 将 VALUES 子句注入到原始SQL模板中
    final_sql = base_sql_template.format(values_clause)

    print("生成的最终SQL语句示例:")
    print(final_sql)

    # 建立数据库连接并执行
    try:
        # 请替换为你的实际数据库连接信息
        with psycopg.connect(dbname='test', user='your_user', password='your_password', host='localhost') as conn:
            with conn.cursor() as cur:
                cur.executemany(final_sql, values_to_insert)
                conn.commit()
                print(f"成功插入/更新 {len(values_to_insert)} 行数据。")
    except psycopg.Error as e:
        print(f"数据库操作失败: {e}")

注意事项：

Manus

全球首款通用型AI Agent，可以将你的想法转化为行动。

250 查看详情

• 这种方法简单有效，但在构建复杂SQL或防止SQL注入方面存在潜在风险。
• 如果列数可能变化，确保num_columns的计算是准确的。

解决方案二：使用psycopg.sql模块安全构建SQL (推荐)

对于更专业、更安全的SQL语句构建，Psycopg3提供了psycopg.sql模块。这个模块允许你以编程方式组合SQL片段，从而避免手动字符串拼接可能带来的SQL注入风险，并提高代码的可读性和可维护性。

核心思路：

1. 使用sql.SQL对象封装SQL语句的静态部分。
2. 使用sql.Placeholder()生成单个占位符对象。
3. 利用sql.SQL(', ').join()方法将多个sql.Placeholder()对象连接起来，形成动态的占位符列表。
4. 使用sql.SQL.format()方法将动态生成的占位符列表注入到SQL语句中。

示例代码：

import psycopg
from psycopg import sql

# 待插入的数据，每个子列表代表一行，包含7个元素
values_to_insert = [
    ['type1', 'key1', 1, 2, 3, 4, 5],
    ['type2', 'key2', 6, 7, 8, 9, 10],
    ['type3', 'key3', None, None, None, None, None]
]

if not values_to_insert:
    print("没有数据可插入。")
else:
    # 1. 获取列数
    num_columns = len(values_to_insert[0])

    # 2. 使用sql.Placeholder()生成与列数匹配的占位符列表
    # sql.SQL(', ').join(...) 会将多个 sql.Placeholder() 用逗号连接
    placeholders_sql = sql.SQL(', ').join(sql.Placeholder() * num_columns)

    # 3. 构建完整的SQL语句，使用 {placeholders} 作为 VALUES 子句的占位符
    # 注意：VALUES ({placeholders}) 中的括号是SQL语法的一部分
    final_sql_obj = sql.SQL("""
INSERT INTO activities (type_, key_, a, b, c, d, e)
VALUES ({placeholders})
ON CONFLICT (key_) DO UPDATE
SET
    a = EXCLUDED.a,
    b = EXCLUDED.b,
    c = EXCLUDED.c,
    d = EXCLUDED.d,
    e = EXCLUDED.e
""").format(placeholders=placeholders_sql) # 使用 .format() 注入动态生成的占位符

    # 建立数据库连接并执行
    try:
        # 请替换为你的实际数据库连接信息
        with psycopg.connect(dbname='test', user='your_user', password='your_password', host='localhost') as conn:
            with conn.cursor() as cur:
                # 打印生成的SQL语句（用于调试）
                print("使用psycopg.sql生成的最终SQL语句示例:")
                print(final_sql_obj.as_string(conn)) # as_string() 用于查看最终的SQL字符串

                cur.executemany(final_sql_obj, values_to_insert)
                conn.commit()
                print(f"成功插入/更新 {len(values_to_insert)} 行数据。")
    except psycopg.Error as e:
        print(f"数据库操作失败: {e}")

优势：

• 安全性： psycopg.sql模块可以有效防止SQL注入攻击，因为它将SQL结构和参数值分离处理。
• 可读性与可维护性： 对于复杂的SQL语句，使用此模块可以使代码结构更清晰，更易于理解和维护。
• 灵活性： 能够以编程方式动态构建SQL的各个部分，适应各种复杂的查询需求。

总结与注意事项

在Psycopg3中使用executemany进行批量插入并处理冲突时，关键在于正确构建VALUES子句的占位符。

1. 占位符数量匹配： 确保VALUES子句中的%s占位符数量与你尝试插入的列数严格一致。一个%s代表一个参数，而不是一行或一个多列结构。
2. ON CONFLICT子句： ON CONFLICT (key_) DO UPDATE SET ...是PostgreSQL中实现UPSERT（更新或插入）逻辑的标准方式，它与executemany和动态占位符的构建完美结合。
3. 推荐使用psycopg.sql模块： 尽管字符串拼接可以解决问题，但psycopg.sql模块提供了更安全、更健壮、更专业的SQL构建方式。特别是在生产环境或处理动态SQL时，强烈推荐使用它来组合SQL语句，以提高代码质量和安全性。

通过以上两种方法，你可以有效地在Psycopg3中利用executemany实现高效的批量数据插入和冲突处理。

以上就是Psycopg3高效批量插入与冲突处理：executemany的正确姿势的详细内容，更多请关注其它相关文章！

# 句中  # 阜阳网站建设推广方法  # 福建创新网站推广公司  # 网络诚信建设网站  # 拉萨seo服务  # 关键词优化排名的方式  # 西安最好的seo优化  # 三只松鼠网站推广方式  # 开封网站怎么推广  # 清河品质网站建设  # 皮影戏营销推广策略研究  # 这是  # word  # 中带  # 与非  # 解决问题  # 行数  # 两种  # 文档  # 多个  # 子句  # 防止sql注入  # sql语句  # sql注入 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 《红果免费短剧》下载观看方法  Golang中的rune与byte类型区别是什么_Golang字符与字节处理详解  键盘声音异常怎么回事_键盘异响怎么处理  4399造梦西游3无敌版_4399游戏入口  广州地铁app准妈咪徽章领取方法  C++如何使用CMake构建项目_C++ CMakeLists.txt编写入门教程  word页码灰色不能用如何解决  实现二叉树的层序插入：基于树大小的路径导航  mysql通配符能用于日志查询吗_mysql通配符在系统日志查询中的实际使用方法  Python中处理嵌套字典与列表的数据提取与过滤教程  《洛克王国：世界》国家队搭配攻略  在XML中嵌入二进制数据（如图片）的最佳实践是什么？ Base64编码与解析注意事项  汽水音乐官网网页版入口 汽水音乐官网网页版在线入口  谷歌学术论文搜索引擎 谷歌学术官网入口论坛永久链接  Chart.js 教程：自定义插件实现图表与图例间距调整  diskgenius分区工具如何设置Bios启动项  晓晓优选app支付宝绑定方法  告别繁琐SEO！如何使用SyliusSitemap插件自动化生成网站地图，提升搜索引擎排名  c++20的指定初始化(Designated Initializers)怎么用_c++ C风格结构体初始化  CSS如何使用outline-offset与颜色组合突出元素边框  如何配置VS Code作为您Git操作的默认编辑器  win11怎么启用或禁用休眠 Win11 powercfg命令管理休眠文件【技巧】  《单词速记宝》设置学习计划方法  快手网页版官方访问 快手网页版页面在线打开  谷歌邮箱官方入口链接 谷歌邮箱网页版电脑端快速登录  优化Flask模板中SQLAlchemy查询迭代标签：处理字符串空格问题  泰拉瑞亚水晶无法放置问题  C++ priority_queue怎么用_C++优先队列底层实现与自定义比较器  CSS如何在页面中引入重置样式_使用Normalize.css或Reset.css统一浏览器默认样式  汽车之家网页版免费登录_汽车之家官网首页直接进入  手机自动关机是怎么回事？如何修复？手机异常关机的原因排查与修复技巧  哔哩哔哩在线观看入口 B站官网免费进入  《全民k歌》网页版最新登录入口一览  Teambition网盘如何共享文件  批改网网页版登录 批改网电脑版学生登录入口  《下一站江湖2》心法融合技巧  企查查官网和爱企查 企查查企业查询官网入口  Win10怎么设置快速启动 Win10开启快速启动设置方法  铁路12306怎么申请退票_铁路12306退票申请操作流程  Sublime Text怎么关闭自动完成_Sublime禁用Auto Complete设置  手机坏了微信聊天记录怎么导出来 新手机恢复聊天记录技巧  睡觉时心跳快是什么原因 夜间心悸如何应对  如何用mysql开发用户注册登录功能_mysql用户注册登录数据库设计  小红书如何引流到私信？引流到私信有用吗？  win11关机几秒又自己开机 Win11关机自动重启问题修复  iPhone 14 Pro如何更改区域设置_iPhone 14 Pro地区语言修改教程  VS Code快捷键when上下文子句的妙用  钉钉任务无法提醒如何处理 钉钉任务提醒优化方法  个人所得税办理入口 个人所得税综合所得年度汇算入口  纯CSS实现滚动时动态时间轴线条颜色填充效果 

 2025-11-10