pandas read_sql missing dtype on empty result

[MagnetarAlex]

Code Sample, a copy-pastable example if possible

con = some sqlalchemy engine
query = "select * from table"
result = pd.read_sql(query, con)

Problem description

Assume table have two columns, user (str) and id (int).
Previously, even if the result has 0 rows, the columns would have dtype in consistent with the table column.

result.dtypes
user object
id int64
dtype: object

In pandas 0.23, if the result is empty, then all the columns dtypes in result are object.

result.dtypes
user object
id object
dtype: object

Expected Output

user object
id int64
dtype: object

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS

commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 62 Stepping 4, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.23.0
pytest: 3.5.1
pip: 10.0.1
setuptools: 39.1.0
Cython: 0.28.2
numpy: 1.14.3
scipy: 1.1.0
pyarrow: 0.9.0
xarray: 0.10.4
IPython: 6.4.0
sphinx: None
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2018.4
blosc: None
bottleneck: 1.2.1
tables: None
numexpr: 2.6.5
feather: 0.4.0
matplotlib: 2.2.2
openpyxl: 2.5.3
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.4
lxml: 4.2.1
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.7
pymysql: 0.8.1
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[gfyoung]

Hmm...the dtypes should reflect the schema. Could you provide a representative example, if possible?

[TomAugspurger]

With read_sql though, we don't have a schema. The SQL expression can change the types at will.

@MagnetarAlex can you make a self-contained example (using sqlite3) where you create an empty table and read the types, and then run it on 0.22.0 and 0.23.0 and post the results?

[MagnetarAlex]

@gfyoung @TomAugspurger
Hey guys, here is a simple example, in the first query, the dtypes on id/userid are int64, while on the second query, the dtypes are object.

import sqlite3
import pandas as pd

db = sqlite3.connect(':memory:')
cursor = db.cursor()
cursor.execute('''CREATE TABLE users(id INTEGER PRIMARY KEY, userid INTEGER, username TEXT)''')
db.commit()

cursor = db.cursor()
cursor.execute('''INSERT INTO users(userid, username)
VALUES(?,?)''', (123, 'John'))

query = 'select * from users'
df = pd.read_sql(query, db)
print(df.dtypes)

query = 'select * from users where userid=0'
df = pd.read_sql(query, db)
print(df.dtypes)

[TomAugspurger]

Thanks for the example.

In pandas 0.23, if the result is empty, then all the columns dtypes in result are object.

I read that as "starting in pandas 0.23", but it was never the case that the empty query had non-object dtypes, right? I get the same result for 0.23 and 0.22.

In the first case, you get ints since that's what pandas inferred. pandas has to infer types here, since query can be any sql query, including ones that change types:

In [7]: pd.read_sql("select cast(userid as text) as userid from users", db).dtypes
Out[7]:
userid    object
dtype: object

In the second case, pandas just gets and empty dataframe and has to infer "object" dtype for everything.

It would be great to have a note in the docs and docstrings explaining this. Could you do that @MagnetarAlex?

[MagnetarAlex]

I see. Also tried with 0.21.1 and it's the same result.
I had a whole package update a few weeks ago so I guess it's probably related to other db packages change (sqlalchemy or pyodbc). Apologize for the confusion.

It's a pleasure to contribute to the doc notes, my friend LB helped me to polish the sentences. Hope it will help.

Note that pandas infers column dtypes from query outputs, and not by looking up data types in the physical database schema.

For example, assume ‘userid’ is an integer column in a table. Then, intuitively, “select userid …” will return integer-valued series, while “select cast(userid as text) …” will return object-valued (str) series.

Accordingly, if the query output is empty, then all resulting columns will be returned as object-valued (since they are most general). If you foresee that your query will sometimes generate an empty result, you may want to explicitly typecast afterwards to ensure dtype integrity.

[TomAugspurger]

Thanks. Mind making a pull request adding that to the prose docs in doc/source/io.rst?

…

On Thu, Jun 14, 2018 at 5:01 PM, MagnetarAlex ***@***.***> wrote: I see. Also tried with 0.21.1 and it's the same result. I had a whole package update a few weeks ago so I guess it's probably related to other db packages change (sqlalchemy or pyodbc). Apologize for the confusion. It's a pleasure to contribute to the doc notes, my friend LB helped me to polish the sentences. Hope it will help. Note that pandas infers column dtypes from query outputs, and not by looking up data types in the physical database schema. For example, assume ‘userid’ is an integer column in a table. Then, intuitively, “select userid …” will return integer-valued series, while “select cast(userid as text) …” will return object-valued (str) series. Accordingly, if the query output is empty, then all resulting columns will be returned as object-valued (since they are most general). If you foresee that your query will sometimes generate an empty result, you may want to explicitly typecast afterwards to ensure dtype integrity. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#21364 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABQHIilFrsQiwuj46GfDg1oVVQwgip20ks5t8t00gaJpZM4Ue6Br> .

[oguzhanogreden]

Seeing that a PR didn't arrive, I'm thinking of making it. Is it sufficient if I tag @MagnetarAlex in the commit message?