0

I have a table name stock with (item,date) as index in Postgres database which record monthly record. Then I have pandas dataframe name stock_df which calculate monthly stock. Now I need to append that stock_df in table stock in postgres in such way that.

If stock_df has same item and same date like in stock table, it should update table and if it is new record it should be append.

I tried this:-

stock_df.to_sql(name="stock", con=conn_postgres, if_exists='update', schema='arpan', index=False)

But this code check all the column and if it is dublicate it update or append the table which is different from what I desire My requirement is:- If stock_df item and date match the index of stock(item,date) then it should update data and if it didn't then it should append.

Arpan Ghimire
  • 183
  • 1
  • 1
  • 8
  • According to the [doc](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html), `if_exists` parameter refers to the existence of the table, not a record in it, and it doesn't have an `update` option. – Zegarek Feb 13 '23 at 11:12
  • then how can I solve this? – Arpan Ghimire Feb 13 '23 at 11:13
  • PostgreSQL supports a `MERGE` as well as an `INSERT...ON CONFLICT DO UPDATE` but I don't see pandas exposing those easily. It does offer a `method` parameter of `DataFrame.to_sql()`, where you can put your [version of the insert](https://pandas.pydata.org/docs/dev/user_guide/io.html#io-sql-method) as a callable. You can use the [`MERGE`](https://www.postgresql.org/docs/current/sql-merge.html) or [`INSERT...ON CONFLICT DO UPDATE`](https://www.postgresql.org/docs/current/sql-insert.html) in there. – Zegarek Feb 13 '23 at 11:19
  • Does this answer your question? [Insert into postgreSQL table from pandas with "on conflict" update](https://stackoverflow.com/questions/55187884/insert-into-postgresql-table-from-pandas-with-on-conflict-update) – Zegarek Feb 13 '23 at 11:26

0 Answers0