BUG: pandas.to_sql raises unexpected column error if data contains -numpy.inf

[PiotrBenedysiuk]

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

• 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

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()

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

[arw2019]

This runs fine on the 1.0. master.

The complete snippet I ran is:

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

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 62c7dd3
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

[WillAyd]

Would take a test for this if anyone would like to add

[jorisvandenbossche]

This might depend on the database you are using (and the database driver).

For example, the example using sqlite also works with pandas 0.23.

(now, it's still good to actually test this for sqlite)