我需要INSERT ... ON CONFLICT DO UPDATE使用 psycopg2 一次將( ) 多行upsert到 postgreSQL 資料庫中。本質上，我有一個表示“行”的元組串列，我需要將它們插入到資料庫中，或者在發生沖突時更新資料庫。我需要（可能）更新每一列（如果沒有插入），以及每一行。
我嘗試了兩種主要方法，使用 psycopg2 的cursor.execute()函式和execute_many()函式。首先，我做了以下事情：
upsert_statement = 'INSERT INTO table (col1, col2, col3) VALUES %s ON CONFLICT (col1) DO UPDATE SET (col1, col2, col3) = ROW (excluded.*) WHERE table IS DISTINCT FROM excluded'
psycopg2.extras.execute_values(cursor, upsert_statement, values)
我創建了一個插入值的 SQL 陳述句execute_many()（values傳遞給它的是元組串列），并且在發生沖突時，列值應該更新為排除。但是，SyntaxError: number of columns does not match number of values 有時我會收到錯誤訊息，即使我知道列數和值相同的事實。
所以，我嘗試只使用execute()：
upsert_statement = f'INSERT INTO table (col1, col2, col3) VALUES (value1, value2, value3), (value4, value5, value6)... ON CONFLICT (col1) DO UPDATE SET (col1, col2, col3) = (value1, value2, value3), (value4, value5, value6)...'
cursor.execute(upsert_statement)
在這里，我將批量更新插入作為 SQL 的一部分，因此不必使用execute_values(). 但是，我在SyntaxError之后得到了一個DO UPDATE SET，因為我認為擁有(col1, col2, col3) = (value1, value2, value3), (value4, value5, value6)....
我究竟做錯了什么？如何使用 psycopg2 批量插入多行？
（我應該注意到在現實中，(col1, col2, col3)并且(value1, value2, value3)是動態的，并且經常變化）
uj5u.com熱心網友回復：
您需要在陳述句中使用表 EXCLUDED而不是值文字ON CONFLICT。這是一個特殊的表，其中包含建議用于插入的值。您也不需要重新設定沖突的值，只需重新設定其余的值。
INSERT INTO table (col1, col2, col3)
VALUES
(value1, value2, value3),
(value4, value5, value6)
ON CONFLICT (col1) DO UPDATE
SET (col2, col3) = (EXCLUDED.col2, EXCLUDED.col3);
為了便于閱讀，如果您將 f 字串三重參考，您可以格式化您的內嵌 SQL。我不確定是否以及哪些 IDE 可以檢測到它是 Python 中的行內 SQL 并切換語法突出顯示，但我發現縮進足夠有用。
upsert_statement = f"""
INSERT INTO table (col1, col2, col3)
VALUES
({value1}, {value2}, {value3}),
({value4}, {value5}, {value6})
ON CONFLICT (col1) DO UPDATE
SET (col2, col3) = (EXCLUDED.col2, EXCLUDED.col3)"""
這是一個簡單的測驗：
drop table if exists test_70066823 cascade;
create table test_70066823 (
id integer primary key,
text_column_1 text,
text_column_2 text);
insert into test_70066823 select 1,'first','first';
insert into test_70066823 select 2,'second','second';
select * from test_70066823;
-- id | text_column_1 | text_column_2
------ --------------- ---------------
-- 1 | first | first
-- 2 | second | second
--(2 rows)
insert into test_70066823
values
(1, 'third','first'),
(3, 'fourth','third'),
(4, 'fifth','fourth'),
(2, 'sixth','second')
on conflict (id) do update
set text_column_1=EXCLUDED.text_column_1,
text_column_2=EXCLUDED.text_column_2;
select * from test_70066823;
-- id | text_column_1 | text_column_2
------ --------------- ---------------
-- 1 | third | first
-- 3 | fourth | third
-- 4 | fifth | fourth
-- 2 | sixth | second
--(4 rows)
您可以參考此以提高插入性能。使用簡單的基于字串的插入execute或者execute_many是那里提到的前 2 個最慢的方法。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/363753.html