close

How do I perform an UPDATE of existing rows of a db table using a Pandas DataFrame?

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How do I perform an UPDATE of existing rows of a db table using a Pandas DataFrame in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

How do I perform an UPDATE of existing rows of a db table using a Pandas DataFrame?

  1. How do I perform an UPDATE of existing rows of a db table using a Pandas DataFrame?

    first delete those rows that are going to be “upserted”. This can be done in a loop, but it's not very efficient for bigger data sets (5K+ rows), so i'd save this slice of the DF into a temporary MySQL table:

  2. perform an UPDATE of existing rows of a db table using a Pandas DataFrame

    first delete those rows that are going to be “upserted”. This can be done in a loop, but it's not very efficient for bigger data sets (5K+ rows), so i'd save this slice of the DF into a temporary MySQL table:

Method 1

I think the easiest way would be to:

first delete those rows that are going to be “upserted”. This can be done in a loop, but it’s not very efficient for bigger data sets (5K+ rows), so i’d save this slice of the DF into a temporary MySQL table:

# assuming we have already changed values in the rows and saved those changed rows in a separate DF: `x`
x = df[mask]  # `mask` should help us to find changed rows...

# make sure `x` DF has a Primary Key column as index
x = x.set_index('a')

# dump a slice with changed rows to temporary MySQL table
x.to_sql('my_tmp', engine, if_exists='replace', index=True)

conn = engine.connect()
trans = conn.begin()

try:
    # delete those rows that we are going to "upsert"
    engine.execute('delete from test_upsert where a in (select a from my_tmp)')
    trans.commit()

    # insert changed rows
    x.to_sql('test_upsert', engine, if_exists='append', index=True)
except:
    trans.rollback()
    raise

PS i didn’t test this code so it might have some small bugs, but it should give you an idea…

Method 2

I was struggling with this before and now I’ve found a way.

Basically create a separate data frame in which you keep data that you only have to update.

df #updating data in dataframe

s_update = “” #String of updations

Loop through data frame.

for i in range(len(df)):
    s_update += "update your_table_name set column_name = '%s' where column_name = '%s';"%(df[col_name1][i], df[col_name2][i])

Now pass s_update to cursor.execute or engine.execute (wherever you execute SQL query)

This will update your data instantly.

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read