Pandas read_sql from sqlite doesn't work with np datatypes

Code Sample, a copy-pastable example if possible

import sqlite3
import pandas as pd
conn = sqlite3.connect('tmp')
df = pd.DataFrame({'a': [0,1,2]})
df.to_sql('df', conn)
pd.read_sql('SELECT * FROM df WHERE a = ?', conn, params=[1]).shape  # (1,2) as expected
pd.read_sql('SELECT * FROM df WHERE a = ?', conn, params=[np.int64(1)]).shape # (0, 2) - bug

Expected Output

(1,2) Actually, it works only with int/float/np.float64, but not with np.int64/np.int32/np.float128/np.float32. This of course is a problem when the values are obtained from pandas dataframes, where they are often np.int64.

output of pd.show_versions()

INSTALLED VERSIONS

commit: None python: 3.5.0.final.0 python-bits: 64 OS: Linux OS-release: 3.13.0-77-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: en_US.UTF-8

pandas: 0.18.0 nose: 1.3.7 pip: 8.1.1 setuptools: 18.2 Cython: 0.23.4 numpy: 1.11.0 scipy: 0.16.1 statsmodels: 0.6.1 xarray: None IPython: 4.0.0 sphinx: None patsy: 0.4.1 dateutil: 2.5.2 pytz: 2016.3 blosc: None bottleneck: 1.0.0 tables: None numexpr: 2.4.6 matplotlib: 1.5.0 openpyxl: None xlrd: None xlwt: None xlsxwriter: None lxml: None bs4: None html5lib: None httplib2: None apiclient: None sqlalchemy: None pymysql: None psycopg2: None jinja2: 2.8 boto: None

Comment From: jorisvandenbossche

The params are just passed through to the database driver, sqlite3 in this case, so I am not sure if this is something that should be solved on the pandas side, the driver, or the user hem/herself.

In any case, it could be solved by going through the param values and trying .item() on the values.

Comment From: pkch

Well of course I can deal with it, as you suggest (or by simply converting it explicitly, using int()). But that's only after I know that it's a problem. How long would it take someone to figure out that this bug is affecting their results? Especially, since there's no error message, just silently producing incorrect result.

And yes, I don't know if it's a bug in pandas, or sqlite3.

Comment From: mroeschke

I agree that this is likely not an issue with pandas and probably due to the data going through sqllite3. Since this is an upstream issue closing.