Как обновить выбранные строки значениями из файла CSV в Postgres?

Я использую Postgres и хотел бы сделать большой запрос на обновление, который будет взят из CSV-файла, скажем, я получил таблицу, которая получила (id, banana, apple),

Я хотел бы запустить обновление, которое изменяет бананы, а не яблоки, каждый новый банан и их идентификатор будут в файле CSV.

Я попытался посмотреть на сайте Postgres, но примеры убивают меня.

Источник

3 ответа

Решение

Я мог бы COPY файл во временную таблицу и обновите фактическую таблицу оттуда. Может выглядеть так:

CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- but see below

COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);

UPDATE tbl
SET    banana = tmp_x.banana
FROM   tmp_x
WHERE  tbl.id = tmp_x.id;

DROP TABLE tmp_x; -- else it is dropped at end of session automatically

Если импортированная таблица совпадает с таблицей, которая должна быть точно обновлена, это может быть удобно:

CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;

Создает пустую временную таблицу, соответствующую структуре существующей таблицы, без ограничений.

привилегии

SQL COPY требует привилегий суперпользователя для этого. ( Руководство):

COPY присвоение имени файлу или команде разрешено только суперпользователям базы данных, поскольку позволяет читать или записывать любой файл, к которому у сервера есть права доступа.

Метакоманда psql \copyработает для любой роли БД. Руководство:

Выполняет внешнюю (клиентскую) копию. Это операция, которая запускает команду SQL COPY, но вместо того, чтобы сервер считывал или записывал указанный файл, psql считывает или записывает файл и направляет данные между сервером и локальной файловой системой. Это означает, что доступность файлов и привилегии принадлежат локальному пользователю, а не серверу, и никаких привилегий суперпользователя SQL не требуется.

Область действия временных таблиц ограничена одним сеансом с одной ролью, поэтому вышеприведенное должно выполняться в одном сеансе psql:

CREATE TEMP TABLE ...;
\copy tmp_x FROM '/absolute/path/to/file' (FORMAT csv);
UPDATE ...;

Если вы пишете это в команде bash, обязательно оберните все это одним вызовом psql. Подобно:

echo 'CREATE TEMP TABLE tmp_x ...; \copy tmp_x FROM ...; UPDATE ...;' | psql

Обычно вам нужна мета-команда \\ переключаться между мета-командами psql и SQL-командами в psql, но \copy является исключением из этого правила. Руководство снова:

специальные правила разбора применяются к \copy мета-команды. В отличие от большинства других метакоманд, весь остаток строки всегда считается аргументом \copy и ни переменная интерполяция, ни разложение по кавычкам не выполняются в аргументах.

Большие столы

Если таблица импорта велика, она может заплатить, чтобы увеличить temp_buffers временно для сеанса (первое в сеансе):

SET temp_buffers = '500MB';  -- example value

Добавьте индекс во временную таблицу:

CREATE INDEX tmp_x_id_idx ON tmp_x(id);

И беги ANALYZE вручную, так как временные таблицы не покрываются автоочисткой / автоанализом.

ANALYZE tmp_x;

Связанные ответы:

Источник

У меня была такая же проблема. Но в этом решении я обнаружил некоторые трудности. Поскольку я не был суперпользователем, использование копии дает ошибку. Так что я нашел альтернативное решение для моей проблемы.

Я использую postgresql и pgadmin4 . вот решение, с которым я пришел.

  1. Создайте новую таблицу и скопируйте таблицу фруктов в новую таблицу.
       CREATE TABLE fruits_copy AS TABLE fruits WITH NO DATA;

  1. Импортируйте данные файла CSV в новую таблицу (fruits_copy). Я использую pgadmin4, так что вот как импортировать данные . (Может отличаться).

  2. Обновите таблицу фруктов из таблицы fruit_copy.

ОБНОВЛЕНИЕ фрукты УСТАНОВИТЕ банан = Fruits_copy.banana ОТ Fruit_copy WHERE Fruits.id = Fruit_copy.id;

  1. После этого, если вы хотите удалить новую таблицу, вы можете просто удалить ее.

УДАЛИТЬ ТАБЛИЦУ fruit_copy;

Источник

Вы можете попробовать приведенный ниже код, написанный на python, входной файл - это файл csv, содержимое которого вы хотите обновить в таблицу. Каждая строка разделяется на основе запятой, поэтому для каждой строки строка [0] - это значение в первом столбце, строка [1] - это значение во втором столбце и т. Д.

    import csv
    import xlrd
    import os
    import psycopg2
    import django
    from yourapp import settings
    django.setup()
    from yourapp import models


    try:
       conn = psycopg2.connect("host=localhost dbname=prodmealsdb 
       user=postgres password=blank")
       cur = conn.cursor()

       filepath = '/path/to/your/data_to_be_updated.csv'
       ext = os.path.splitext(filepath)[-1].lower()
       if (ext == '.csv'): 
          with open(filepath) as csvfile:
          next(csvfile)
          readCSV = csv.reader(csvfile, delimiter=',')
          for row in readCSV:
              print(row[3],row[5])
              cur.execute("UPDATE your_table SET column_to_be_updated = %s where 
              id = %s", (row[5], row[3]))
              conn.commit()
          conn.close()
          cur.close()

    except (Exception, psycopg2.DatabaseError) as error:
    print(error)
    finally:
    if conn is not None:
      conn.close()
Источник
Другие вопросы по тегам