Using pandas.DataFrame.to_sql to UPDATE/REPLACE data

It’s pretty simple. You’ll have to first create a temporary table that matches your destination table. Then, use the pandads dataframe to replace the data in the temporary table with your new data (if_exists=’replace’).
dataframe.to_sql(‘mydatatable_temp’, conn, if_exists=’replace’, index=False)

From there you will use this little bit of SQL to update your destination data table with the newly loaded temp table data
conn.execute(‘REPLACE INTO mydatatable (SELECT * FROM mydatatable_temp);’)

If you found this at all useful, please comment and I will come back and make this a much better walkthrough. I also routinely answer questions and comments below.

3 thoughts on “Using pandas.DataFrame.to_sql to UPDATE/REPLACE data”

  1. Hello,
    Why not to use the direct approach?

    dataframe.to_sql(‘mydatatable, conn, if_exists=’replace’, index=False)

    I am trying it but it seems locked

    Reply

Leave a Comment