Postgresql UPSERT при шифровании столбца с помощью шифрования Python
Я использую BULK UPSERT для вставки записи в новую таблицу, пока данные столбца шифруются с помощью python lib CryptoDome. вот что я сделал
def bulk_upsert_query(data, table_name=None):
values_list = []
add = values_list.append
for i in data:
encrypt_user_id = encryption_fnc(str(i.user_id))
add(f"({i.id},'{encrypt_user_id}','{i.name}')")
upsert_sql = r"""insert into upsert_test_table
(id,encrypt_user_id,name)
values
""" + "\n, ".join(values_list) + """ ON CONFLICT ON CONSTRAINT
"upsert_test_table_pkey"
DO UPDATE SET
name=excluded.name,
encrypt_user_id=excluded.user_id"""
return upsert_sql
data = данные из моей старой таблицы с user_id как int (например: 124 345 786)
в upsert_test_table столбце идентификатора таблицы является столбцом первичного ключа.
этот bulk_upsert_query fnc зашифрует int user_id и добавит к values_list, а затем создаст запрос upsert.
В соответствии с вставкой он работает, как я и ожидал, но когда дело доходит до обновления, если конфликт со столбцом id, то, как вы можете видеть, я установил encrypt_user_id=excluded.user_id для столбца user_id,
он обновляет существующий зашифрованный user_id с помощью int user_id (из старой таблицы), потому что я не помещал здесь никакого шифрования fnc,
Обновление: столбец user_id можно изменить
Итак, что я хочу, так это назвать свой encryption_fnc в DO UPDATE SET разделе,
пока он обновляется, я также хочу сделать шифрование.
Может ли кто-нибудь сказать мне, кто я могу достичь этого?
Примечание. Я могу выполнять шифрование на сайте базы данных с помощью pgcrypto, но мое требование — выполнять шифрование на стороне Python, а не на стороне базы данных.
@FrankHeikens Извините, я плохо разбираюсь в sql-инъекциях, но эта функция будет выполняться вручную только 1/2 раза, поэтому меня не очень беспокоят sql-инъекции. спасибо за вашу заботу, если у вас есть какие-либо предложения, я все слышу.
У вас нет столбца user_id в полезной нагрузке вставки. encrypt_user_id=excluded.user_id поэтому вставляет null вместо старого, незашифрованного user_id. демо. Вы пробовали заменить это на encrypt_user_id=excluded.encrypt_user_id? У вас уже есть зашифрованный user_id для этой строки в полезной нагрузке, почему бы не указать на это
@Zegarek У меня есть столбец user_id в моей вставляемой полезной нагрузке (данные), и я вставляю этот столбец в новую таблицу путем шифрования (user_id), вы сказали, заменяя excluded.user_id на excluded.encrypt_user_id здесь excluded укажите исходный источник данных, который имеет незашифрованный столбец user_id, а не столбец encrypt_user_id .
@Zegarek извините, я был глуп, я устал, заменив excluded.user_id на excluded.encrypt_user_id, и это работает, хотя я понятия не имею, как работает таблица excluded, спасибо.
Ответы 2
Решено:
Извините, я был глуп, я думал, что таблица excluded является исходной таблицей (входная полезная нагрузка), но когда я заменил excluded.user_id на excluded.encrypt_user_id, и она работает, хотя понятия не имею, как работает исключенная таблица.
upsert_sql = r"""insert into upsert_test_table
(id,encrypt_user_id,name)
values
""" + "\n, ".join(values_list) + """ ON CONFLICT ON CONSTRAINT
"upsert_test_table_pkey"
DO UPDATE SET
name=excluded.name,
encrypt_user_id=excluded.encrypt_user_id"""
Согласно комментарию :
в этой полезной нагрузке вставки нет столбца user_id, поэтому excluded.user_id есть null.
ON CONFLICT...DO UPDATE SET encrypt_user_id=excluded.user_id вставляет null вместо старого незашифрованного user_id: демо
CREATE TABLE upsert_test_table
(id SMALLSERIAL,
encrypt_user_id TEXT,
name TEXT,
user_id INT,
CONSTRAINT "upsert_test_table_pkey" PRIMARY KEY (id));
INSERT INTO upsert_test_table VALUES
(1,'enc1','Bob',11);
INSERT INTO upsert_test_table (id,encrypt_user_id,name) VALUES
(1,'enc101','Bob2'),--conflict here
(2,'enc2','Ted')
ON CONFLICT ON CONSTRAINT "upsert_test_table_pkey"
DO UPDATE SET
name = excluded.name, --name will be overwritten by incoming data
encrypt_user_id = excluded.user_id;--null because user_id not in incoming data
SELECT * FROM upsert_test_table;
-- id | encrypt_user_id | name | user_id
------+-----------------+------+---------
-- 1 | | Bob2 | 11
-- 2 | enc2 | Ted |
Из документации:
Предложения
SETиWHEREвON CONFLICT DO UPDATEимеют доступ к существующей строке, используя имя таблицы (или псевдоним), и к строке, предлагаемой для вставки, используя специальную таблицуexcluded.
Проблема здесь заключалась в том, что excluded было неправильно понято и вместо этого использовалось как ранее существовавшие данные, уже найденные в таблице после конфликта, в то время как на самом деле это данные, которые поступали, как полезная нагрузка этой вставки.
Этого должно быть достаточно, чтобы исправить одно неверное толкование и ссылаться на новое/входящее/вставленное значение как excluded, старое/существующее по имени целевой таблицы и столбцу:
upsert_sql = r"""insert into upsert_test_table
(id,encrypt_user_id,name)
values
""" + "\n, ".join(values_list) + """ ON CONFLICT ON CONSTRAINT
"upsert_test_table_pkey"
DO UPDATE SET
name = upsert_test_table.name, --keep the old name
encrypt_user_id = excluded.encrypt_user_id --overwrite with new
"""
Offtopic: Вы на 100% уверены, что этот кусок SQL не подвержен SQL-инъекциям?