英文:
How can I convert this postgres SQL query to sqlalchamy core
问题
我正在使用PostgreSQL与SQLAlchemy，并已经阅读了所有与SQLAlchemy中的UPDATE相关的文档，但没有找到将其转换的方法。
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;
英文:
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;
答案1
得分: 1
这不完全正确，因为我没有为表test设置别名，但很接近。也许我以后可以弄清楚。您需要生成确切的SQL吗？
我认为您需要使用values()
with Session(engine) as session:
# 使用来自ORM类的表对象。您也可以定义一个普通的表。
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()
这将生成以下PostgreSQL SQL语句：
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论