批量更新mysql

UPDATE table_name
SET column1 = CASE id
    WHEN 1 THEN 'value1'
    WHEN 2 THEN 'value2'
    WHEN 3 THEN 'value3'
END,
column2 = CASE id
    WHEN 1 THEN 'value4'
    WHEN 2 THEN 'value5'
    WHEN 3 THEN 'value6'
END
WHERE id IN (1, 2, 3);

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://user:password@host/database')
metadata = MetaData(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()

table = Table('table_name', metadata,
    Column('id', Integer, primary_key=True),
    Column('column1', String),
    Column('column2', String)
)

update_data = [
    {'id': 1, 'column1': 'value1', 'column2': 'value4'},
    {'id': 2, 'column1': 'value2', 'column2': 'value5'},
    {'id': 3, 'column1': 'value3', 'column2': 'value6'}
]

stmt = table.update().\
    where(table.c.id == bindparam('b_id')).\
    values({'column1': bindparam('b_column1'), 'column2': bindparam('b_column2')})

session.execute(stmt, [
    {'b_id': row['id'], 'b_column1': row['column1'], 'b_column2': row['column2']} for row in update_data
])

session.commit()