How return str from mysql using mysql.connector?

问题:

I use Python3, and try use MySQL Connector/Python from mysql.com

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

回答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 type(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=...)

回答2:

Based on your SQL query:

select caption from domains

You can read caption column directly from the fetched row

Example 1:

print row.caption

Example 2:

print row[ 0 ]

Documentation Reference:

1. Rows - Row API documentation
2. Cursor - Cursor API documentation

回答3:

I don't think that you can get the cursor to return strings. The MySQL Connector Documentation says that they chose to return bytearrays so that they only have to maintain one codebase for both Python2 and Python3:

With the use of “raw” cursors, the returned values is of the bytearray type. This is necessary for having both Python 2 and 3 return the same data.

I addressed this issue using a list comprehension to decode each bytearray in the row:

for row in cursor:
    type_fixed_row = tuple([el.decode('utf-8') if type(el) is bytearray else el for el in row])
    print( type_fixed_row )

回答4:

An easy way to solve this issue is to make sure that you are retrieving 'strings' from your MySQL table. To do so, you just have to add a CAST in your query as follows:

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

This should work for you.