CODEWITHSUNDEEP
pythonpandaspostgresql
Hryhorii Pavlenko
Hryhorii Pavlenko

Reputation: 3910

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

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?

Upvotes: 0

Views: 577

Answers (1)

user11064812
user11064812

Reputation: 

     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)

Upvotes: 1

Related Questions