I have a PostgreSQL database, which I interact with using SQLAlchemy in Python.
The database contains daily price data (including non-working days), starting from Jan-2005; the date column is the index for this database. The data can be reissued up to 5-days after the closing price for a security has been announced, so every day, I capture a 5-day rolling window of closing prices in a dataframe for each security, which I use to overwrite corresponding data in the db.
To do this, I use the below function:
def update_from_df(self): con = self._engine.connect() self._df.to_sql(self._table_name,con,if_exists="replace") con.close()
The above function deletes all data that was residing in the database (i.e. the data that does not have a corresponding index as the dataframe data) and replaces it with the far smaller dataset that was contained in the DF. This is not desired -- I merely want to overwrite data in the database whose corresponding data entry in the dataframe is non-identical.
In an attempt to rectify this, I wrote code that looks at the daterange of the dataframe (that contains updated data), deleting rows in the database that correspond to the daterange, copying the chunk of data from the dataframe, and finally appending this data to the database. This process is a bit clunky and I worry that with a larger data selection, performance will become hindered.
Instead of using replace in the code, the alternatives of
append do not help with this problem.
Is there a function that updates a PostgreSQL database using dataframe data that only has a corresponding index (which in this case would be a data entry that has the same date and price ticker column ID), while not touching all other database data -- something along the lines of the Pandas