pandas to_sql(if_exists='replace') should not commit after the drop query

[ma7555]

Code Sample, a copy-pastable example if possible

it is hard to generate a reproducible code for this problem as it would need parallel processes reading from a table, and another process that updates that table frequently.

Problem description

pandas/pandas/io/sql.py

Line 646 in d3e5485

self.pd_sql.drop_table(self.name, self.schema)

pandas/pandas/io/sql.py

Line 647 in d3e5485

self._execute_create()

The issue is that to_sql with (if_exists='replace') carries the two instructions separately and commits them serially into the database.

This causes 'table not found error' if multiple other processes are reading from the table as well.

The commit needs to happen after the second instruction only and not into the drop query.

[hdsheena]

This also means that if there is an error partway through the to_sql process, the table is dropped and then doesn't exist anymore..

The sql DROP TABLE command always results in an implicit commit. https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html