Bug #91281 | Mysql Connector Python doesn't convert cDecimal properly | ||
---|---|---|---|
Submitted: | 15 Jun 2018 11:41 | Modified: | 26 Apr 2022 16:28 |
Reporter: | Marcin Lulek | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / Python | Severity: | S1 (Critical) |
Version: | 8.x, 2.x, 8.0.11, 2.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[15 Jun 2018 11:41]
Marcin Lulek
[15 Jun 2018 12:37]
Marcin Lulek
2.x versions are affected too
[15 Jun 2018 12:37]
Marcin Lulek
I should note that 2.x returning None is also a bug in this regard?
[18 Jun 2018 9:07]
MySQL Verification Team
Hello Marcin, Thank you for the report. I'm not seeing any exceptions but [(None,)] in both the cases. Thanks, Umesh
[18 Jun 2018 9:07]
MySQL Verification Team
## root@ArtfulAardvark:/home/ushastry# pip3 install m3-cdecimal Collecting m3-cdecimal Downloading https://files.pythonhosted.org/packages/38/48/f971b928e29d7c163dd01f265c294417dfe898bd07a1... (639kB) 100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 645kB 192kB/s Building wheels for collected packages: m3-cdecimal Running setup.py bdist_wheel for m3-cdecimal ... done Stored in directory: /root/.cache/pip/wheels/a9/4e/f3/0b1acc4efe5587ac08806ed5d82ec784d8c4c1c6b06ff15c67 Successfully built m3-cdecimal Installing collected packages: m3-cdecimal Successfully installed m3-cdecimal-2.3 root@ArtfulAardvark:/home/ushastry# ## 8.0.11 tar -zxvf mysql-connector-python-8.0.11.tar.gz /usr/bin/python3.6 setup.py install --prefix=/home/ushastry/Downloads/connector-python-8_0_11/ cd /home/ushastry/Downloads/connector-python-8_0_11/ vi 91281.py PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py -- from cdecimal import Decimal #this doesn't root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-8_0_11# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py [(None,)] -- from decimal import Decimal # this works root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-8_0_11# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py [('1.5',)] ## 2.1.7 tar -zxvf mysql-connector-python-2.1.7.tar.gz /usr/bin/python3.6 setup.py install --prefix=/home/ushastry/Downloads/connector-python-2_1_7/ cd /home/ushastry/Downloads/connector-python-2_1_7 vi 91281.py PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py -- from cdecimal import Decimal #this doesn't root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-2_1_7# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py [(None,)] -- from decimal import Decimal # this works root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-2_1_7# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py [('1.5',)]
[18 Jun 2018 16:06]
Marcin Lulek
Interesting, this is what I get on ubuntu 17.10 and python Python 3.6.3 ergo@ergo-desktop:~/workspace/oracle$ python3 -m venv env3 ergo@ergo-desktop:~/workspace/oracle$ env3/bin/pip install mysql-connector-python m3-cdecimal ..... removed .... Failed to build m3-cdecimal Installing collected packages: six, protobuf, mysql-connector-python, m3-cdecimal Running setup.py install for m3-cdecimal ... done Successfully installed m3-cdecimal-2.3 mysql-connector-python-8.0.11 protobuf-3.6.0 six-1.11.0 ergo@ergo-desktop:~/workspace/oracle$ env3/bin/python test.py Traceback (most recent call last): File "test.py", line 13, in <module> cursor.execute(query, (Decimal('1.5'),)) File "/home/ergo/workspace/oracle/env3/lib/python3.6/site-packages/mysql/connector/cursor_cext.py", line 246, in execute prepared = self._cnx.prepare_for_mysql(params) File "/home/ergo/workspace/oracle/env3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 514, in prepare_for_mysql result = self._cmysql.convert_to_mysql(*params) _mysql_connector.MySQLInterfaceError: Python type cdecimal.Decimal cannot be converted For Python 2.7: ergo@ergo-desktop:~/workspace/oracle$ virtualenv env27 Running virtualenv with interpreter /usr/bin/python2 New python executable in /home/ergo/workspace/oracle/env27/bin/python2 Also creating executable in /home/ergo/workspace/oracle/env27/bin/python Installing setuptools, pkg_resources, pip, wheel...done. ergo@ergo-desktop:~/workspace/oracle$ env27/bin/pip install mysql-connector-python m3-cdecimal Collecting mysql-connector-python Using cached https://files.pythonhosted.org/packages/dc/48/32c715d2cef42d0791c5b2f21b4f1f280c8e45afa66a... Collecting m3-cdecimal Collecting protobuf>=3.0.0 (from mysql-connector-python) Using cached https://files.pythonhosted.org/packages/27/e7/bf96130ebe633b08a3913da4bb25e50dac5779f1f68e... Collecting six>=1.9 (from protobuf>=3.0.0->mysql-connector-python) Using cached https://files.pythonhosted.org/packages/67/4b/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90... Requirement already satisfied: setuptools in ./env27/lib/python2.7/site-packages (from protobuf>=3.0.0->mysql-connector-python) (39.2.0) Installing collected packages: six, protobuf, mysql-connector-python, m3-cdecimal Successfully installed m3-cdecimal-2.3 mysql-connector-python-8.0.11 protobuf-3.6.0 six-1.11.0 ergo@ergo-desktop:~/workspace/oracle$ env27/bin/python test.py Traceback (most recent call last): File "test.py", line 13, in <module> cursor.execute(query, (Decimal('1.5'),)) File "/home/ergo/workspace/oracle/env27/local/lib/python2.7/site-packages/mysql/connector/cursor_cext.py", line 246, in execute prepared = self._cnx.prepare_for_mysql(params) File "/home/ergo/workspace/oracle/env27/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 514, in prepare_for_mysql result = self._cmysql.convert_to_mysql(*params) _mysql_connector.MySQLInterfaceError: Python type cdecimal.Decimal cannot be converted
[19 Jun 2018 20:20]
Lukasz Fidosz
Hello Umesh, It appears you have installed connector-python without C extensions, C extensions are default in latest version but you still need to pass --with-mysql-capi=/path_to_your/mysql_config to enable them, hence you experiencing different results than Marcin - None instead of errors, which is incorrect behaviour anyway in my opinion. The error seems to be caused by the way we are testing for decimal: https://github.com/mysql/mysql-connector-python/blob/8.0.11/src/mysql_capi.c#L1804 I would propose to use PyObject_IsInstance instead, example patch: diff --git a/src/mysql_capi.c b/src/mysql_capi.c index c8839a8..dec304e 100644 --- a/src/mysql_capi.c +++ b/src/mysql_capi.c @@ -1719,7 +1719,7 @@ MySQL_ping(MySQL *self) PyObject* MySQL_convert_to_mysql(MySQL *self, PyObject *args) { - PyObject *value, *new_value; + PyObject *value, *new_value, *mod_decimal; PyObject *prepared, *quoted; int i; Py_ssize_t size; @@ -1795,24 +1795,24 @@ MySQL_convert_to_mysql(MySQL *self, PyObject *args) else if (PyDelta_CheckExact(value)) { new_value= pytomy_timedelta(value); -#ifndef PY3 - } - else if (strcmp((value)->ob_type->tp_name, "Decimal") == 0) - { -#else - } - else if (strcmp((value)->ob_type->tp_name, "decimal.Decimal") == 0) - { -#endif - new_value= pytomy_decimal(value); } else - { - PyOS_snprintf(error, 100, - "Python type %s cannot be converted", - (value)->ob_type->tp_name); - PyErr_SetString(MySQLInterfaceError, (const char*)error); - goto error; + { + mod_decimal= PyImport_ImportModule("decimal"); + if ((mod_decimal) && (PyObject_IsInstance(value, PyObject_GetAttrString(mod_decimal, "Decimal")))) + { + new_value= pytomy_decimal(value); + } + else + { + Py_XDECREF(mod_decimal); // i don't like this being repeated twice :| + PyOS_snprintf(error, 100, + "Python type %s cannot be converted", + (value)->ob_type->tp_name); + PyErr_SetString(MySQLInterfaceError, (const char*)error); + goto error; + } + Py_XDECREF(mod_decimal); } if (!new_value) That will fix it in python 2 in scenarios when cdecimal is used as drop in replacement for decimal, as in by doing: sys.modules['decimal'] = cdecimal Such approach is for example suggested by sqlalchemy: http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Numeric In python3 decimal is based on cdecimal implementation so no need to use external package, but this also makes use of the same code base for both lines. Example code ran after following patch is applied: import sys import cdecimal sys.modules['decimal'] = cdecimal from decimal import Decimal import mysql.connector cnx = mysql.connector.connect(user='test', password='test', host='127.0.0.1', database='test') query = ("SELECT %s") cursor = cnx.cursor() cursor.execute(query, (Decimal('1.5'),)) print(cursor.fetchall()) [(u'1.5',)]
[20 Jun 2018 4:48]
MySQL Verification Team
Thank you,Lukasz. Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team - https://dev.mysql.com/community/ Regards, Umesh
[26 Apr 2022 16:19]
Nuno Mariz
Posted by developer: cdecimal has been integrated into CPython 3.3, where it supersedes the pure Python version: import decimal will automatically import the C version.