How to update a Table from a CSV file using Python?

user_13286893 0
Viewed 142

The sample data in my table is:

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

I want to update the 1st row of the table from a csv file which has data:

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

Basically, I want to execute something similar to :

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

But the updated values of the columns should come from the csv file,

and preferably dynamic so that code can be reused for multiple tables.

snowflake-cloud-data-platform csv python
1 Answers

Either a script with variables can be used for dynamism or, via SQL too it can be done. Below shown example, to update for multi-value from stage.

Original Table -

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

Update multiple table columns from stage. The stage sub-query must return only single record.

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 |
+------------------------+-------------------------------------+

For dynamism environment varibales can be used -

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

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

Give update using above environment variables -

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 |
+------------------------+-------------------------------------+

Table after updates -

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

A procedure can also be created for use with multiple tables. Below is a sample procedure, modify parameter-list and update statement inside procedure code as per need.

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;
$$
;
user_139153130
Related