如何在Postgres中使用CSV文件中的值更新所选行？

Question

如何在Postgres中使用CSV文件中的值更新所选行？

sqldatabasepostgresqlfile-iocsv

98

我正在使用Postgres，并希望进行一次重大的更新查询，该查询将从CSV文件中获取数据。假设我有一个包含（id，banana，apple）的表格。

我想运行一个更新语句，更改香蕉而不是苹果，每个新香蕉及其ID都在CSV文件中。

我试图查看Postgres网站，但里面的例子让我感到困惑。

- user519753

你不是在尝试从pgadmin3中执行这个操作，是吗？你可能需要一种脚本语言（例如Python等）。你还需要澄清你所说的“更新”的含义。我猜测你的CSV文件包含了数据库中可能存在或不存在的项目，你必须插入或更新它们 - 但仅当它们是香蕉时才这样做。但请澄清一下。 - user948581

3个回答

3

我遇到了同样的问题，但在这个解决方案中我遇到了一些困难。由于我不是超级用户，使用copy会出错。所以我找到了另一个解决方案。

我正在使用postgresql和pgadmin4。以下是我想到的解决方案。

创建一个新表并将水果表复制到新表中。

CREATE TABLE fruits_copy AS TABLE fruits WITH NO DATA;

将CSV文件中的数据导入新表(fruits_copy)。我使用的是pgadmin4，所以这里是如何导入细节。（可能会有所不同）。
从fruits_copy表更新fruits表。

UPDATE fruits SET banana = fruits_copy.banana FROM fruits_copy WHERE fruits.id = fruits_copy.id;

之后如果你想删除新表，可以直接删除它。

DROP TABLE fruits_copy;

- hakuna_matata

-1

你可以尝试下面这个用Python编写的代码，输入文件是你要更新到表格中的CSV文件。每行基于逗号拆分，因此对于每一行来说，row [0]是第一列下的值，row [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()

- Anupama V Iyengar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

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;

创建一个与现有表结构相匹配且没有约束条件的空临时表。

权限

在Postgres 10及以下版本中，SQL COPY需要超级用户权限才能执行。
从Postgres 11开始，还有一些预定义角色（以前称为“默认角色”）允许执行此操作。手册:

COPY只允许数据库超级用户或被授予pg_read_server_files、 pg_write_server_files或pg_execute_server_program其中之一角色的用户命名文件或命令[...]

psql元命令\copy适用于任何数据库角色。手册:

执行客户端复制。这是运行SQLCOPY命令的操作，但是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中切换psql元命令和SQL命令需要使用元命令\\。但是\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;