如何使用Python从CSV文件更新表?

Question

我的表中的示例数据是:

AccountKey   ParentAccountKey   CreatedOn                UpdatedOn
61           58                 2021-04-17 00:00:00.000  2021-05-17 00:00:00.000
101          51                 2021-04-17 00:00:00.000  2021-05-17 00:00:00.000
40           36                 2021-04-17 00:00:00.000  2021-05-17 00:00:00.000

我想从csv文件中更新表的第一行，其中包含数据:

AccountKey   ParentAccountKey   CreatedOn                UpdatedOn
61           97                 2021-04-17 00:00:00.000  2022-05-18 00:00:00.000

基本上，我想执行类似于:

update Tablename
set ParentAccountKey = 97,
Updatedon = CURRENT_DATE()
where AccountKey =61;

但是列的更新值应该来自csv文件，

，最好是动态的，这样代码可以在多个表中重用。

Answer 1

带有变量的脚本可以用于动态，通过SQL也可以做到。下面展示的例子，从stage.

更新多值。原表-

select * from TNAMEUPD;
+-----------+----------+
| PARENTKEY | CHILDKEY |
|-----------+----------|
|         1 |       10 |
|         2 |       20 |
|         3 |       30 |
+-----------+----------+

从stage更新多个表列。阶段子查询必须只返回一条记录。

update TNAMEUPD set parentkey=(select $1 from @test_stage t where t.$1=1234), 
CHILDKEY=(select $1 from @test_stage t where t.$1=1234) where parentkey=3;
+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      1 |                                   0 |
+------------------------+-------------------------------------+

对于动态环境变量可以使用-

set (pkey) = (select 2);
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
set (ckey) = (select 1234);
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

使用上述环境变量进行更新-

update TNAMEUPD set parentkey=(select $1 from @test_stage t where t.$1=$ckey), 
CHILDKEY=(select $1 from @test_stage t where t.$1=$ckey) where parentkey=$pkey;
+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      1 |                                   0 |
+------------------------+-------------------------------------+

表更新后-

select * from TNAMEUPD;
+-----------+----------+
| PARENTKEY | CHILDKEY |
|-----------+----------|
|         1 |       10 |
|      1234 |     1234 |
|      1234 |     1234 |
+-----------+----------+

还可以创建用于多个表的过程。下面是一个示例过程，可以根据需要修改过程代码中的参数列表和更新语句。

create or replace procedure upd_tab 
(table_name string,pkey number, ckey number,stage_name string)
returns integer
language sql as
$$
begin
execute immediate 'update '||table_name||' 
set parentkey = (select $1 from @'||stage_name ||' s 
where s.$1='||ckey||') 
where parentkey='||pkey;
return 0;
end;
$$
;