MySQL Bugs: #88995: JSON columns should accept Python dicts as input

Bug #88995	JSON columns should accept Python dicts as input
Submitted:	20 Dec 2017 17:31	Modified:	2 Mar 2018 15:00
Reporter:	Chris Foresman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	8.0.5b1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	json

Description:
Connector/Python doesn't convert Python dicts into valid JSON_TYPE when used as a parameter to a query.

Additionally, I'd expect the driver to return a Python `dict` when querying a `JSON` column, but it appears it is returned as `str` (perhaps related to Bug #83516).

How to repeat:
With versions:
- MySQL: 5.7.20
- Connector/Python: 8.0.5b1 (installed via pip)

Assuming minimal schema:
```sql
CREATE TABLE orders (
    id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    results JSON,
);
INSERT INTO orders(results) VALUES (json_object('initial', 'value'));
```

```python
>>> conn = database_connection(url)
>>> cursor = conn.cursor()
>>> cursor.execute("""UPDATE orders SET results=%s WHERE id=1""", [{"results": "fake", "data": "whatever"}])
Traceback (most recent call last):
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/conversion.py", line 179, in to_mysql
    return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_dict_to_mysql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/cursor.py", line 427, in _process_params
    res = [to_mysql(i) for i in res]
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/cursor.py", line 427, in <listcomp>
    res = [to_mysql(i) for i in res]
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/conversion.py", line 182, in to_mysql
    "MySQL type".format(type_name))
TypeError: Python 'dict' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/cursor.py", line 549, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "/Users/chrisforesman/.venv/orp/lib/python3.6/site-packages/mysql/connector/cursor.py", line 432, in _process_params
    "Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'dict' cannot be converted to a MySQL type
```

Additionally, after inserting the data with `json.dumps`, fetching the column returns `str` instead of `dict`:
```python
>>> import json
>>> data = json.dumps({"results": "fake", "data": "whatever"})
>>> cursor.execute("""UPDATE orders SET results=%s WHERE id=1""", [data])
>>> cursor.execute("SELECT results FROM orders WHERE id=1;")
>>> result = cursor.fetchone()
>>> result
('{"data": "whatever", "results": "fake"}',)
>>> type(result[0])
<class 'str'>
```

Suggested fix:
When specifying a `dict` as a query parameter, it should be converted into a `str` with `json.dumps`; if it's not serializable to JSON, raise an error. Likewise, data from a JSON column should be returned as a `dict`.

It would be preferable if a specific JSONEncoder and JSONDecoder subclass could be specified if desired (primarily for handling DATE/TIME/DATETIME types.

Hi Chris,
Thank you for the bug report.
But Connector/Python shouldn't deal with this kind of abstraction when using SQL statements.

Why not? Does the driver coerce Python int, long, float, string types when interpolating  into queries? Why not handle dicts for the JSON type?

Because JSON is not a built-in type (or even type in Python). This kind of conversions shouldn't be made in a driver, is better suited for frameworks that have an high level of abstraction.

As per above developer([17 Jan 10:13] Nuno Mariz) comment we are moving this bug into "Not A Bug"

Respectfully, I have to disagree here. By not supporting the obvious and de facto connection between Python dicts and JSON, it means developers either have to pepper json.loads and JSON.dumps everywhere (with encode and decide on Python 3), or somehow write a customer Cursor class or wrapper around the Connector to do that, or go as far as using an ORM even if that's not what is needed or desired. I'd fork it if it was open source and the code was available on GitHub and just add the minimal compatibility myself, but that doesn't appear to be an option.

Hi Chris,
I understand your point of view and it's a valid one for your use case (which is also important). But besides the reason that I pointed, serialize and deserialize JSON can lead to other problems.
For example if the user wants to set an object hook to parse a field in the JSON (see the options available in https://docs.python.org/3/library/json.html#json.load) or simply wants to pass a JSON string to the client (i.e. browser). In both cases the user needs to serialize to string again, which can hit performance in some cases.
Also type conversions like floats numbers, etc. could be an issue.

I see you points, and I agree it might take some work to come up with an API that can handle those cases while still providing a sensible default. After some searching, I did in fact find the repo for Connector/Python on GitHub, so I'll fork and see what I can come up with to add this capability in useful and extensible way. Do you accept pull requests from outside contributors? And if so, is there documentation on code style, etc?

Yes, we accept contributions. Please visit http://www.oracle.com/technetwork/community/oca-486395.html and submit the Oracle Contributor Agreement.
Regarding the coding style, please use Pylint and use the configuration file: https://github.com/mysql/mysql-connector-python/blob/master/support/style/pylint.rc

Thank you.

Chris,
I've run into the same problem and I wondered if you ever came to any resolution (post-thread comments)?

Thanks.

I haven't worked out anything concrete just yet. `json.loads()` takes bytes, though, so we're just dumping everything as it goes in, and losing it when it comes out.

Thanks for the answer Chris, even if it isn't the one I was hoping for :(