pandas read sql db2 corrupts decimal

Question

I am trying to read a datatable from using db2. It seems, however, that decimals are simply ignored and floats are somehow multiplied by 100. For instance 100.50 becomes 10050.0 when read into a pandas dataframe.

BTW I am from Norway, so decimals are denoted by a , rather than a .. I dont know if this matters.

I use the following SQL (in WinSQL Lite):

SELECT CUSTOMER_ID, FUND_NAME, SAVINGS_AMOUNT
FROM SAVINGS_TABLE
WHERE CUSTOMER_ID = 1

, which gives:

CUSTOMER_ID, FUND_NAME, SAVINGS_AMOUNT
          1    ASSET_X       259131,72
          1    ASSET_Y       718533,33   

I know this is correct, so the issue is not the data.

In Spyder, using Python, I have

import pandas as pd
import pyodbc as po

DSN = 'MY_DSN'
UID = 'MY_USER'
PWD = 'MY_PASSWORD'
CON = po.connect('DSN={0}; UID={1}; PWD={2}'.format(DSN, UID, PWD))

SQL = """SELECT CUSTOMER_ID, FUND_NAME, SAVINGS_AMOUNT
FROM SAVINGS_TABLE
WHERE CUSTOMER_ID = 1"""

df = pd.read_sql(SQL, CON)

df
Out[16]: 
    CUSTOMER_ID    FUND_NAME    SAVINGS_AMOUNT
0             1      ASSET_X        25913172.0
1             1      ASSET_Y        71853333.0

What is happening here, and how can I fix it?

The column SAVINGS_AMOUNT is DECIMAL with Size=9 and Scale=2. Surely the issue is somehow related to this. I guess I have to set a parameter in pd.read_sql to interpret the column.

I know I can simply divide by 100 to correct this. But I dont want to do that. I want to read the correct numbers.

Also I'm using Windows.

Answer 1

You may find it helps to explicitly set the decimal separator to one used in Norway

pyodbc.setDecimalSeparator(',')

Answer 2

Not sure if you've tried forcing the SQL input:

SQL = """SELECT CUSTOMER_ID, FUND_NAME, Cast(SAVINGS_AMOUNT AS Decimal(20,2))
FROM SAVINGS_TABLE
WHERE CUSTOMER_ID = 1"""