Test for pd.to_sql column error if data contains -np.inf
State: closed

- [x] closes #34431 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Dtype Conversions
description: Unexpected or buggy dtype conversions
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
Hey everyone - checking in in case there's anything I can do to keep this PR moving forward!
Looks like there is a merge conflict and there are some CI failures in the Travis build
@mroeschke Ok, I've looked into that now.

It seems that the new test is failing:
```
=========================== short test summary info ============================
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemy::test_to_sql_with_negative_npinf[load_iris_data0-input0]
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemy::test_to_sql_with_negative_npinf[load_iris_data0-input1]
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemy::test_to_sql_with_negative_npinf[load_iris_data0-input2]
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemyConn::test_to_sql_with_negative_npinf[load_iris_data0-input0]
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemyConn::test_to_sql_with_negative_npinf[load_iris_data0-input1]
FAILED pandas/tests/io/test_sql.py::TestMySQLAlchemyConn::test_to_sql_with_negative_npinf[load_iris_data0-input2]
= 6 failed, 72578 passed, 1156 skipped, 1016 xfailed, 115 warnings in 1193.92s (0:19:53) =
The command "ci/run_tests.sh" exited with 1.
```
But when I run `pytest -q pandas/tests/io/test_sql.py` everything passes. Do by any chance you have an idea of what might be going on? 

Either way, what's the best way to proceed on this PR? Should I try to fix the problem in this PR (happy to carry on but I'll need some help on where to start debugging - not sure what to try right now) or mark the test as xfail?
Looks like this issue isn't fully solved for MySQL based on the test failures. 

If you would like to solve this issue for MySQL, that'd be great. It might need to be handled in `sql.py`.

Otherwise, we would also xfail the test for MySQL and leave the original issue open.
@mroeschke Sounds good - happy to work on fixing this for MySQL
Following up on the MySQL issue, the specific error we run into there is 
```
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'inf' in 'field list'")
```
I think this is basically because MySQL doesn't have a way to represent infinity. 

This came up in `mysqlclient-python` (see [issue report](https://github.com/PyMySQL/mysqlclient-python/issues/246)) where they raise an error when an input to MySQL contains `np.inf`.

I feel like for Pandas we want to handle this case, though. One solution could be to load `np.inf` as a string and make sure it's loaded as infinity when we load from MySQL to a `DataFrame`, but I think one might run into issues with datatype if we try to include a string in a numeric column.

Would love to hear your thoughts on how to move forward!
I think the original issue has the best suggestion to raise `ProgrammingError: inf can not be used with MySQL.` if it's possible to catch the error raised by SQLAlchemy.
I've added a working, but probably not production level, solution. It catches the error for the examples we're looking at in #34430  but I'm not 100% that the details are right
Looks ready to go - happy to work on any improvements, though!
Thanks @arw2019. Nice PR as always