CODEWITHSUNDEEP
pythonsqlpandas
Hana
Hana

Reputation: 1470

Update one column in sql from a DataFrame in Python

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?

Upvotes: 0

Views: 3409

Answers (1)

BENY
BENY

Reputation: 323226

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)

Upvotes: 2

Related Questions