ENH: Pluggable SQL performance via new SQL `engine` keyword
State: closed

- [x] xref, but is first step in implementing #36893
- [x] tests added and passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry (in the docstring)
- [ ] whatsnew entry (in the docs)

This is the first step in implementing an `engine` backend to the SQL I/O functions, as discussed in #36893. This PR is simply to refactor the existing SQLAlchemy parts to make them extensible for an external engine.

No new engines were added at this stage. No new tests were added, only verified that existing tests fail ("test behavior, not implementation" suggests that we don't need to test internal refactoring)

Lots of the code was borrowed from the Parquet I/O implementation.

Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
weird, looks like CI only has one error but it's totally unrelated - something about IPython code blocks in the docs, but it's in the Parquet section, nothing to do with this PR.

I can't reproduce the error locally.

```bash
(base) root@eda624c2c1ba:/workspaces/pandas-josh/doc# grep -B10 \"^<<<-------------------------------------------------------------------------$\" sphinx.log
(base) root@eda624c2c1ba:/workspaces/pandas-josh/doc# 
```
@pandas-dev/pandas-core if any comments on the structure.
Made all requested changes except the test, as noted above
Weird CI failure, unrelated to my changes - something to do with `inf` in MySQL - but I think it should be good to merge now, can you pls take a look? Thanks. @jreback 
> lgtm ping on green

ok will do, thanks!
The same random CI error for the nan/inf tests in MySQL... anyone know how to resolve? Pretty sure it has nothing to do with my changes
> The same random CI error for the nan/inf tests in MySQL... anyone know how to resolve? Pretty sure it has nothing to do with my changes

this *should* be xfailed already, you have merged master?
> > The same random CI error for the nan/inf tests in MySQL... anyone know how to resolve? Pretty sure it has nothing to do with my changes
> 
> this _should_ be xfailed already, you have merged master?

Pretty sure I did. 

This is the offending test, permalinked from my PR branch:

https://github.com/yehoshuadimarsky/pandas/blob/c34c97b7f70297223ae48cc7b3893a824eb273e6/pandas/tests/io/test_sql.py#L2029-L2051

And here's the permalink from my branch of the code that raises the error:

https://github.com/yehoshuadimarsky/pandas/blob/c34c97b7f70297223ae48cc7b3893a824eb273e6/pandas/io/sql.py#L1342-L1351

Although in fairness, I moved that code out of `to_sql` and into the `SQLAlchemyEngine` class. But still can't figure out why it's failing, can't reproduce locally. Anyone able to help?
@yehoshuadimarsky I think CI is because ```-np.inf``` raises a different error than ```np.inf``` in PyMySQL. Which I think is ```(1054, "Unknown column 'infe0' in 'field list'")```. 
This then is not caught by 
https://github.com/pandas-dev/pandas/blob/c34c97b7f70297223ae48cc7b3893a824eb273e6/pandas/io/sql.py#L1345-L1351
when it should be. 
This was caught previously by 
https://github.com/pandas-dev/pandas/blob/49c779118c2d473c60565d9010e119bb62d06f74/pandas/io/sql.py#L1595-L1599
(code from master removed in this PR, this code seems a little too broad to me, and probably needs to be narrowed down.)

Can you try catching ```(1054, "Unknown column 'infe0' in 'field list'")``` too? 
> @yehoshuadimarsky I think CI is because `-np.inf` raises a different error than `np.inf` in PyMySQL. Which I think is `(1054, "Unknown column 'infe0' in 'field list'")`.
> This then is not caught by
> https://github.com/pandas-dev/pandas/blob/c34c97b7f70297223ae48cc7b3893a824eb273e6/pandas/io/sql.py#L1345-L1351
> 
> 
> when it should be.
> This was caught previously by
> https://github.com/pandas-dev/pandas/blob/49c779118c2d473c60565d9010e119bb62d06f74/pandas/io/sql.py#L1595-L1599
> 
> 
> (code from master removed in this PR, this code seems a little too broad to me, and probably needs to be narrowed down.)
> Can you try catching `(1054, "Unknown column 'infe0' in 'field list'")` too?

Thanks!! So because it's a regex, can I just change it to `msg = "(1054, \"Unknown column 'inf(e0)?' in 'field list'\")"` (adding the `(e0)?` as an optional part)? I tested it out here https://regex101.com/r/cBUuvd/1
Nope CI fix didn't work 😢 
IIRC we had an xfail in place for this partcular test. go ahead an dput it back and can be tackled there.
@jreback This is not a flaky test. This is failing because we are not raising the correct error message. See #34431. Xfailed case is also xfailed here I think and is not the problem. 
@yehoshuadimarsky We're a little closer now with 2 tests failing instead of 4. :) You need to also catch ```ProgrammingError: inf can not be used with MySQL```. Why? See https://github.com/PyMySQL/mysqlclient/issues/246, where they actually catch the inf case and raise a nice error. That appears in our CI with the latest pymysql, but with old pymysql(e.g. 0.8.1 where they don't raise a nice error), you need to catch original. ~~(```OperationalError: (1054, "Unknown column ... ```)~~

EDIT: Please disregard second half of comment. Accidentally copy pasted wrong thing. 
> @jreback This is not a flaky test. This is failing because we are not raising the correct error message. See #34431. Xfailed case is also xfailed here I think and is not the problem.
> @yehoshuadimarsky We're a little closer now with 2 tests failing instead of 4. :) You need to also catch `ProgrammingError: inf can not be used with MySQL`. Why? See [PyMySQL/mysqlclient#246](https://github.com/PyMySQL/mysqlclient/issues/246), where they actually catch the inf case and raise a nice error. That appears in our CI with the latest pymysql, but with old pymysql(e.g. 0.8.1 where they don't raise a nice error), you need to catch original. (`OperationalError: (1054, "Unknown column ... `)

Great. So I catch these 2?
- `OperationalError: (1054, "Unknown column 'inf' in 'field list'")`
- `OperationalError: (1054, "Unknown column 'infe0' in 'field list'")`

Basically, just add the prefix `OperationalError: `?
@yehoshuadimarsky The last one to catch is ```ProgrammingError: inf can not be used with MySQL```. You already caught the other two. 
Complete list should be 

OperationalError: (1054, "Unknown column 'inf' in 'field list'")
OperationalError: (1054, "Unknown column 'infe0' in 'field list'")
ProgrammingError: (pymysql.err.ProgrammingError) inf can not be used with MySQL

Sorry if I was unclear before. You can also test locally by running the first two cases in the OP in #34431.
```
pandas.DataFrame({'foo': [numpy.inf]}).to_sql('foobar1', engine) #ProgrammingError: inf can not be used with MySQL
pandas.DataFrame({'foo': [-numpy.inf]}).to_sql('foobar2', engine) # OperationalError: (1054, "Unknown column 'infe0' in 'field list'")
```
Both cases need to raise ```ValueError: inf cannot be used with MySQL```. (Third is xfailed).
If this doesn't work, I guess just use the original fix. 
https://github.com/pandas-dev/pandas/blob/49c779118c2d473c60565d9010e119bb62d06f74/pandas/io/sql.py#L1595-L1599

EDIT: Was actually sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) inf can not be used with MySQL and I was wrong here. See my later review/suggestion. 
For the life of me can't figure this out: https://stackoverflow.com/questions/67358166
@lithomas1 shoot I still can't get the CI to pass, any suggestions?
@lithomas1 nope still failed CI
@yehoshuadimarsky Yay, looks like the CI fix worked finally. I think the last thing to do is to evaluate @xhochy 's proposal. I'm not a core team member so I'll leave this to @jreback and @charlesdong1991. 
> @yehoshuadimarsky Yay, looks like the CI fix worked finally. I think the last thing to do is to evaluate @xhochy 's proposal. I'm not a core team member so I'll leave this to @jreback and @charlesdong1991.

Thanks for all your help!
thanks @yehoshuadimarsky very nice!
@yehoshuadimarsky can you do a followup to add a whatsnew note / update io.rst if needed anywhere.
> @yehoshuadimarsky can you do a followup to add a whatsnew note / update io.rst if needed anywhere.

Yes, just for this change? There's not much here that changed to the external user, it's just refactoring to support more future engines.
yeah just a quick one for the addition of the option (even though it doesn't do anything different yet)