python - SQL/ python : Transform data from csv and into table with different schema with condition

Question

所以，我有一个包含如下数据的 csv 文件:

id       type      sum_cost         date_time
--------------------------------------------------
a1        pound     500        2019-04-21T10:50:06    
b1        euro      100        2019-04-21T10:40:00    
c1        pound     650        2019-04-21T11:00:00    
d1        usd       410        2019-04-21T00:30:00     

我想做的是将这些数据插入到数据库表中，其中的架构与 csv 不同，因此表中的列如下所示:

_id , start_time, end_time, pound_cost, euro_cost, count

我从 csv 插入到此表中，id = id，start_time 是 date_time - 1 小时，end_time 是 date_time - 30 分钟。对于 pound_cost 和 euro_cost，如果 type 是英镑，则将其 sum_cost 中的值插入到 pound_cost 并将 0 添加到 euro_cost。欧元的方式相同。并将 1 添加到 count 列。

所以，表的结果会是这样的:

_id   start_time           end_time              pound_cost  euro_cost  count
-----------------------------------------------------------------------------
 a1  2019-04-21T09:50:06  2019-04-21T10:20:06      500           0        1
 b1  2019-04-21T09:40:06  2019-04-21T10:10:00       0           100       1
 c1  2019-04-21T10:00:00  2019-04-21T10:30:00      650           0        1
 d1  2019-04-20T23:30:00  2019-04-21T00:00:00       0           410       1

那么，我应该如何将数据插入表中，这与我如何将值从 csv 转换为表有关。这是我第一次使用 postgresql，我并没有经常使用 sql，所以我想知道是否有一个函数可以做到这一点。或者，如果没有，我如何使用 Python 转换数据并将它们插入到表中。

谢谢。

Answer 1

正如评论中所讨论的那样，您可以通过使用 COPY 命令和一个临时表来保存文件中的数据来轻松完成此操作。

用你的 CSV 结构创建一个临时表，注意所有的数据类型都是文本。这使得复制速度更快，因为验证被最小化了。

CREATE TEMP TABLE  temptable 
      ( id TEXT ,
        TYPE TEXT,
        sum_cost TEXT ,
        date_time TEXT );

使用COPY 从文件加载到这个表中。如果您从服务器加载文件，请使用 COPY，如果它在客户端计算机中，请使用 psql 的 \COPY。如果需要，将其适本地更改为不同的分隔符。

\COPY temptable from '/somepath/mydata.csv'  with delimiter ',' CSV HEADER;

现在，只需使用表达式运行 INSERT INTO .. SELECT 即可进行各种转换。

INSERT INTO maintable (
          _id,start_time,end_time,pound_cost,euro_cost,count )
SELECT id,
     date_time::timestamp - INTERVAL '1 HOUR', 
     date_time::timestamp - INTERVAL '30 MINUTES',
  CASE type
      WHEN 'pound' THEN sum_cost::numeric
     ELSE 0 END,
  CASE type when 'euro' THEN sum_cost::numeric --you have not specified what 
                                               --happens to USD,use it as required.
     ELSE 0 END, 
   1 as count       -- I have hardcoded it based on your info, not sure what it 
                    --actually means
from temptable t; 

现在，数据在你的主表中

select * from maintable;

 _id |     start_time      |      end_time       | pound_cost | euro_cost | count
-----+---------------------+---------------------+------------+-----------+-------
 a1  | 2019-04-21 09:50:06 | 2019-04-21 10:20:06 |        500 |         0 |     1
 b1  | 2019-04-21 09:40:00 | 2019-04-21 10:10:00 |          0 |       100 |     1
 c1  | 2019-04-21 10:00:00 | 2019-04-21 10:30:00 |        650 |         0 |     1
 d1  | 2019-04-20 23:30:00 | 2019-04-21 00:00:00 |          0 |         0 |     1