Skip to content

Added JSON field support #428

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
wants to merge 1 commit into from
Closed

Added JSON field support #428

wants to merge 1 commit into from

Conversation

desertkun
Copy link

As of MySQL 5.7.8, MySQL supports a native JSON field type, I've added support for this field. It gets automatically unpacked using json.loads, and falls back to {} in case of corrupted JSON.

According to this the JSON field type has code MYSQL_TYPE_JSON=245.

Thank you!

@methane
Copy link
Member

methane commented Feb 19, 2016

Without this PR, people can catche exceptins while decoding JSON. And people can use faster
JSON library (e.g. ujson) too.

Additionally, there is asymmetry issue.
cursor.execute("INSERT INTO tbl (json_column) VALUES (%s)", ([1,2,3],))
%s will be interpolated as (1, 2, 3), not "[1,2,3]".

So I don't want to convert JSON type in driver level.
You can convert it yourself easily.

@methane methane closed this Feb 19, 2016
@desertkun
Copy link
Author

Well, I can remove the convert code, but still the library need the definition of JSON type, since if you enable unicode support, it would crash if some non-ascii symbol will show up in the JSON object.

Also, while doing execute, the JSON is passed as a string, not a JSON-object, so only such a thing would work with this PR: cursor.execute("INSERT INTO tbl (json_column) VALUES (%s)", "[1,2,3]")

@methane
Copy link
Member

methane commented Feb 19, 2016

Also, while doing execute, the JSON is passed as a string, not a JSON-object, so only such a thing would work with this PR: cursor.execute("INSERT INTO tbl (json_column) VALUES (%s)", "[1,2,3]")

Yes, you should pass JSON to PyMySQL in string.
So PyMySQL should returns JSON in string.

@methane
Copy link
Member

methane commented Feb 19, 2016

Well, I can remove the convert code, but still the library need the definition of JSON type, since if you enable unicode support, it would crash if some non-ascii symbol will show up in the JSON object.

Then, your PR is wrong since json.loads() accepts only unicode. convert_json raises TypeError.

@zoltan-fedor
Copy link

Well, I can remove the convert code, but still the library need the definition of JSON type, since if you enable unicode support, it would crash if some non-ascii symbol will show up in the JSON object.

I have this exact problem.
Using SQLAlchemy with PyMySQL and trying to insert a JSON with non-ascii unicode characters - but crashing due to the non-ascii symbols.

@methane , how non-ascii JSON can be handled without this PR?

This is the current code:

        reg = db.session.query(models.Registration).filter(models.Registration.registration_hash == session['reg_hash']).one_or_none()
        reg.step2_details = json.dumps(step2_details, ensure_ascii=False).encode('utf8')
        reg.registration_status = 'step2_completed'
        db.session.add(reg)
        db.session.commit()

step2_details variable is:
{"munkahely szakterülete": "oktatás/képzés", "mi iránt érdeklődik a kiállításon": ["tűzvédelem, -oltás"], "honnan, kitől hallott a kiállításról": " hírlevél/portál"}

Error thrown:

  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/apps/sforum/venv1/lib/python3.5/site-packages/flask_debugtoolbar/__init__.py", line 125, in dispatch_request
    return view_func(**req.view_args)
  File "/apps/sforum/app/app/main/views.py", line 310, in registration_step2_1
    reg = db.session.query(models.Registration).filter(models.Registration.registration_hash == session['reg_hash']).one_or_none()
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/orm/query.py", line 2672, in one_or_none
    ret = list(self)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/orm/query.py", line 2743, in __iter__
    return self._execute_and_instances(context)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/orm/query.py", line 2766, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/sql/elements.py", line 262, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/base.py", line 1387, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/apps/sforum/venv1/src/sqlaclhemy/lib/sqlalchemy/engine/default.py", line 462, in do_execute
    cursor.execute(statement, parameters)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/cursors.py", line 308, in _query
    conn.query(q)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 820, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 1002, in _read_query_result
    result.read()
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 1292, in read
    self._read_result_packet(first_packet)
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 1347, in _read_result_packet
    self._read_rowdata_packet()
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 1385, in _read_rowdata_packet
    rows.append(self._read_row_from_packet(packet))
  File "/apps/sforum/venv1/lib/python3.5/site-packages/pymysql/connections.py", line 1396, in _read_row_from_packet
    data = data.decode(encoding)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 21: ordinal not in range(128)

@zoltan-fedor
Copy link

Actually my problem seems to be related to SQLAlchemy, because I managed to update the JSON field just using standard SQL.

step2_details_s = json.dumps(step2_details, ensure_ascii=False)
result = db.engine.execute("""update registration
                                set step2_details = '{1}', registration_status = 'step2_completed'
                                where registration_hash='{0}'""".format(session['reg_hash'], step2_details_s))

@methane methane mentioned this pull request Jul 4, 2016
Closed
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Aug 4, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Reviewers
No reviews
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
3 participants
@desertkun @methane @zoltan-fedor