3

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
halloleo
  • 9,216
  • 13
  • 64
  • 122
  • NUMBER and FLOAT are different data types in Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6. – pifor May 28 '20 at 07:05
  • @pifor True, they are of type NUMBER - adjusting the post accodringly. But they have a decimal fraction and still should not be mapped to integers! – halloleo May 28 '20 at 07:23

1 Answers1

1

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.

pdaawr
  • 436
  • 7
  • 16