How can I retrieve binary data using Mysql Python connector?

Question

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)

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
– Bob Stein, Commented May 19, 2019 at 14:33

zilmano · Accepted Answer · 2019-03-06 22:06:43Z

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.

answered Mar 6, 2019 at 22:06

zilmano

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I retrieve binary data using Mysql Python connector?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related