2

I have a Pandas DataFrame that I'm sending to MySQL via to_sql with sqlalchemy. My floats in SQL sometimes show decimal places that are slightly off (compared to the df) and result in an error: "Warning: (1265, "Data truncated for column 'Dividend' at row 1")". How do I round the floats so that they match the value in the DataFrame?

The values are pulled from a CSV and converted from strings to floats. They appear fine when written to Excel, but when sent to SQL, the numbers are slightly off.

I've looked into the issues with floats when it comes to binary, but I can't figure out how to override that during the transfer from DataFrame to SQL.

from sqlalchemy import create_engine
import pandas as pd

def str2float(val):
    return float(val)

data = pd.read_csv(
        filepath_or_buffer = filename,
        converters = {'col1':str2float}

db = create_engine('mysql://user:pass@host/database')
data.to_sql(con=db, name='tablename', if_exists='append', index=False)
db.dispose()

Most floats pull over similar to 0.0222000000, but every once in awhile it will appear like 0.0221999995. Ideally I would like it to automatically truncate all the 0s at the end, but I would settle for the first example. However I need to have it round up to match the float that was stored in the DataFrame.

drummerof13
  • 43
  • 1
  • 7

1 Answers1

1

I had a similar problem. The number I imported into the data-frame had 3 decimal places. But when inserted into the SQL table, it had 12 digits.

I just used .round() method and it worked for me.

df["colname"] = df["colname"].round(3)
Ahmed Tounsi
  • 1,482
  • 1
  • 14
  • 24
Devang Sanghani
  • 731
  • 5
  • 14