Search code examples
pythonpandassqlalchemydataframe

pandas.to_sql replace old data with new data based on 'unique id'

I have a pandas Dataframe, that I have put into sql with the below code.

code

df = pandas.read_csv(io.StringIO(r))
pandas.DataFrame.to_sql(df, name='Database count details', con=engine)

Sample data

UNIQUE id   name          refreshed_at    values
2449205     ABC           2014-01-10      22
26019260    DEF           2016-03-04      51
26019261    GHI (1333)    2016-03-04      0.55

My intentions are to run a code once a week and to replace the data in the .dbfile with that week's data if the unique id matches. If it doesn't match, it will append to the .db file.

How should I do this? Or is there a better way to perform this task?

Solution

  • There doesn't seem to be a feature to get through this easily. Currently, I just drop the entire table, and recreate a new one.. 

    meta = MetaData()
table_to_drop = Table('Database count details', 
                       meta, autoload=True, autoload_with=engine)
table_to_drop.drop(engine)