python - How to insert a pandas dataframe to an already existing table in a database?

Question

I'm using sqlalchemy in pandas to query postgres database and then insert results of a transformation to another table on the same database. But when I do df.to_sql('db_table2', engine) I get this error message: ValueError: Table 'db_table2' already exists. I noticed it want to create a new table. How to insert pandas dataframe to an already existing table ?

df = pd.read_sql_query('select * from "db_table1"',con=engine)
#do transformation then save df to db_table2
df.to_sql('db_table2', engine)

ValueError: Table 'db_table2' already exists

question from:https://stackoverflow.com/questions/38610723/how-to-insert-a-pandas-dataframe-to-an-already-existing-table-in-a-database

Answer 1

make use of if_exists parameter:

df.to_sql('db_table2', engine, if_exists='replace')

or

df.to_sql('db_table2', engine, if_exists='append')

from docstring:

"""
if_exists : {'fail', 'replace', 'append'}, default 'fail'
    - fail: If table exists, do nothing.
    - replace: If table exists, drop it, recreate it, and insert data.
    - append: If table exists, insert data. Create if does not exist.
"""