Python dataframe to SQL numbers are getting rounded

Question

I have a dataframe like this:

        ID            Value       Value2

      1079712667    169945540   18.103862
      1079712668    NA          100.509234
      1079712669    2600000     28.833578
      1079712670    14362445    101.525284
      1079712671    6834165     NA

I upload it to SQL using

df.to_sql('TABLE',engine,if_exists='append',index=False)

And I get:

      ID            Value         Value2

      1079712667    1.69946e+008  18.1039
      1079712668    2.28525e+006  100.509
      1079712669    2.6e+006      28.8336
      1079712670    NA            101.525
      1079712671    6.83417e+006  NA

All SQL columns are varchar(500) as I have mixed text/numbers values. Not sure what I can do to disable the number roundup and precision. The ID values for example are being upload as they should be.

Not sure if this is the right way. But you can preserve the dtypes by casting everything to str while writing to the SQL DB. from sqlalchemy.types import String; df.to_sql('TABLE',engine,if_exists='append',index=False, dtype={'Value': String, 'Value2: String}) — Nickil Maveli
– Nickil Maveli, Commented Nov 9, 2016 at 11:13
it's not very clear what are you trying to achieve... What kind of data types do you want to have in your SQL table - number with NULLs instead of "NA" or varchar with numbers converted to strings? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Nov 9, 2016 at 11:19
Nickil, I will try this. MaxU - numbers converted to strings — Evgeni
– Evgeni, Commented Nov 9, 2016 at 11:22
@Evgeni, i would recommend to convert your DF columns to numeric ones using df.col_name = pd.to_numeric(df.col_name, errors='coerce'), after that you won't have this problems. You will also have NULLs in your SQL table in place of strings (actually in place of those values, which pd.to_numeric() couldn't convert to numeric dtype) — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Nov 9, 2016 at 14:26

MMF · Accepted Answer · 2016-11-09 11:24:41Z

2

The issue was raised two years ago : here.

After that a new feature was introduced to allow you to override the default chosen type using the dtype kwarg in to_sql.

dtype can be set to any of the known SQL data types. Here is the link to all these types.

dtype must be a dictionary with column names as keys and SQL data type as values :

dtype={'Value1': DOUBLE, 'Value2: DOUBLE}

Warning: If you look at jorisvandenbossche answer in the git issue thread (here), there might be some approximation issues.

@Nickil Maveli : I like your trick. Using str while writing to the SQL database might be the best solution though to avoid any problems.

answered Nov 9, 2016 at 11:24

MMF

5,9703 gold badges18 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Python dataframe to SQL numbers are getting rounded

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related