No way to specify higher precision (e.g. Double) when saving DataFrame with floating number to MySQL · Issue #9009 · pandas-dev/pandas (original) (raw)

When saving DataFrame to MySQL, Pandas will map Python float (by default double precision) to MySQL FLOAT (by default single precision). I couldn't find a way to either specify the use of MySQL DOUBLE, or MySQL DECIMAL.

When saving some financial data this will cause loss of precision. For example, 2-year treasury future price quotes are multiples of 1/4/32 = 0.0078125, and there are usually 3 digits before the decimal point. A real-world example is 109.8828125 (TUZ4 settlement price on 12/04/2014). Saving a DataFrame with this number to MySQL results in the rounded 109.883. The error itself is not huge (2.4% of a tick), but still undesirable, especially considering using MySQL DOUBLE or DECIMAL can easily improve the precision.

Currently a workaround is to modify pandas/io/sql.py to map Python float to MySQL FLOAT(53), which forces the use of DOUBLE. There are two places to change, one inside method definition SQLTable._sqlalchemy_type(self, col), the other inside dictionary definition _SQL_TYPES. This work around will waste storage when only single precision is needed. It will be better if some option is provided to utilize MySQL DOUBLE and DECIMAL only when needed.