在 sqlalchemy 中从具有多列的另一个表更新表 [英] Updating a table from another table with multiple columns in sqlalchemy

问题描述

我想在 SQLAlchemy 中根据另一个表的其他多列更新一个表的多列.我在测试时使用 SQLite，所以我不能使用 `UPDATE table1 SET col=val WHERE table1.key == table2.key" 语法.

I want to update multiple columns of one table according to other multiple columns of another table in SQLAlchemy. I'm using SQLite when testing it, so I can't use the `UPDATE table1 SET col=val WHERE table1.key == table2.key" syntax.

换句话说，我正在尝试创建这种更新查询:

In other words, I'm trying to create this sort of update query:

UPDATE table1 
SET 
   col1 = (SELECT col1 FROM table2 WHERE table2.key == table1.key), 
   col2 = (SELECT col2 FROM table2 WHERE table2.key == table1.key)

在 SQLAlchemy 中:

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))

我只想查询一次而不是两次，除非 SQLite 和 MySQL 足够聪明，不会自己进行两次查询.

Only I'd like to do the query only once instead of twice, unless SQLite and MySQL are smart enough not to make that query twice themselves.

推荐答案

我不认为你可以.因此，这不是真正的答案，但评论时间太长了.

I don't think you can. Thus, this is not really an answer, but it is far too long for a comment.

您可以使用 2 列轻松编写查询(我想您已经知道了):

You can easily compose your query with 2 columns (I guess you already knew that):

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

之后你可以使用with_only_columns()方法，参见api:

and afterwards you can use the 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 
FROM tweet 
WHERE tweet.id IS NOT NULL), b=(SELECT tweet.user_id 
FROM tweet 
WHERE tweet.id IS NOT NULL)

但是正如您从更新语句中看到的，您将有效地进行两次选择.(抱歉，我没有完全根据您的示例调整输出，但我相信您明白了.

But as you see from the update statement, you will be effectivelly doing two selects. (Sorry I did not adapt the output completely to your example, but I'm sure you get the idea).

我不确定，如您所说，MySQL 是否足够智能以使其仅进行一次查询.大概吧.无论如何希望它有所帮助.

I'm not sure whether, as you say, MySQL will be smart enough to make it one query only. I guess so. Hope it helps anyway.

这篇关于在 sqlalchemy 中从具有多列的另一个表更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！