Description:
I was surprised today to find that Connector/Python tries to interpret TEXT/BLOB column as JSON when using cursors from PooledMySQLConnection.
Notably, if the value on the row is a numeric value, it comes out of the cursor as integer instead of string as one would expect for a TEXT/BLOB column. If the value is just a normal string, it comes out as string.
But this happens only with PooledMySQLConnection - with normal MySQLConnection the behaviour is to return strings.
The logic that converts the TEXT/BLOBs is in MySQLConverter class _BLOB_to_python method which internally calls _JSON_to_python.
I'm using MySQL 5.6.
How to repeat:
1. Create a table with a single TEXT column and INSERT some data into it:
```
CREATE TABLE testdata (
value TEXT
);
INSERT INTO testdata (value) VALUES
('just a string', '123456', 'hello', 'false', 'true');
```
2. Query the values with MySQL Connector / Python using just regular, unpooled connection:
```
import mysql.connector
connection_details = {'host': 'localhost','port': 3306, 'database': 'testdb', 'user': 'johndoe', 'password': 'secret' }
connection_regular = mysql.connector.connect(**connection_details)
cursor_regular = connection_regular.cursor()
cursor_regular.execute("SELECT value FROM testdata")
for row in cursor_regular:
print(f"value fetched via regular connection: {row[0]} {type(row[0])}")
```
The output will be:
```
value fetched via regular connection: just a string <class 'str'>
value fetched via regular connection: 123456 <class 'str'>
value fetched via regular connection: true <class 'str'>
value fetched via regular connection: false <class 'str'>
value fetched via regular connection: hello <class 'str'>
```
3. Create a connection pool and query the values through a pooled connection:
```
connection_pool = mysql.connector.pooling.MySQLConnectionPool(**connection_details)
connection_pooled = connection_pool.get_connection()
cursor_pooled = connection_pooled.cursor()
cursor_pooled.execute("SELECT value FROM testdata")
for row in cursor_pooled:
print(f"value fetched via pooled connection: {row[0]} {type(row[0])}")
```
The output from this will be:
```
value fetched via pooled connection: just a string <class 'str'>
value fetched via pooled connection: 123456 <class 'int'>
value fetched via pooled connection: True <class 'bool'>
value fetched via pooled connection: False <class 'bool'>
value fetched via pooled connection: hello <class 'str'>
```
Note the difference in the printed data types for the values.
Suggested fix:
* Make the documentation clear that there's difference on how TEXT/BLOB columns are handled with cursors from pooled connections.
* Allow users to opt-out from the JSON parsing. Preferably this should be default, as this is very surprising behaviour and inconsistent.