How Can I Retrieve Binary Data Using Mysql Python Connector?

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 Connect

Solution 1:

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.