2

If I create a simple table in MySQL with binary data:

CREATE TABLE foo ( bar binary(4) )
INSERT INTO foo (bar) VALUES ( UNHEX('de12') )

Then try to read it using the MySQL Connector/Python:

import mysql.connector
conn = mysql.connector.connect(database='test', user='test')
cursor = conn.cursor()
cursor.execute("SELECT * FROM foo")
cursor.fetchall()

I get this error:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xde in position 0: invalid continuation byte

I don't understand why the Python MySQL connector is trying to decode the column as UTF-8. I just want to get the raw binary data, how can I do this?

(Python 2.7.10, Mysql 5.7.22, MySQL Connector/Python 8.0.12)

James
  • 3,597
  • 2
  • 39
  • 38
  • `connection.set_charset_collation('latin1')` worked around it for me, for the dumb reason that latin1 will never have decode errors. Connector seems to have gone ignorant of BINARY data types. – Bob Stein May 19 '19 at 14:33

1 Answers1

2

Use raw connection (or raw cursor) to execute the fetch.

import mysql.connector
conn = mysql.connector.connect(database='test', 
user='test',raw=True)
cursor = conn.cursor()
cursor.execute("SELECT * FROM foo")
cursor.fetchall()

By default, python fetch command tries to convert the binary data to a string. When it tries this, it encounters a byte sequence which is not allowed in utf-8-encoded strings. Setting raw mode to True overrides this behaviour and makes the results be returned as is, rather than converted to Python types.

zilmano
  • 21
  • 2