2

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.

Evgeni
  • 141
  • 1
  • 9
  • 2
    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 Nov 09 '16 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 Nov 09 '16 at 11:19
  • Nickil, I will try this. MaxU - numbers converted to strings – Evgeni Nov 09 '16 at 11:22
  • 1
    @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 Nov 09 '16 at 14:26

1 Answers1

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.

MMF
  • 5,750
  • 3
  • 16
  • 20