SQLAlchemy插入更新方法及行数返回

本文深入解析了在 SQLAlchemy 中实现“插入或更新”（upsert）操作并准确获取受影响行数的多种方法与关键陷阱：针对 PostgreSQL，强调必须借助 `RETURNING` 子句配合 `fetchall()` 才能精确统计真实影响行数，而非依赖不可靠的 `rowcount`；对 MySQL 和 SQLite，则分别指出需通过 `exec_driver_sql()` 获取 `rowcount` 或调用 `SELECT changes()` 来规避方言限制；同时明确警示 ORM 层中 `merge()` 的非原子性缺陷，推荐使用原生 `insert().on_conflict_do_update().returning()`（SQLAlchemy 2.0+）实现高效、可监控的 upsert，并特别提醒约束缺失将导致操作静默失效——这些细节直击生产环境常见痛点，是数据库开发者绕不开的实战指南。

SQLAlchemy 里 insert().on_conflict_do_update() 不返回行数

PostgreSQL 的 ON CONFLICT DO UPDATE 本身不直接返回“影响了多少行”，execute() 返回的 Result 对象默认也不暴露这个信息。你调用 result.rowcount 得到的往往是 -1（尤其在 psycopg2 异步或某些驱动配置下），不是真实受影响行数。

真正能拿到插入/更新行数的方式，是靠 RETURNING 子句 + 手动计数：

• 对 PostgreSQL：用 INSERT ... ON CONFLICT DO UPDATE RETURNING id，然后统计 result.fetchall() 长度
• 对 MySQL：原生不支持 ON DUPLICATE KEY UPDATE 的 RETURNING，得靠 rowcount —— 但必须确保使用 exec_driver_sql() 或显式设置 cursor.rowcount 可用（如关闭连接池预处理）
• 对 SQLite：INSERT OR REPLACE 后查 connection.execute("SELECT changes()").scalar()

PostgreSQL 实操：用 RETURNING 精确统计

这是最可靠的方式，适用于需要区分“新插入”和“已存在但更新”的场景：

from sqlalchemy import text
stmt = text("""
INSERT INTO users (id, name, email)
VALUES (:id, :name, :email)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email
RETURNING id
""")
result = conn.execute(stmt, {"id": 123, "name": "Alice", "email": "a@b.com"})
affected_count = len(result.fetchall())  # ✅ 真实行数

注意点：

• RETURNING 必须指定至少一个列（哪怕只写 RETURNING 1），否则 fetchall() 为空，长度为 0
• 不能混用 ORM 模型对象直接传入 text()；要用原生参数绑定
• 如果想同时知道是 insert 还是 update，可在 RETURNING 里加伪列：RETURNING CASE WHEN xmax = 0 THEN 'insert' ELSE 'update' END（PostgreSQL 内部机制）

MySQL / SQLite 的兼容性取舍

MySQL 的 ON DUPLICATE KEY UPDATE 会把“插入”和“更新”都算作“受影响行数”，但 SQLAlchemy 默认不透出这个值：

• 用 exec_driver_sql()（SQLAlchemy 2.0+）可绕过 ORM 层限制：result = conn.exec_driver_sql("INSERT INTO t ... ON DUPLICATE KEY UPDATE ...")，之后 result.rowcount 通常可用
• SQLite 的 INSERT OR REPLACE 本质是删+插，rowcount 不稳定；推荐改用 conn.exec_driver_sql("SELECT changes()").scalar() 获取最后操作变更行数
• 跨数据库抽象时，别依赖 rowcount —— 它在不同方言、不同执行模式（如 execute vs executemany）下行为差异极大

ORM 层怎么写？别用 merge()

session.merge() 看似符合“插入或更新”语义，但它内部是先 SELECT 再决定 insert/update，无法原子化，且 rowcount 总是 1（不管实际是否写入）。

真要走 ORM 路线，只能手动控制：

• 用 session.execute(insert(...).on_conflict_do_update(...).returning(...))（SQLAlchemy 2.0+ 支持）
• 避免混合 ORM 实例和原生语句；一旦用了 RETURNING，就老实用字典或元组接收结果
• 别指望 session.commit() 后从 session 缓存里反推影响行数——缓存不记录底层 DML 统计

最易被忽略的是：RETURNING 在 PostgreSQL 中要求目标表有主键或唯一约束才能触发 ON CONFLICT，否则语法报错；而 MySQL 的 ON DUPLICATE KEY UPDATE 则强制依赖已有 UNIQUE/PRIMARY KEY 索引 —— 约束缺失时，所谓“插入或更新”会静默变成纯插入，且无提示。

理论要掌握，实操不能落！以上关于《SQLAlchemy插入更新方法及行数返回》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！