MySQL Bugs: #117827: Inconsistent conversion to

Bug #117827	Inconsistent conversion to_sql for cext vs pure python
Submitted:	28 Mar 17:54	Modified:	3 Sep 17:02
Reporter:	Andrew Zhao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	9.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We encountered inconsistent behavior when using the python connector between the c extension and pure python.  We found that with the cext, numpy ints and floats can be passed as inputs to the sql query, but when using pure python, this will give the error: Python 'float64' cannot be converted to a MySQL type.

Looking at the code here, in the pure python, the type name must match exactly for the conversion to complete, so since float64 does not match _float_to_mysql, this will fail with the error.
https://github.com/mysql/mysql-connector-python/blob/e27103535aa39ef75c3a19c2d4e7c26a73adc...

On the other hand the code in the c extension uses the function PyFloat_Check as opposed to PyFloat_CheckExact, meaning that any subclasses of float, including numpy.float64 will succeed.
https://github.com/mysql/mysql-connector-python/blob/e27103535aa39ef75c3a19c2d4e7c26a73adc...

As a result, some code that works in the cext implementation will fail in pure python if that code passes any subclasses of convertible type into the 

How to repeat:
This can be shown with something similar to this:

cnx = mysql.connector.connect(user='scott', database='employees', use_pure=True)
value = numpy.float64(5.0)
my_query(cnx, value)

By changing use_pure to True of False the code will crash or succeed.

Suggested fix:
I am not sure if the intended behavior is the cext one or the pure python, but adjusting these to match would be useful.  Changing the pure python to match the cext behavior is less likely to cause any issues because the cext usage is currently default and also more permissive than the pure python.

Hello Andrew Zhao,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Added the following entry to the Connector/Python 9.4.0 changelog: 

"Unlike when using the C extension, the pure-Python implementation could not convert NumPy float62 values into a MySQL type during a query. With this fix, the conversion works, so that the C Extension and pure-Python implementations are now consistent in this respect."