How to return str from MySQL using mysql.connector?

Question

I'm trying to use MySQL Connector/Python from mysql.com with Python 3.

I have tables in UTF-8 coding, and when I fetch the rows, all my chars columns returned like bytearray. This is make some confusion.

How I can fetch directly str?

UPD:

# -*- coding: utf-8 -*-
import mysql.connector
con = mysql.connector.connect( user ="root", db = "vg_site_db", charset = 'utf8' )
cursor = con.cursor()
sql = """select caption from domains
"""
cursor.execute( sql )
row = cursor.fetchone()
while row is not None:
    print( row )
    row = cursor.fetchone()

output:

(bytearray(b'ezsp.ru'),)
(bytearray(b'eazyshop.ru'),)
(bytearray(b'127.0.0.1:8080'),)
(bytearray(b'rmsvet.ru'),)

I want:

('ezsp.ru',)
('eazyshop.ru',)
('127.0.0.1:8080',)
('rmsvet.ru',)

UPD2:

My tables use COLLATE utf8_bin.

Answer 1

Seems like this happens when you use binary collation, at least the same happened to me. To convert the bytearrays to Unicode strings, you can add a custom converter class:

class MyConverter(mysql.connector.conversion.MySQLConverter):

    def row_to_python(self, row, fields):
        row = super(MyConverter, self).row_to_python(row, fields)

        def to_unicode(col):
            if isinstance(col, bytearray):
                return col.decode('utf-8')
            return col

        return[to_unicode(col) for col in row]

sql = mysql.connector.connect(converter_class=MyConverter, host=...)

Answer 2

MySQL Connector returns strings (as stored using the CHAR, VARCHAR, and TEXT data types) as bytearrays when respective columns are defined with a binary collation (e.g. utf8_bin). You must call .decode() on values to get Python strings, e.g.:

for row in cursor:
    caption = row[0].decode()

That said, unless you have a specific requirement to use utf8_bin, it's a much better idea to use the utf8mb4 character set with the collation utf8mb4_unicode_ci on the database level. That would solve your problem and allow for full Unicode support. See this and this fore more details.