Configure pandas read_sql_query to handle NULL?

Question

When i do

from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite://') conn = engine.connect() conn.execute("create table test (a float)") for _ in range(5): conn.execute("insert into test values (NULL)") df = pd.read_sql_query("select * from test", engine) #df = pd.read_sql_table("test", engine) df.a 

the result is a column of None values, not a float("nan") . This is very annoying, especially if you read floating point columns with NULL values.

The read_sql_table version read_sql_table fine, as I assume it can use type information.

Can read_sql_query be easily configured to interpret NULL values ​​as float("nan") ?

Answer 1

It seems that the problem was raised, and something like this - the coerce_float argument - was added to pandas in version 0.7.2, according to the wesm comment on the linked page:

hi arthur, I added the coerce_float parameter (in the commit above), which converts Decimal → float and populates None with NaN. Converting Decimal to float is still very slow. Coming soon to be part 0.7.2

Although the description in pandas.read_sql_query 0.18.1 docs looks confusing:

coerce_float: boolean, defaults to True

Trying to convert values ​​to non-string, non-numeric objects (e.g. decimal.Decimal) to a floating point is useful for SQL result sets