No way to specify higher precision (e.g. Double) when saving DataFrame with floating number to MySQL (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.