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.

I don't understand why it would call create table again when I use 'append' argument — Abdul Moiz, Apr 24 '22 at 04:41
Does the proposed answer [here](https://stackoverflow.com/a/26766205/4516825) help you? (Do update your pandas or pass engine rather than the connection to the to_sql function. `engine = create_engine("mysql+mysqldb://:@[:]/") ` and `new_df.to_sql("test", engine, if_exists='append')`) — Rouhollah Joveini, Apr 24 '22 at 05:27

score 0 · Answer 1 · answered Apr 24 '22 at 18:43

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.

Getting error appending into mysql database

1 Answers1