Python - Pandas read sql modifies float values columns

Question

I'm trying to use Pandas read_sql to validate some fields in my app.

When i read my db using SQL Developer, i get these values:

603.29
1512.00
488.61
488.61

But reading the same sql query using Pandas, the decimal places are ignored and added to the whole-number part. So i end up getting these values:

60329.0
1512.0
48861.0
48861.0

How can i fix it?

That just removed the '.0' but didn't fixed the real problem. I've changed the sql query to convert that column to char for now. — João Pedro
– João Pedro, Commented Oct 30, 2019 at 17:55

João Pedro · Accepted Answer · 2019-10-30 17:59:58Z

3

I've found a workaround for now.

Convert the column you want to string, then after you use Pandas you can convert the string to whatever type you want.

Even though this works, it doesn't feel right to do so.

answered Oct 30, 2019 at 17:59

João Pedro

514 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MattiH · Accepted Answer · 2022-01-06 06:24:23Z

Could you specify what SQL you are using? I just encountered a bit similar problem, which I overcame by defining the datatype more specifically in the query - here the solution for it. I guess you could use the similar approach and see if it works.

In my MySQL database I have four columns with very high precision

When I tried to read them with this query, they were truncated to 5 digits after the decimal delimiter.

    query = """select  
               y_coef,
               y_intercept,
               x_coef,
               x_intercept
               from TABLE_NAME"""
    df = pd.read_sql(query, connection)

However, when I specified that I want to have them with the precision of 15 digits after the decimal delimiter like below, they were not truncated anymore.

        query = """select 
               cast(y_coef as decimal(15, 15)) as y_coef,
               cast(y_intercept as decimal(15, 15)) as y_intercept,
               cast(x_coef as decimal(15, 15)) as x_coef,
               cast(x_intercept as decimal(15, 15)) as x_intercept
               from TABLE_NAME"""
    df = pd.read_sql(query, connection)

Collectives™ on Stack Overflow

Python - Pandas read sql modifies float values columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related