0

I have Postgresql table test_data as df1 dataframe like below-

|email_id| status |role
|abc@gmail.com | 1 |1
|abc@gmail.com | 1 |2
|def@gmail.com | 0 |2
|ghi@gmail.com | 1 |2
|mno@gmail.com | 3 |1
|pqr@gmail.com | 2 |1
|MNP@gmail.com | 1 |2

Another dataframe df2 as the updated one -

|email_id| status |role

|abc@gmail.com | 1 |1
|abc@gmail.com | 0 |2
|def@gmail.com | 2 |2
|ghi@gmail.com | 1 |2
|mno@gmail.com | 1 |1
|pqr@gmail.com | 0 |1
|MNP@gmail.com | 1 |2

I want to update POSTGRES table test_data fieldname status in df1 from df2 based on email_id and role.

Used below approach to update the test_data table -

# importing psycopg2 module
import psycopg2
 
# establishing the connection
conn = psycopg2.connect(
   database="postgres",
    user='postgres',
    password='password',
    host='localhost',
    port= '5432'
)

conn.autocommit=True
for i in df2.index:
    status = df2['status'][i].item()
    email_id = df2['email_id'][i]
    role = df2['role'][i].item()
    sql1 = "update test.test_data set status = %s where role=%s and email_id = %s"
   cur.execute(sql1,[status,role,email_id])
   
cur.close()
conn.close()

Dtypes of DF2 -

email_id  object
role      int64
status    int64

The data in Postgres table is not updating, its running with no errors. Please help .

deepu2711
  • 59
  • 1
  • 7
  • The immediate issue is that you have not read the docs [Parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries). If you had you would know it should be `cur.execute(sql1, [df2['status'],df2['email_id']])`. The second issue, I suspect, will be that `df2['status']` and `df2['email_id']` are not single values and `execute()` will not work. The third issue is that they are probably a Pandas object and will not be recognized by `psycopg2`. – Adrian Klaver Nov 04 '22 at 15:30
  • @AdrianKlaver can you please help me with the edited question ? – deepu2711 Nov 04 '22 at 16:04
  • That would be the third issue I mentioned, Panda(actually numpy) types. `psycopg2` does not know how to adapt them. You will need to convert them to standard Python types. – Adrian Klaver Nov 04 '22 at 16:09
  • @AdrianKlaver how to convert them to standard python types? – deepu2711 Nov 04 '22 at 16:11
  • 1
    This is really something you should be able to find for yourself. A quick search found this [numpy dtypes to native Python](https://stackoverflow.com/questions/9452775/converting-numpy-dtypes-to-native-python-types/11389998#11389998). So `type(np.int64(1234).item()) int` or in your case `df2['status'][i].item()`. – Adrian Klaver Nov 04 '22 at 17:15
  • @AdrianKlaver , It worked, but the data in the Postgres table is not updating. Can you please check the code above once ,I have put autocommit , then also data not updating. Please help. – deepu2711 Nov 04 '22 at 19:21
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249334/discussion-between-deepu2711-and-adrian-klaver). – deepu2711 Nov 04 '22 at 19:22
  • @Parfait and not so as `conn.autocommit=True` as shown in code above. – Adrian Klaver Nov 05 '22 at 13:50

0 Answers0