Search code examples
pythonsql-serverpandassqlalchemy

float values not displaying decimal places when using pandas to_sql()

I am inserted values into a table using df.to_sql() function, but the values are not being displayed properly in SQL Server.

At the top of my script I use:

pd.options.display.float_format = '{:,.4f}'.format

and when I print my dataframes all is well:

 Value    Price
100.0000   34.0100
101.4000   35.0100
102.0000   36.0100
103.9000   37.0100
104.0000   38.0100

however, when I insert into SQL Server I get:

Value    Price
100      34.01
101.4    35.01
102      36.01
103.9    37.01
104      38.01

This is what I have tried:

df.to_sql('table', con=engine, if_exists='replace', index=False, dtype={"Price": Float(), "Value": Float()})

Any idea why this is happening and how to fix? I am using SQLAlchemy and SQL Server

Solution

  • As @siggemannen and @DaleK mentioned, Float does not have precision and is an approximation data type. Numeric is the option to go with for fixed precision:

    from sqlalchemy.types import Numeric

df.to_sql('CounterpartyData', con=engine, if_exists='replace', index=False, dtype={"Price": Numeric(19,4), "Value": Numeric(19,4)})

    Output in SQL:

    Value    Price
100.0000   34.0100
101.4000   35.0100
102.0000   36.0100
103.9000   37.0100
104.0000   38.0100