I reproduced above error:
Traceback (most recent call last):
File "demo.py", line 16, in <module>
cursor.execute(query, ())
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte '0xff ... '
in position 0: invalid start byte
Using versions:
$ python --version
Python 2.7.10
>>> mysql.connector.__version__
'8.0.15'
With python code
#!/usr/bin/python
# -*- coding: utf-8 -*-
import mysql.connector
conn = mysql.connector.connect(
user='asdf',
password='asdf',
host='1.2.3.4',
database='the_db',
connect_timeout=10)
cursor = conn.cursor(buffered=True) #error is raised here
try:
query = ("SELECT data_blob FROM blog.cmd_table")
cursor.execute(query, ())
except mysql.connector.Error as err: #error is caught here
#error is caught here, and printed:
print(err) #printed thustly
Using a python variable "raw byte binary" populated by python's open( like this:
def read_file_as_blob(filename):
#r stands for read
#b stands for binary
with open(filename, 'rb') as f:
data = f.read()
return data
So the problem is somewhere between the encoding transform of data in the file -> the encoding of data for mysql blob -> and how mysql lifts that blob and converts it back to utf-8.
Two solutions:
Solution 1 is exactly as AHalvar said, set use_pure=True parameter and pass to mysql.connector.connect( ... ). Then mysteriously it just works. But good programmers will note that deferring to mysterious incantation is a bad code smell. Fixes by brownian motion incur technical debt.
Solution 2 is to encode your data early and often, and prevent double re-encoding and double data decoding which is the source of these problems. Lock it down to a common encoding format as soon as possible.
The gratifying solution for me was forcing utf-8 encoding earlier in the process. Enforcing UTF-8 everywhere.
data.encode('UTF-8')
The unicode pile of poo represents my opinion on such babysitting of character encoding between various devices on different operating systems and encoding schemes.
