Table a: Appending and Updating Rows with Primary Keys

At a glance

The community member has a table, tableA, which is the final output of their pipeline. The table already contains primary keys. The community member wants to append new rows with unique primary keys, and update existing rows with the same primary keys. They tried using if_exists='replace' in the save_args, but this deleted the whole table and only stored the current results. Using if_exists='append' resulted in duplicated results being inserted despite the primary keys. The community member has solved the issue by ensuring that the primary keys are created after the first insertion to the table. When they experimented with replace, the whole table seems to have lost the primary key definition, and it was fixed when they redefined the primary keys.

Useful resources

AAfiq Johari

tableA:
  type: pandas.SQLTableDataset
  table_name: tableA
  load_args:
    schema: my.dev
  save_args:
    schema: my.dev
    if_exists: "append"
    index: False

<strike> I have a table, TableA, which is the final output of my pipeline. The table already contains primary keys. If the primary key values are not yet in the table, I want to append the new rows. However, for rows where the primary key values already exist, I want to update those rows with the new results. What would be the right save_args to use in this case? I tried 'replace' for if_exists, but this keeps deleting the whole table and only the current results are stored. If I use 'append', despite the primary keys, duplicated results will still be inserted into the table </strike><strike>https://docs.kedro.org/en/0.18.14/kedro_datasets.pandas.SQLTableDataset.html#kedro_datasets.pandas.SQLTableDataset</strike>

1 comment

AAfiq Johari

Solved. Basically, I have to ensure primary keys are created after the first insertion to the table.

When I experimented with replace, the whole table seems to have lost the primary key definition. And it's fixed when I redefine the primary keys

Add a reply

Join the Kedro community

Table a: Appending and Updating Rows with Primary Keys