How can I convert this postgres SQL query to sqlalchamy core

Question

I am using postgres with sqlalchamy and have gone through all documentation of update from in sqlalchamy but not found way to convert it.

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

Answer 1

This isn't exactly correct because I didn't alias the table test but it is pretty close. Maybe I can figure that out later. Do you need that EXACT sql to be generated?

I think you need to use values()

with Session(engine) as session:
    # Use a table object from an ORM class.  You could define a regular table too.
    test_t = Test.__table__
    value_expr = values(
        column('column_b', String),
        column('column_a', Integer),
        name="c").data(
            [('123', 1), ('345', 2)])
    stmt = update(test_t).where(test_t.c.column_b == value_expr.c.column_b).values(column_a=value_expr.c.column_a)
    session.execute(stmt)
    session.commit()

This makes this SQL using postgresql:

UPDATE test SET column_a=c.column_a FROM (VALUES (%(param_1)s, %(param_2)s), (%(param_3)s, %(param_4)s)) AS c (column_b, column_a) WHERE test.column_b = c.column_b