Getting error appending into mysql database

Question

I am running into a weird error trying to append dataframe to MySQL table using pandas to_sql function. I have not been able to find answer to this anywhere. Here is a test example:

test_df = pd.DataFrame(['a','b','c','d'], columns = ['char'])

with engine.begin() as connection:
    test_df.to_sql(name='test', con=connection, if_exists='append')

The above runs successfuly, I can see the table being created in my database.

new_df = pd.DataFrame(['e','f'], columns = ['char'])

with engine.begin() as connection:
    new_df.to_sql(name='test', con=connection, if_exists='append')

However, when I try to append more data, I get following error:

OperationalError: (MySQLdb._exceptions.OperationalError) (1050, "Table 'test' already exists")
[SQL: 
CREATE TABLE test (
    `index` BIGINT, 
    `char` TEXT
)

]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

This is very confusing. Since I did not see anyone encounter this error, could it be a bad installation of my packages. Any help will be greatly appreciated.

Answer 1

Thanks to the comment by Rouhollah. I made the "append' to work by replacing

engine = create_engine(f"mysql://{user}:{password}@{host}:{port}")

with

engine = create_engine(f"mysql://{user}:{password}@{host}:{port}/{database}")

previously, I was accessing database using engine.execute(f"USE {database}") which seems to break the append function.