psycopg2:使用元组元组中的值更新表中的多行 [英] psycopg2: Update multiple rows in a table with values from a tuple of tuples
问题描述
我正在尝试使用元组元组一次更新多行.我想出了如何从 这篇文章 构建 sql 语句,但事实证明,在 psycopg2
中实现它更具挑战性.这是我所拥有的:
I'm attempting to update several rows at once using a tuple of tuples.
I figured out how to construct the sql statement from this post, but implementing it in psycopg2
has proven to be more challenging.
Here's what I have:
c = db.cursor()
new_values = (("Richard",29),("Ronald",30))
sql = """UPDATE my_table AS t
SET name = e.name
FROM (VALUES %s) AS e(name, id)
WHERE e.id = t.id;"""
c.execute(sql, (new_values,))
结果是一个错误:ProgrammingError: table "e" has 1 columns available but 2 columns specified
这是因为 FROM
子句被解释为:
The result is an error: ProgrammingError: table "e" has 1 columns available but 2 columns specified
This is because the FROM
clause is being interpreted as:
FROM (VALUES (("Richard",29),("Ronald",30)))
代替:
FROM (VALUES ("Richard",29),("Ronald",30))
我可以通过执行以下操作来解决此问题,但它似乎不安全:
I can work around this by doing the following but it seems unsafe:
import re
c = db.cursor()
sql = """UPDATE my_table AS t
SET name = e.name
FROM (VALUES %s) AS e(name, id)
WHERE e.id = t.id;"""
sql = c.mogrify(sql, (new_values,))
# Replace the first occurance of '((' with '('.
sql = sql.replace('((', '(',1)
# Replace the last occurance of '))' with ')'.
sql = re.sub(r'(.*)\)\)', r'\1)', sql)
sql = c.execute(sql)
有没有更好的方法来做到这一点?
Is there a better way to do this?
推荐答案
这篇文章 为我指明了正确的方向.文档 extras.execute_values
还包含一个使用 UPDATE
子句的好例子.
This post pointed me in the right direction. The documentation for extras.execute_values
also contains a great example using the UPDATE
clause.
c = db.cursor()
update_query = """UPDATE my_table AS t
SET name = e.name
FROM (VALUES %s) AS e(name, id)
WHERE e.id = t.id;"""
psycopg2.extras.execute_values (
c, update_query, new_values, template=None, page_size=100
)
这篇关于psycopg2:使用元组元组中的值更新表中的多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!