BUG: to_sql with sqlalchemy[mysql], if_exists='repalce' raise 'sqlalchemy.exc.NoSuchTableError'; if_exists='fail' raise 'Table already exists'

[UlionTse]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the master branch of pandas.

Reproducible Example

class Database:
    def __init__(self):
        self.save_schema = 'build_dwd'
        self.save_table = 'dwd_abtest_report'
        self.mysql_dwd_config = {
            'drivername': 'mysql+pymysql',
            'username': 'xxx',
            'password': 'xxx@#$xx',
            'host': 'am-xxx.ads.aliyuncs.com',
            'port': xxx,
        }
        self.mysql_engine_url = sqlalchemy.engine.URL.create(**self.mysql_dwd_config)
        self.mysql_dwd_engine = sqlalchemy.create_engine(self.mysql_engine_url)



    def get_mysql_data(self, sql):
        return pandas.read_sql(sql=sql, con=self.mysql_dwd_engine, chunksize=None)

    def write_mysql_data(self, df, if_replace=False):
        df.to_sql(
            con=self.mysql_dwd_engine,
            schema=self.save_schema,
            name=self.save_table,
            if_exists='replace' if if_replace else 'append',
            index=False,
            index_label='id',
            chunksize=10000,
        )

Issue Description

BUG: to_sql with sqlalchemy[mysql],
if_exists='repalce' raise 'sqlalchemy.exc.NoSuchTableError';
if_exists='fail' raise 'Table already exists'.

In fact, if_exists='repalce' has error. when I gave schema, but it unknow schema.

Expected Behavior

if_exists='repalce' should drop table, create table, and insert data.

Installed Versions

Details

INSTALLED VERSIONS

commit : 5f648bf
python : 3.8.5.final.0
python-bits : 64
OS : Darwin
OS-release : 19.6.0
Version : Darwin Kernel Version 19.6.0: Mon Aug 31 22:12:52 PDT 2020; root:xnu-6153.141.2~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : zh_CN.UTF-8
pandas : 1.3.2
numpy : 1.20.3
pytz : 2020.1
dateutil : 2.8.1
pip : 21.2.4
setuptools : 50.3.1.post20201107
Cython : 0.29.21
pytest : 6.1.1
hypothesis : None
sphinx : 4.1.2
blosc : None
feather : None
xlsxwriter : 1.3.7
lxml.etree : 4.6.3
html5lib : 1.1
pymysql : 1.0.2
psycopg2 : None
jinja2 : 2.11.3
IPython : 7.28.0
pandas_datareader: None
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 2021.05.0
fastparquet : None
gcsfs : None
matplotlib : 3.4.3
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.5
pandas_gbq : None
pyarrow : 5.0.0
pyxlsb : None
s3fs : None
scipy : 1.7.1
sqlalchemy : 1.4.23
tables : 3.6.1
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
numba : 0.54.0

[erfannariman]

-1 on this change. I think if_exists="replace" behaves as expected here. It actually tries to drop the table before inserting and since your table does not exist, it raises NoSuchTableError.

From the documenten:

replace: Drop the table before inserting new values.

[UlionTse]

@erfannariman Yes, but my table actually exists. Because then I use if_exists='fail', raise 'Table xxx already exists'.

[mroeschke]

Agreed -1 on the change here as well. This would be large breaking change for the replace behavior. Thanks for suggestion but since it hasn't gotten much support from the pandas team, closing.