SQLAlchemy correlated update for multiple columns

Question

I have been able to used the correlated update construct shown in the docs here to update one column in a table.

For example:

sel = select([UpdateTable.column]).\
      where(UpdateTable.id == OrigTable.id)

up = update(OrigTable).values(column=sel) 

Which produces SQL like:

UPDATE origtable SET column=(SELECT updatetable.column
FROM updatetable
WHERE updatetable.id = origtable.id)

Is it possible to use the Declaritive or Query Api to update multiple columns for a selection?

I am trying to emulate something like the following in PostgreSQL:

UPDATE origtable
   SET
     column1 = updatetable.column1,
     column2 = updatetable.column2
   FROM updatetable 
   WHERE origtable.id == updatetable.id

EDIT:

It seems that formatting this a single statement may not be possible with the current SQLAlchemy api.

However, a workaround using two selects seems possible using the solution from here.

Answer 1

Since this is for Postgresql, we can use SQLAlchemy's multiple table update support. The documentation is for Core only, but the Query API is built atop Core, and we can apply that knowledge:

session.query(OrigTable).\
    filter(OrigTable.id == UpdateTable.id).\
    update({OrigTable.column1: UpdateTable.column1,
            OrigTable.column2: UpdateTable.column2},
           synchronize_session=False)

which results in

UPDATE origtable
SET column1=updatetable.column1,
    column2=updatetable.column2
FROM updatetable
WHERE origtable.id = updatetable.id