如何使用 psycopg2 更新 postgresql 中多行的多列 [英] How can I update multiple columns on multiple rows in postgresql using psycopg2
问题描述
我有一个有点复杂的 sql 查询,它应该更新表中多行的多列.我正在尝试将多个参数传递给查询,并且还循环遍历要通过 psycopg2 更新的数据,但我想不出一种方法来做到这一点.
I have a somewhat complex sql query that should update multiple columns on multiple rows in a table. Am trying to pass the multiple parameters to the query and also loop though the data to be updated through psycopg2 but I can't figure out a way to do this.
这是我想要循环的示例数据.
Here is the sample data I want to loop through.
data = [(214, 'Feb', 545), (215, 'March', 466)]
到目前为止,这是我的 sql 查询
So far here is the sql query I have
query = """
UPDATE table_1
SET
date_from =
(CASE version
WHEN 1 THEN '1900-01-01' ELSE
( SELECT date_to
FROM table_1
WHERE month = data.month
AND cust_key = data.cust_key
AND prod_key = data.prod_key
AND version = (
SELECT version-1
FROM table_1
WHERE month = data.month
AND cust_key = data.cust_key
AND prod_key = data.prod_key
ORDER BY version DESC LIMIT 1)
)
END),
date_to = current_date
FROM (VALUES %s) AS data(cust_key, month, prod_key)
WHERE month = data.month
AND cust_key = data.cust_key
AND prod_key = data.prod_key
"""
这是我传递参数的方式
WHERE month = data.month
AND cust_key = data.cust_key
AND prod_key = data.prod_key
FROM (VALUES %s) AS data(cust_key, month, prod_key)
这就是我执行查询的方式
And this is how I am executing the query
cursor = db.cursor()
execute_values(cursor, query, (data,))
db.commit()
return True
当我执行查询时,出现此错误 psycopg2.errors.InvalidColumnReference: table "data";有 2 列可用,但指定了 3 列我在这个网站上经历了多种解决方案,但似乎没有一个对我有用.
When I execute the query, I get this error psycopg2.errors.InvalidColumnReference: table "data" has 2 columns available but 3 columns specified I have gone through multiple solutions on this site but none seems to work for me.
有没有办法解决这个问题?
Is there a way around this?
推荐答案
您的 data
已经采用 psycopg2.extras.execute_values
预期的格式.
Your data
is already in the format as expected by psycopg2.extras.execute_values
.
所以,不要把它转换成另一个元组,直接做
So, do not convert it to another tuple, simply do
execute_values(cursor, query, data)
这篇关于如何使用 psycopg2 更新 postgresql 中多行的多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!