Adjust the connection string to return non-binary data?

[Diego-Hernandez-Moodys]

Using MySQL 5. The module works great but my pandas dataframe is returning binary encoded strings. What's the current work-around as opposed to identifying the affected columns myself?

My MySQL 5 connection string looks like

url = f"mysql://{hostname}/{database}"

I tried

url = f"mysql://{hostname}/{database}?charset=utf8"

and I'm getting a URL parameter charset unknown.

[wangxiaoying]

Hi @Diego-Hernandez-Moodys , I'm not so sure what you mean here. Can you please give and example (including ddl for creating the table) to illustrate? Like printing the type of the current returned result and what is the one you are looking for.

[Diego-Hernandez-Moodys]

DDL

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE TABLE `myTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text COLLATE utf8mb4_unicode_ci,
)

import connectorx

url = "mysql://localhost/db_name"

# This table has over a million rows
query = """
SELECT *
FROM myTable
"""

dataframe = connectorx(url, query)
dataframe['comment']

Produces

...
2545255    b'new_security+potential_match'
2545256    b'new_security+potential_match'
2545257    b'new_security+potential_match'
2545258    b'new_security+potential_match'
2545259    b'new_security+potential_match'

I suspect it has something to do with the SqlAlchemy MySQL backend as I'm using one of mysqlclient and mysql-connector (sorry, I don't know which one because they're both in our requirements.txt)

import sqlalchemy

url = "mysql://localhost/db_name"
engine = sqlalchemy.create_engine(url)
# This table has over a million rows
query = """
SELECT *
FROM myTable
"""

dataframe = pandas.read_sql(query, engine) # takes forever
dataframe['comment']

Produces

...
2546616    new_security+potential_match
2546617    new_security+potential_match
2546618    new_security+potential_match
2546619    new_security+potential_match
2546620    new_security+potential_match

[wangxiaoying]

It looks like our upstream mysql driver returns MYSQL_TYPE_BLOB for TEXT types, so we parse the result as binary strings accordingly. I've opened an issue there: blackbeam/rust-mysql-simple#336.