Description:
persisting and/or returning values from a DOUBLE column undergoes data truncation not present in other MySQL drivers, only on Py2K, not Py3K. The test case below fails only for MySQL connector, not MySQLdb, on Python 2.7, passes on Python 3.3, 3.4:
from __future__ import print_function
def test(dbapi):
conn = dbapi.connect(
user='scott', passwd='tiger', host='localhost',
db='test')
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE t (
scale_value DOUBLE(15, 12),
unscale_value DOUBLE
)
""")
value = 45.768392065789
cursor.execute(
"INSERT INTO t (scale_value, unscale_value) VALUES (%s, %s)",
(value, value))
cursor.execute("SELECT scale_value, unscale_value FROM t")
row = cursor.fetchone()
try:
print(
"DBAPI: %s, VALUE: %.11f, ROW[0]: %.11f, ROW[1]: %.11f" %
(dbapi.__name__, value, row[0], row[1]))
assert row == (value, value)
finally:
cursor.execute("DROP TABLE t")
try:
import MySQLdb
except ImportError:
pass
else:
test(MySQLdb)
from mysql import connector
test(connector)
output on Py2.7:
DBAPI: MySQLdb, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579
DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206580, ROW[1]: 45.76839206580
Traceback (most recent call last):
File "test.py", line 41, in <module>
test(connector)
File "test.py", line 29, in test
assert row == (value, value)
AssertionError
output on py3.3 (where I dont have MySQLdb installed):
DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579
it seems that MySQL connector might be running this float through the string function, which truncates a float in the way we see above:
>>> str(45.768392065789)
'45.7683920658'
>>>
on Py3K, the additional accuracy is preserved:
>>> str(45.768392065789)
'45.768392065789'
>>>
How to repeat:
run the above script.
Suggested fix:
MySQL connector should not be relying upon the raw str() function to parse floating points.
Description: persisting and/or returning values from a DOUBLE column undergoes data truncation not present in other MySQL drivers, only on Py2K, not Py3K. The test case below fails only for MySQL connector, not MySQLdb, on Python 2.7, passes on Python 3.3, 3.4: from __future__ import print_function def test(dbapi): conn = dbapi.connect( user='scott', passwd='tiger', host='localhost', db='test') cursor = conn.cursor() cursor.execute(""" CREATE TABLE t ( scale_value DOUBLE(15, 12), unscale_value DOUBLE ) """) value = 45.768392065789 cursor.execute( "INSERT INTO t (scale_value, unscale_value) VALUES (%s, %s)", (value, value)) cursor.execute("SELECT scale_value, unscale_value FROM t") row = cursor.fetchone() try: print( "DBAPI: %s, VALUE: %.11f, ROW[0]: %.11f, ROW[1]: %.11f" % (dbapi.__name__, value, row[0], row[1])) assert row == (value, value) finally: cursor.execute("DROP TABLE t") try: import MySQLdb except ImportError: pass else: test(MySQLdb) from mysql import connector test(connector) output on Py2.7: DBAPI: MySQLdb, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579 DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206580, ROW[1]: 45.76839206580 Traceback (most recent call last): File "test.py", line 41, in <module> test(connector) File "test.py", line 29, in test assert row == (value, value) AssertionError output on py3.3 (where I dont have MySQLdb installed): DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579 it seems that MySQL connector might be running this float through the string function, which truncates a float in the way we see above: >>> str(45.768392065789) '45.7683920658' >>> on Py3K, the additional accuracy is preserved: >>> str(45.768392065789) '45.768392065789' >>> How to repeat: run the above script. Suggested fix: MySQL connector should not be relying upon the raw str() function to parse floating points.