Update one column in sql from a DataFrame in Python

Question

Using pandas, I read in a query from sql using something like this:

df = pd.read_sql(query, engine)

This dataframe is quite large and I have updated one column called 'weight' by doing some calculations.

What I want is to update that column with my new column in the DataFrame. How can I do this so the column update corresponds to the correct rows?

For example, say I have a sample df like this: df =

 type color  size weight
 10  green  12     13
 23  green  40     10 
 16    red  40     15

where df['weight'] is my newly calculated column that I want to update in the db.

The db looks like this currently: Table =

 type color  size weight
 10  green  12     null
 23  green  40     null 
 16    red  40     null

My expected sql updated table is this:

 type color  size weight
 10  green  12     13
 23  green  40     10 
 16    red  40     15

I'm using psychopg2 and below is my thought process for the code:

UPDATE table 
SET weight = df.weight
WHERE table.type = df.type
AND table.size = df.size
AND table.color = df.color

What is the best way to write the update code in Python to update the database?

Yea exactly, but since the df is so large, I want to make sure the correct 'weight' value corresponds to the correct row. The df is only a part of the table, so I don't want to replace the entire table to df. Sorry for the confusion I hope that makes more sense — Hana
– Hana, Commented May 7, 2018 at 0:44

BENY · Accepted Answer · 2018-05-07 00:47:34Z

2

You can try wirting the df in pandas as temp table in your db, then using sql create the matching columns

connect=('yourconnectionfordb')
df.to_sql('test', connect, if_exists='replace')

query = "UPDATE table AS f" + \
      " SET weight = test.weight" + \
      " FROM test AS t" + \
      " WHERE f.type = t.type " + \
      " AND f.size = t.size" + \
      " AND f.color = t.color"

with connect.begin() as conn:
   conn.execute(query)

answered May 7, 2018 at 0:47

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Update one column in sql from a DataFrame in Python

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related