将 TXT 加载到 postgres 数据库中，不存在的列填充为 null

Question

我有点像python新手，所以如果有一个简单的解决方案我没有看到，请原谅我。我有大量的 txt 文件 (~280(，我需要将它们读入 postgres 数据库中的表中。该表的结构使其包含 txt 文件中存在的所有可能的列/变量。考虑下面的例子...

file1.txt ：

ID | Var_A | Var_B
1  | Apple | Dog
2  | Peach | Bird

file2.txt ：

ID | Var_A | Var_C
5  | Grape | Cat      
3  | Apple | Dog   

file3.txt ：

ID | Var_B | Var_C
7  | Apple | Cat
6  | Peach | Bird

理想情况下，我希望我的表包含 txt 文件中的所有数据，其中 txt 文件中不存在的列/变量被记录为空白或 null。(为了演示和清晰起见，我在下面将它们写为 null。

postgres_table：

ID | Var_A | Var_B | Var_C
1  | Apple | Dog   | NULL
2  | Peach | Bird  | NULL
3  | Apple | NULL  | Dog
5  | Grape | NULL  | Cat
6  | NULL  | Peach | Bird
7  | NULL  | Apple | Cat

鉴于文件数量众多，此过程需要自动化。我一直在python中使用psycopg2来完成这项任务，并且之前做过许多尝试，但没有一次成功。我相信COPY查询将是最有效的(有数百万行数据(，但是在尝试复制txt文件中不存在的列时，我遇到了困难。

cursor = db.cursor()
query = "COPY postgres_table FROM '{}'(DELIMITER('|'));".format(~/file1.txt)
cursor.execute(query)
db.commit()

此查询生成以下错误：

psycopg2.DataError: missing data for column "Var_C"

也许有人能看到我没有看到的东西。我也会对这项任务的其他潜在解决方案感兴趣。

Answer 1

一种选择是将文本文件读取为字符串，然后修改字符串以有效地添加 null，然后将其传递给 format 方法而不是文件。

因此，您可以读取文件的第一行，查看缺少哪些字段(将其与数据库表中的字段进行比较(，并在缺少字段的点在字符串的每一行中添加"|"。

Answer 2

您可以使用临时表将数据从所有文件加载到其中，然后使用 plpgsql 甚至 plpython 对它执行所需的操作。

例如：

-- Destination table
create table if not exists dest(
  id int,
  var_a varchar,
  var_b varchar,
  var_c varchar);
begin; -- Start transaction
-- Temporary table to load data from all files
create temporary table tt(
  tt_order serial,
  tt_id varchar,
  tt_var_1 varchar,
  tt_var_2 varchar,
  tt_file varchar)
on commit drop; -- It will be drooped at the end of transaction
-- Load data, header will be included
copy tt(tt_id, tt_var_1, tt_var_2) from '~/file1.txt' (delimiter '|', header false);
update tt set tt_file = '~/file1.txt' where tt_file is null;
copy tt(tt_id, tt_var_1, tt_var_2) from '~/file2.txt' (delimiter '|', header false);
update tt set tt_file = '~/file2.txt' where tt_file is null;
copy tt(tt_id, tt_var_1, tt_var_2) from '~/file3.txt' (delimiter '|', header false);
update tt set tt_file = '~/file3.txt' where tt_file is null;
-- Remove spaces
update tt set
  tt_id = trim(tt_id),
  tt_var_1 = trim(tt_var_1),
  tt_var_2 = trim(tt_var_2);
select * from tt;
-- Result is
/*
 tt_order | tt_id | tt_var_1 | tt_var_2 |   tt_file   
----------+-------+----------+----------+-------------
        1 | ID    | Var_A    | Var_B    | ~/file1.txt
        2 | 1     | Apple    | Dog      | ~/file1.txt
        3 | 2     | Peach    | Bird     | ~/file1.txt
        4 | ID    | Var_A    | Var_C    | ~/file2.txt
        5 | 5     | Grape    | Cat      | ~/file2.txt
        6 | 3     | Apple    | Dog      | ~/file2.txt
        7 | ID    | Var_B    | Var_C    | ~/file3.txt
        8 | 7     | Apple    | Cat      | ~/file3.txt
        9 | 6     | Peach    | Bird     | ~/file3.txt
(9 rows)
*/
-- Move data to destination table
do language plpgsql $$
declare
  r record;
  q text;
begin
  for r in select * from tt order by tt_order loop
    if lower(r.tt_id) = 'id' then -- If header from next file
      -- Build insert query
      q := 'insert into dest (id, ' || lower(r.tt_var_1) || ', ' || lower(r.tt_var_2) || ') values ($1::int, $2, $3)';
    else
      -- Insert data record
      execute q using r.tt_id, r.tt_var_1, r.tt_var_2;
    end if;
  end loop;
end; $$;
commit;
select * from dest;
-- Result is
/*
 id | var_a | var_b | var_c 
----+-------+-------+-------
  1 | Apple | Dog   | 
  2 | Peach | Bird  | 
  5 | Grape |       | Cat
  3 | Apple |       | Dog
  7 |       | Apple | Cat
  6 |       | Peach | Bird
(6 rows)
*/