ENH: Store in SQL using double precision
State: closed

Closes #9009 . @jorisvandenbossche -- do you think this should test/use single precision ?  We could test for float32 perhaps? My own feeling is that this is the safer default.


Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
We should indeed be safe by default, so opt for what you did now. 
But maybe we can do a refinement now at once? For example, distuinguish float32/float64 dtype and give appropriate sqlalchemy type. Also int32/int64 is a possibility.

Unless I'm missing something, it's actually surprisingly tricky to differentiate between float32 / float64 in a robust manner.   We could check `dtype`, but that wouldn't catch object-typed series w. NAs. inference.pyx, which we use, doesn't return this information (just returns 'floating'), nor does util.pxd (which it calls), nor does numpy_helper.h (which that calls) .  It's surely doable to add those functions but that almost seems like a separate PR.

I wouldn't worry about the `object` dtype. We are already doing the extra of inferring floating data in `object` data (which I already regard as a nice extra but not essential feature, as users should ensure their data have the correct dtype, and there are functions to do this like `DataFrame.convert_objects`).

So I would just check the dtype, when there are `'floating'` data, and use the general case for object dtype.

@jorisvandenbossche  OK, added checks for float32 and int32

Also made a couple of small changes that I though were logical while playing w. this --- passing through dtype argument in to_sql and get_schema, and changed the default floating MYSQL type to be DOUBLE (not SINGLE).

I made your suggested changes.

I copied and pasted your fix from #9138 for now --- I can remove and rebase once you have merged.

Also, I can get the precision to go up to 14 digits.  However, to get this I needed to insert some special code into `get_table`.  Specifically, by default SQLAlchemy reads double-precision numbers into a 'Decimal' type w. 10 digits of precision. See http://stackoverflow.com/questions/19387882/how-to-make-sqlalchemy-return-float-instead-of-decimal-when-reflecting-tables .

@artemyk regarding the precision issue (because of the decimal use). I think that looks OK to do (as we should ensure the full precision is available), but I was wondering if this has performance implications?

@artemyk I merged the other PR, so you can rebase this one

@jorisvandenbossche OK, rebased / upgraded test.
Re performance implications --- I don't think so, it just makes sqlalchemy skip a conversion to Decimal.  It's more inline with what happens if we do `sqlalchemyengine.execute('SELECT * FROM tbl')` (in that case, FLOATs don't get converted to `Decimal` objects)

OK, do you know the reason sqlalchemy does this by default? (just wondering)

Good point about .values .
Re asdecimal, here's what I found on http://docs.sqlalchemy.org/en/rel_0_9/core/types.html : 

> When using the Numeric type, care should be taken to ensure that the asdecimal setting is apppropriate for the DBAPI in use - when Numeric applies a conversion from Decimal->float or float-> Decimal, this conversion incurs an additional performance overhead for all result columns received.
> DBAPIs that return Decimal natively (e.g. psycopg2) will have better accuracy and higher performance with a setting of True, as the native translation to Decimal reduces the amount of floating- point issues at play, and the Numeric type itself doesn’t need to apply any further conversions. However, another DBAPI which returns floats natively will incur an additional conversion overhead, and is still subject to floating point data loss - in which case asdecimal=False will at least remove the extra conversion overhead.

@artemyk I justed tested it with postgresql/psycopg2, and it seems that there, `asdecimal` is already False for me when using `DOUBLE PRECISION(53)` (which contrasts a bit your quote). So of course, there is no performance difference in that case. 
But in any case, I suppose the check can do no harm?

@jorisvandenbossche I'm now wondering --- do you think its possible there is a DECIMAL-type datatype in , say, postgres, that can store arbitrary precision decimals? And that the code we have here would force conversion to Float, thereby loosing precision?

@jorisvandenbossche Can we merge?

@artemyk Yes! (sorry for dropping the ball here)

Can you just rebase?

@jorisvandenbossche Rebased...

@artemyk Thanks for the work on this, and really for letting this take so long!