Repository: pandas-dev/pandas
Pull request: Test for pd.to_sql column error if data contains -np.inf
- [x] closes #34431 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
BUG: pandas.to_sql raises unexpected column error if data contains -numpy.inf
- [x] I have checked that this issue has not already been reported.

- [x] I have confirmed this bug exists on the latest version of pandas.

- [ ] (optional) I have confirmed this bug exists on the master branch of pandas.

---
#### Code Sample, a copy-pastable example

```python
import pandas
import numpy
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'")
pandas.DataFrame({'foo': [-numpy.inf], 'infe0':['bar']}).to_sql('foobar3', engine) # inserts (foo:Null, infe0: 'bar') into db

```

#### Problem description

I'd expect consistent behaviour between `numpy.inf` and `-numpy.inf`. Raising ProgrammingError in both cases is clear for the user. Furthermore, the query send should **not** interpret -inf as field `infe0`.

#### Expected Output
`ProgrammingError: inf can not be used with MySQL` in all 3 cases.
#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit: None
python: 3.7.1.final.0
python-bits: 64
OS: Linux
OS-release: 4.19.76-linuxkit
machine: x86_64
processor: 
byteorder: little
LC_ALL: None
LANG: C.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: None
pip: 20.0.2
setuptools: 40.6.2
Cython: None
numpy: 1.15.4
scipy: None
pyarrow: 0.11.1
xarray: None
IPython: 7.13.0
sphinx: None
patsy: None
dateutil: 2.8.1
pytz: 2019.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: 4.4.1
bs4: None
html5lib: None
sqlalchemy: 1.3.11
pymysql: None
psycopg2: None
jinja2: 2.11.2
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

</details>

Hints from issue discussion:
This runs fine on the 1.0. master.

The complete snippet  I ran is:
```python
import pandas
import numpy

from sqlalchemy import create_engine

engine = create_engine('sqlite://', echo=False)

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'")
pandas.DataFrame({'foo': [-numpy.inf], 'infe0':['bar']}).to_sql('foobar3', engine) # inserts (foo:Null, infe0: 'bar') into db
```
<details>
  <summary><b>Output of pd.show_versions()</b></summary>

INSTALLED VERSIONS
------------------
commit           : 62c7dd3e771d5dc2921212cb363239b8f1447058
python           : 3.8.2.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.15.0-101-generic
Version          : #102-Ubuntu SMP Mon May 11 10:07:26 UTC 2020
machine          : x86_64
processor        : 
byteorder        : little
LC_ALL           : C.UTF-8
LANG             : C.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.1.0.dev0+1681.g62c7dd3e7.dirty
numpy            : 1.17.5
pytz             : 2020.1
dateutil         : 2.8.1
pip              : 20.1.1
setuptools       : 46.4.0.post20200518
Cython           : 0.29.19
pytest           : 5.4.2
hypothesis       : 5.15.1
sphinx           : 3.0.4
blosc            : None
feather          : None
xlsxwriter       : 1.2.8
lxml.etree       : 4.5.1
html5lib         : 1.0.1
pymysql          : None
psycopg2         : None
jinja2           : 2.11.2
IPython          : 7.14.0
pandas_datareader: None
bs4              : 4.9.1
bottleneck       : 1.3.2
fastparquet      : 0.4.0
gcsfs            : None
matplotlib       : 3.2.1
numexpr          : 2.7.1
odfpy            : None
openpyxl         : 3.0.3
pandas_gbq       : None
pyarrow          : 0.17.1
pytables         : None
pyxlsb           : None
s3fs             : 0.4.2
scipy            : 1.4.1
sqlalchemy       : 1.3.17
tables           : 3.6.1
tabulate         : 0.8.7
xarray           : 0.15.1
xlrd             : 1.2.0
xlwt             : 1.3.0
</details>
Would take a test for this if anyone would like to add

Commit message: DOC: updated what's new (#34431)

Patch to review:
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index 88bf0e005a221..04a097eec02d6 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -901,6 +901,7 @@ I/O
 - Bug in :meth:`~DataFrame.read_feather` was raising an `ArrowIOError` when reading an s3 or http file path (:issue:`29055`)
 - Bug in :meth:`~DataFrame.to_excel` could not handle the column name `render` and was raising an ``KeyError`` (:issue:`34331`)
 - Bug in :meth:`~SQLDatabase.execute` was raising a ``ProgrammingError`` for some DB-API drivers when the SQL statement contained the `%` character and no parameters were present (:issue:`34211`)
+- Bug in :meth: in earlier versions `pd.to_sql` was raising an error when reading DataFrames with `-np.inf` entries; added test (:issue: `34431`)
 
 Plotting
 ^^^^^^^^
diff --git a/pandas/tests/io/test_sql.py b/pandas/tests/io/test_sql.py
index bd53785e89bfe..f537c86958b7d 100644
--- a/pandas/tests/io/test_sql.py
+++ b/pandas/tests/io/test_sql.py
@@ -564,6 +564,13 @@ class DummyException(Exception):
         res2 = self.pandasSQL.read_query("SELECT * FROM test_trans")
         assert len(res2) == 1
 
+    def _to_sql_with_negative_npinf(self):
+        engine = sqlalchemy.create_engine("sqlite://", echo=False)
+
+        pd.DataFrame({"foo": [np.inf]}).to_sql("foobar1", engine)
+        pd.DataFrame({"foo": [-np.inf]}).to_sql("foobar2", engine)
+        pd.DataFrame({"foo": [-np.inf], "infe0": ["bar"]}).to_sql("foobar3", engine)
+
 
 # -----------------------------------------------------------------------------
 # -- Testing the public API


Code review comments:
diff_hunk: @@ -901,6 +901,7 @@ I/O
 - Bug in :meth:`~DataFrame.read_feather` was raising an `ArrowIOError` when reading an s3 or http file path (:issue:`29055`)
 - Bug in :meth:`~DataFrame.to_excel` could not handle the column name `render` and was raising an ``KeyError`` (:issue:`34331`)
 - Bug in :meth:`~SQLDatabase.execute` was raising a ``ProgrammingError`` for some DB-API drivers when the SQL statement contained the `%` character and no parameters were present (:issue:`34211`)
+- Bug in :meth: in earlier versions `pd.to_sql` was raising an error when reading DataFrames with `-np.inf` entries; added test (:issue: `34431`)
path: doc/source/whatsnew/v1.1.0.rst
text: @user1:
You can remove `; added test`.

And just ``` Bug in :meth:`~DataFrame.to_sql` when reading... ```

diff_hunk: @@ -564,6 +564,13 @@ class DummyException(Exception):
         res2 = self.pandasSQL.read_query("SELECT * FROM test_trans")
         assert len(res2) == 1
 
+    def _to_sql_with_negative_npinf(self):
path: pandas/tests/io/test_sql.py
text: @user1:
Don't think this is the right spot for this test. Think this needs to go in `_TestSQLAlchemy` so it hits different database flavors.

Also we usually test round trip behavior. Create DataFrame, to_sql, read_sql, `tm.assert_frame_equal(result, expected)`

@author:
@user1 Thanks for the feeback!

I've moved the test to `_TestSQLAlchemy` like you suggested and added the round trips. I also fixed the `whatsnew` entry