更新冲突 postgres 上的多列 [英] Update multiple columns on conflict postgres

问题描述

如果记录存在，我必须编写一个查询来更新记录，否则插入它.我正在将此更新/插入到 postgres 数据库中.我查看了 upsert 示例，其中大多数使用最多两个字段进行更新.但是，我想更新多个列.示例:

I have to write a query to update a record if it exists else insert it. I am doing this update/insert into a postgres DB. I have looked into the upsert examples and most of them use maximum of two fields to update. However, I want to update multiple columns. Example:

query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""

在上面的查询中假设 col2 是唯一键，我插入和更新相同数量的列.我必须使用 pymysql(python 库)执行此查询.在一个简单的插入语句中，我知道如何动态传递包含参数的元组.

In the query above assume that col2 is a unique key, I am inserting and updating the same number of columns. I have to execute this query using pymysql(python library). In a simple insert statement, I know how to pass the tuple containing the parameters dynamically.

cursor.execute(insert_query, data_tuple)

cursor.execute(insert_query, data_tuple)

但在这种情况下，我有两个地方(插入和更新)输入都是动态的.考虑到上面的 upsert 查询，我将参数传递给游标的方式

But in this case, I have both places(insert and update) input to be dynamic. Considering the above upsert query, the way I pass the parameters to the cursor

cursor.execute(upsert_query,data_tuple,data_tuple)

cursor.execute(upsert_query,data_tuple,data_tuple)

然而，这会引发一个错误，即执行函数中的参数数量.那我怎么通过?此外，我试图使用这种方式来传递参数，因为使用赋值(=)对于 20 列来说是一件费力的事情.

However, this one throws up an error with the number of arguments being in the execute function. So how do I pass? Moreover, I am trying to use this way to pass parameters because using the assignment(=) would be a laborious thing to do for 20 columns.

有没有其他替代方法可以做到这一点?就像mysql中的一个简单的替换为"语句.

Is there any other alternative way to do this? Like a simple "replace into" statement in mysql.

推荐答案

对您问题的直接回答是，您执行 tuple + tuple 以将元组加倍.

The direct answer to your question is, you do a tuple + tuple to double the tuple.

cursor.execute(upsert_query, data_tuple + data_tuple)

其他选项:

如果你有单独的值并且你正在构造元组，你可以直接构造一个具有两倍值数量的元组.

Other Options:

If you have individual values and you are constructing the tuple, you can directly construct a tuple with twice the number of values.

query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET col1=%s, col3=%s, col4=%s, ..."""

cur.execute(query, (c1, c2, c3, ... c20, c1, c3, c4, ... c20))

您必须指定值(col2 除外)两次.

You will have to specify the values (except col2) twice.

如果您已经有了最初要求的元组，那么您将使用 + 将同一个元组合并两次.

If you already have the tuple, which is what you originally asked, then you will use a + to merge the same tuple twice.

如果您有单个值而不是元组，您还可以使用命名参数，如字典.

If you have the individual values and not the tuple, you could also use named parameters like a dictionary.

query="""INSERT INTO table (col1,col2,col3,col4...) VALUES(%(c1)s, %(c2)s, %(c3)s, %(c4)s...) ON CONFLICT(col2) DO UPDATE SET col1=%(c1)s, col3=%(c3)s, col4=%(c4)s, ..."""
cur.execute(query, {'c1': c1val, 'c2': c2val, 'c3': c3val, 'c4': c4val, ...})

这种形式有利于可读性，只传递一次参数，如果以后的列数发生变化，易于维护(增加列数等).

This form is good for readability, passes parameters only once, and is easy to maintain (increase in columns, etc) if the number of columns change in the future.

这篇关于更新冲突 postgres 上的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！