search

aio-libs / aiomysql

aiomysql is a library for accessing a MySQL database from the asyncio
https://aiomysql.rtfd.io
MIT License
1.75k stars 255 forks source link

INSERT into json column on MySQL 8 fails #970

Open cemrehancavdar opened 11 months ago

cemrehancavdar commented 11 months ago

Describe the bug

Escaping bytes with _binary prefix causes error on MySQL. SqlAlchemy doc mentions it: https://docs.sqlalchemy.org/en/20/dialects/mysql.html#dealing-with-binary-data-warnings-and-unicode

Also pymysql takes _binary_prefix as optional parameter with default value False and adds _binary prefix if true.

https://github.com/PyMySQL/PyMySQL/blob/c1d8063759a4a3968b0f7907e098554d9a8ad552/pymysql/connections.py#L515C21-L515C35

PyMySQL's behaviour should be more appropriate imho.

To Reproduce

Have a SqlAlchemy Model with JSON column and try to create a new record.

Expected behavior

Should able to create record.

Logs/tracebacks

OperationalError('(pymysql.err.OperationalError) (3144, "Cannot create a JSON value from a string with CHARACTER SET \'binary\'.")')

'INSERT INTO platform_configurations (platform_name, configuration, id) VALUES (%s, %s, %s)'
('string', b'{"additionalProp1":"string","additionalProp2":"string","additionalProp3":"string"}', '01HEN04BMEKFV1SH55084F9JWX')

Python Version

$ python --version
3.11.6

aiomysql Version

$ python -m pip show aiomysql

Name: aiomysql
Version: 0.2.0
Summary: MySQL driver for asyncio.
Home-page: https://github.com/aio-libs/aiomysql
Author: Nikolay Novik
Author-email: nickolainovik@gmail.com
License: MIT
Location: /usr/local/lib/python3.11/site-packages
Requires: PyMySQL
Required-by:

PyMySQL Version

$ python -m pip show PyMySQL

Name: PyMySQL
Version: 1.1.0
Summary: Pure Python MySQL Driver
Home-page: 
Author: 
Author-email: Inada Naoki <songofacandy@gmail.com>, Yutaka Matsubara <yutaka.matsubara@gmail.com>
License: MIT License
Location: /usr/local/lib/python3.11/site-packages
Requires: 
Required-by: aiomysql

SQLAlchemy Version

$ python -m pip show sqlalchemy

Name: SQLAlchemy
Version: 2.0.23
Summary: Database Abstraction Library
Home-page: https://www.sqlalchemy.org
Author: Mike Bayer
Author-email: mike_mp@zzzcomputing.com
License: MIT
Location: /usr/local/lib/python3.11/site-packages
Requires: greenlet, typing-extensions
Required-by: advanced_alchemy, alembic

OS

Alpine linux devcontainer environment

Database type and version

SELECT VERSION();

MYSQL 8.0.33

Additional context

No response

Code of Conduct