SQLAlchemy update record with case clause and enum

Question

I have defined the following model and enum:

class StatusEnum(enum.Enum):
    NEW = 'NEW'
    PROCESSED = 'PROCESSED'
    IN_PROGRESS = 'IN_PROGRESS'

class RequestLog(Base):
    __tablename__ = 'request_log'

    ...
    status = Column(Enum(StatusEnum))
    ...

I'm trying to update the record in the following way:

>>> session.query(RequestLog).filter(RequestLog.id.in_([8])).update(
{'status': case(
    [(RequestLog.attempt_done_count == RequestLog.attempt_count - 1, StatusEnum.PROCESSED)],
    else_=StatusEnum.IN_PROGRESS)},
synchronize_session=False)

During this, I received an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'StatusEnum' [SQL: 'UPDATE request_log SET status=CASE WHEN (request_log.attempt_done_count = request_log.attempt_count - %(attempt_count_1)s) THEN %(param_1)s ELSE %(param_2)s END WHERE request_log.id IN (%(id_1)s)'] [parameters: {'param_2': <StatusEnum.IN_PROGRESS: 'IN_PROGRESS'>, 'attempt_count_1': 1, 'param_1': <StatusEnum.PROCESSED: 'PROCESSED'>, 'id_1': 8}]

Answer 1

For a simple Python enum object bind value the bind processor of sqltypes.Enum is run, which uses the string name of the enum object in question:

In [27]: session.query(RequestLog).filter(RequestLog.id.in_([8])).update(
    ...:     {'status': StatusEnum.NEW},
    ...:     synchronize_session=False)
2017-07-24 15:15:43,848 INFO sqlalchemy.engine.base.Engine UPDATE request_log SET status=%(status)s WHERE request_log.id IN (%(id_1)s)
INFO:sqlalchemy.engine.base.Engine:UPDATE request_log SET status=%(status)s WHERE request_log.id IN (%(id_1)s)
2017-07-24 15:15:43,848 INFO sqlalchemy.engine.base.Engine {'status': 'NEW', 'id_1': 8}
INFO:sqlalchemy.engine.base.Engine:{'status': 'NEW', 'id_1': 8}
Out[27]: 0

This does not seem to happen recursively for SQL expressions though, so in your case() the enum object bind values are passed to psycopg, which does not know how to handle them. To emulate the behaviour in an SQL expression you can manually pass the names of the enum objects, with an appropriate cast:

In [60]: session.query(RequestLog).filter(RequestLog.id.in_([8])).update(
    ...:     {'status': case(
    ...:         [(true(), StatusEnum.PROCESSED.name)],
    ...:         else_=StatusEnum.IN_PROGRESS.name).cast(RequestLog.status.type)},
    ...:     synchronize_session=False)
2017-07-24 15:40:52,853 INFO sqlalchemy.engine.base.Engine UPDATE request_log SET status=CAST(CASE WHEN true THEN %(param_1)s ELSE %(param_2)s END AS statusenum) WHERE request_log.id IN (%(id_1)s)
INFO:sqlalchemy.engine.base.Engine:UPDATE request_log SET status=CAST(CASE WHEN true THEN %(param_1)s ELSE %(param_2)s END AS statusenum) WHERE request_log.id IN (%(id_1)s)
2017-07-24 15:40:52,853 INFO sqlalchemy.engine.base.Engine {'param_2': 'IN_PROGRESS', 'param_1': 'PROCESSED', 'id_1': 8}
INFO:sqlalchemy.engine.base.Engine:{'param_2': 'IN_PROGRESS', 'param_1': 'PROCESSED', 'id_1': 8}
Out[60]: 0

It is unsightly and I'm somewhat sure there exists a better method, but for the time being this was the best I could come up with.