How to return str from MySQL using mysql.connector?

Question

I am trying to use the MySQL Connector / Python from mysql.com with Python 3.

I have UTF-8 encoded tables and when I select rows all my chars columns are returned as bytearray . This makes some confusion.

How can I get str directly?

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() 

exit:

 (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

It seems like this happens when you use binary sort, at least the same thing happened to me. To convert byte arrays to Unicode strings, you can add your own 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

The MySQL Connector returns rows (as stored using CHAR , VARCHAR and TEXT data types) as bytearray when the corresponding columns are defined with binary sorting (for example, utf8_bin ). You must call .decode() for the values ​​to get the Python strings, for example:

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

However, unless you have specific requirements to use utf8_bin , it is much better to use utf8mb4 characters with utf8mb4_unicode_ci matching at the database level. This would solve your problem and provide full Unicode support. See this and this in more detail.

Answer 3

Adding mysql-connector-python==8.0.17 to the require.txt file solved this problem for me.

Answer 4

Based on your SQL query:

 select caption from domains 

You can read the caption column directly from the extracted row

Example 1 :

 print row.caption 

Example 2 :

 print row[ 0 ] 

Documentation link :

• Strings - String API documentation
• Cursor Documentation - Cursor API

Answer 5

I do not think you can force the cursor to return rows. The MySQL Connector Documentation says they decided to return bytearrays, so they only need to support one code base for Python2 and Python3:

Using raw cursors, return values ​​are of type bytearray. This is necessary so that both Python 2 and 3 return the same data.

I examined this problem using list comprehension to decode every byte in a string:

 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 ) 

Answer 6

An easy way to solve this problem is to make sure that you are extracting the "rows" from your MySQL table. To do this, you just need to add CAST to 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.