Updating rows in PostgreSQL via Python - pandas.to_sql: update?

Question

I know that insert or update if key exists option for .to_sql() hasn't been implemented yet, so I'm looking for an alternative.

The first thing that comes to mind is to use the append option:

data.to_sql(
    "Dim_Objects",
    con=connection,
    if_exists="append",
    index=False
)

and remove duplicates in the database separately, after I inserted data:

DELETE FROM "Dim_Objects" a
      USING "Dim_Objects" b
      WHERE a."Code" = b."Code" 
        AND a."TimeStampUpdate" < b."TimeStampUpdate"

In this case, if there's a duplicate, I only keep the latest entry.

This approach seems to work but I hoped I could achieve the same using pandas directly.

Any ideas?

score 1 · Accepted Answer · 2020-08-19 13:52:14Z

1

     can you try? 
  
    data.to_sql('Dim_Objects', con=connection, if_exists='replace')
    
    sql = """
        UPDATE "different_table" AS f
        SET col1 = b.col1
        FROM your_table_name AS data
        WHERE a."Code" = b."Code"
    """
    
    with engine.begin() as conn:     
        conn.execute(sql)

edited Aug 19, 2020 at 13:52

answered Aug 19, 2020 at 13:44

user11064812

Sign up to request clarification or add additional context in comments.

2 Comments

Hryhorii Pavlenko Over a year ago

if_exists="replace" would drop my whole table and replace it with the data dataframe, which is not what I want; I want to append new rows and replace where key already exists, but also I want to keep all records that are already in the table

user11064812 Over a year ago

sorry, You can try wirting the df in pandas as temp table in your db, then using sql create the matching columns. this code was for it. I updated some of the code. I typed the same db name by mistake

Collectives™ on Stack Overflow

Updating rows in PostgreSQL via Python - pandas.to_sql: update?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related