SQLAlchemy如何基于键值对批量更新数据库行？

你要的这种批量更新完全可以实现，能把多次查询合并成一次，主要有两种常用方案：

方法一：通用CASE WHEN语句（适配所有数据库） #

利用SQL的CASE WHEN逻辑，一次性完成所有匹配行的更新，SQLAlchemy可以直接构造该语句：

from sqlalchemy import case

# 将keys和values配对为键值对字典
key_value_map = dict(zip(keys, values))

# 构造CASE表达式：匹配到对应key时设置指定value，未匹配的保持原值
case_expression = case(
    [(Table.key == key, value) for key, value in key_value_map.items()],
    else_=Table.value
)

# 执行批量更新，仅生成一条SQL
db.session.query(Table).filter(Table.key.in_(keys)).update(
    {Table.value: case_expression},
    synchronize_session=False  # 跳过会话对象同步，提升性能
)
db.session.commit()

这段代码会生成如下类似的SQL：

UPDATE "table" 
SET value = CASE 
    WHEN key = 1 THEN 6 
    WHEN key = 2 THEN 7 
    WHEN key = 3 THEN 8 
    WHEN key = 4 THEN 9 
    WHEN key = 5 THEN 0 
    ELSE value 
END 
WHERE key IN (1, 2, 3, 4, 5)

方法二：PostgreSQL专属FROM子句更新（效率更优） #

如果你的数据库是PostgreSQL，可以使用更高效的UPDATE ... FROM语法，直接通过键值对集合关联更新：

简洁SQL执行方式 #

from sqlalchemy import text

# 构造VALUES子句内容
values_clause = ", ".join([f"({k}, {v})" for k, v in zip(keys, values)])

# 执行更新
db.session.execute(text(f"""
    UPDATE "table" t
    SET value = v.new_value
    FROM (VALUES {values_clause}) AS v(key, new_value)
    WHERE t.key = v.key
"""))
db.session.commit()

ORM风格构造（避免硬写SQL） #

from sqlalchemy import Table as SQLTable, Column, Integer
from sqlalchemy.sql import select

# 创建临时表存储键值对
temp_table = SQLTable(
    'temp_key_value',
    Column('key', Integer),
    Column('new_value', Integer),
    prefixes=['TEMPORARY'],
    bind=db.session.get_bind()
)
temp_table.create()

# 批量插入键值对到临时表
db.session.execute(temp_table.insert(), [{'key': k, 'new_value': v} for k, v in zip(keys, values)])

# 通过临时表关联更新原表
db.session.query(Table).filter(Table.key == temp_table.c.key).update(
    {Table.value: temp_table.c.new_value},
    synchronize_session=False
)

temp_table.drop()
db.session.commit()

关键提示 #

• 两种方案都只会生成一条SQL查询，相比循环更新，在数据量较大时性能提升非常明显。
• synchronize_session=False参数可跳过SQLAlchemy对会话内对象的同步操作，若不需要立即使用会话中的实例，建议添加该参数以提升效率。
• 方法一为跨数据库通用方案，方法二仅适用于PostgreSQL。

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