jake wong jake wong - 2 months ago 23
Python Question

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
.db
file 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?

Answer

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)