I am trying to replace an existing mySQL table with pandas, using SQLalchemy. However, it fails. I do get the following error. When appending with the same request, I succeed.
# This fails
tablename = 'table_name'
connection = create_engine(f"mysql+mysqlconnector://{user}:{pw}@{host}/{db}")
df.to_sql(tablename, connection, if_exists='replace', index=False)
When using if_exists='append' it works.
The error message
Traceback (most recent call last):
File "/miniforge3/lib/python3.9/code.py", line 90, in runcode
exec(code, self.locals)
File "<input>", line 1, in <module>
File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/_pydev_bundle/pydev_umd.py", line 198, in runfile
pydev_imports.execfile(filename, global_vars, local_vars) # execute the script
File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"\n", file, 'exec'), glob, loc)
File "test.py", line 5, in <module>
wise.update_table()
File "wise.py", line 547, in update_table
season_table.to_sql(tablename, connection, if_exists='replace', index=False)
File "/miniforge3/lib/python3.9/site-packages/pandas/core/generic.py", line 2951, in to_sql
return sql.to_sql(
File "/miniforge3/lib/python3.9/site-packages/pandas/io/sql.py", line 697, in to_sql
return pandas_sql.to_sql(
File "/miniforge3/lib/python3.9/site-packages/pandas/io/sql.py", line 1729, in to_sql
table = self.prep_table(
File "/miniforge3/lib/python3.9/site-packages/pandas/io/sql.py", line 1628, in prep_table
table.create()
File "/miniforge3/lib/python3.9/site-packages/pandas/io/sql.py", line 835, in create
self.pd_sql.drop_table(self.name, self.schema)
File "/miniforge3/lib/python3.9/site-packages/pandas/io/sql.py", line 1787, in drop_table
self.meta.reflect(bind=self.connectable, only=[table_name], schema=schema)
File "/miniforge3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4848, in reflect
raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine(mysql+mysqlconnector://{user}:{pw}@{host}/{db}): (table_name)