CODEWITHSUNDEEP
pythonsqlpandas
João Pedro
João Pedro

Reputation: 51

Python - Pandas read sql modifies float values columns

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:

  1. 603.29
  2. 1512.00
  3. 488.61
  4. 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:

  1. 60329.0
  2. 1512.0
  3. 48861.0
  4. 48861.0

How can i fix it?

Upvotes: 2

Views: 1078

Answers (2)

MattiH
MattiH

Reputation: 654

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 Screenshot of SQL table

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)

Upvotes: 0

João Pedro
João Pedro

Reputation: 51

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.

Upvotes: 3

Related Questions