I am trying to update multiple rows of specific columns of a dataframe df in postgresql using psycopg2. I have searched over SO and found hints here and here. But no luck.
Below is my sample df:
Bid_Doc_ID DocumentName Is_Uploaded ModifiedDatetime BidderID
1 EMD Scan Y 2021-01-13 13:03:39.938260 325
-1 N 2021-01-13 13:03:39.938260 325
-1 N 2021-01-13 13:03:39.938260 325
1 EMD Scan Y 2021-01-13 13:03:39.938260 326
1 EMD Scan Y 2021-01-13 13:03:39.938260 327
2 Tender Fee Y 2021-01-13 13:03:39.938260 327
Below is the query I am using:
sql = """UPDATE table t
SET "Bid_Doc_ID" = e.Bid_Doc_ID,
"DocumentName" = e.DocumentName,
"Is_Uploaded" = e.Is_Uploaded,
"ModifiedDatetime" = e.ModifiedDatetime
FROM (VALUES %s) AS e(Bid_Doc_ID,DocumentName,Is_Uploaded,ModifiedDatetime,BidderID)
WHERE t."BidderID" = e.BidderID ;"""
Then I am using the following python codes for pushing data from df:
tup = list(zip(*map(df.get,df))) <----this converts the dataframe into a tuple
conn = psycopg2.connect(connection_string)
curr = conn.cursor()
try:
execute_values(curr,sql,tup)
conn.commit()
curr.close()
except (Exception, psycopg2.DatabaseError) as error:
err = str(error)
print("Error: %s" % error)
return err
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
After the above piece of code is run, in the DB I do not see the expected result. In fact I am seeing the entire DocumentName field is blank, all Is_Uploaded is set to 'N' and all Bid_Doc_ID is set to -1.
What am I missing here?
