包含case子句和枚举的SQLAlchemy update记录

from sqlalchemy import create_engine, case
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Enum

engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    status = Column(Enum('A', 'B', 'C'))

# 枚举值映射
status_mapping = {
    'A': 'Active',
    'B': 'Blocked',
    'C': 'Closed'
}

# 构建case子句
case_clause = case(
    value=MyTable.status,
    whens={
        'A': status_mapping['A'],
        'B': status_mapping['B'],
        'C': status_mapping['C']
    },
    else_=MyTable.status
)

# 执行更新操作
session.query(MyTable).update({MyTable.status: case_clause}, synchronize_session=False)
session.commit()