Pandas' `read_sql` creates integer columns when reading from an Oracle table which has number columns with decimal points

user_65889 0
Viewed 561

I have a Oracle table with columns of type VARCHAR2 (i.e. string) and of type NUMBER (i.e. a numeric value with a fractional part). And the numeric columns contain indeed values with decimal points, not integer values.

However when I read this table into a Pandas dataframe via pandas.read_sql I receive the numeric columns in the data frame as int64. How can I avoid this and receive instead float columns with the full decimal values?

I'm using the following versions

python           : 3.7.4.final.0
pandas           : 1.0.3
Oracle           : 18c Enterprise Edition / Version 18.9.0.0.0
read-sql pandas oracle python
1 Answers

I have encountered the same thing. I am not sure if this is the reason but I assume that NUMBER type without any size restrictions is too big for pandas and it is automatically truncated to int64 or the type is improperly chosen by pandas – default NUMBER might be treated as an integer. You can limit the type of the column to e.g. NUMBER(5,4) and pandas should recognise it correctly as a float.

I also found out that using pd.read_sql gives me proper types in contrast to pd.read_sql_table.

user_124913450
Related