pandas read_sql_query returns negative and incorrect values for Oracle Database number field containing positive values
Removing pandas and just using cx_Oracle still resulted in an integer overflow so in the SQL query I'm using:
CAST(field AS NUMBER(19))
At this moment I can only guess that any field between NUMBER(11) and NUMBER(18) will require an explicit CAST to NUMBER(19) to avoid the overflow.
I'm running pandas read_sql_query and cx_Oracle 6.0b2 to retrieve data from an Oracle database I've inherited to a DataFrame.
A field in many Oracle tables has data type NUMBER(15, 0) with unsigned values. When I retrieve data from this field the DataFrame reports the data as int64 but the DataFrame values have 9 or fewer digits and are all signed negative. All the values have changed - I assume an integer overflow is happening somewhere.
If I convert the database values using to_char in the SQL query and then use pandas to_numeric on the DataFrame the values are type int64 and correct.
I'm using Python 3.6.1 x64 and pandas 0.20.1. _USE_BOTTLENECK is False.
How can I retrieve the correct values from the tables without using to_char?