python - Updating a table from another table with multiple columns in sqlalchemy -

i want update multiple columns of 1 table according other multiple columns of table in sqlalchemy. i'm using sqlite when testing it, can't use `update table1 set col=val table1.key == table2.key" syntax.

in other words, i'm trying create sort of update query:

update table1  set     col1 = (select col1 table2 table2.key == table1.key),     col2 = (select col2 table2 table2.key == table1.key) 

in sqlalchemy:

select_query1 = select([table2.c.col1]).where(table1.c.key == table2.c.key) select_query2 = select([table2.c.col2]).where(table1.c.key == table2.c.key) session.execute(table.update().values(col1=select_query1, col2=select_query2)) 

only i'd query once instead of twice, unless sqlite , mysql smart enough not make query twice themselves.

i don't think can. thus, not answer, far long comment.

you can compose query 2 columns (i guess knew that):

select_query = select([table2.c.col1, table2.c.col2]).where(table1.c.key == table2.c.key) 

and afterwards can use method with_only_columns(), see api:

in[52]: print(table.update().values(col1 = select_query.with_only_columns([table2.c.col1]), col2 = select_query.with_only_columns([table2.c.col2]))) update table set a=(select tweet.id  tweet  tweet.id not null), b=(select tweet.user_id  tweet  tweet.id not null) 

but see update statement, effectivelly doing 2 selects. (sorry did not adapt output example, i'm sure idea).

i'm not sure whether, say, mysql smart enough make 1 query only. guess so. hope helps anyway.