如何使用 Mysql Python 连接器检索二进制数据? [英] How can I retrieve binary data using Mysql Python connector?

问题描述

如果我在 MySQL 中用二进制数据创建一个简单的表:

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

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

然后尝试使用 MySQL 连接器/Python 读取它:

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

我收到此错误:

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

我不明白为什么 Python MySQL 连接器试图将列解码为 UTF-8.我只想获取原始二进制数据，我该怎么做?

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 连接器/Python 8.0.12)

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

推荐答案

使用原始连接(或原始游标)执行提取.

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

默认情况下，python fetch 命令尝试将二进制数据转换为字符串.当它尝试这样做时，它遇到了 utf-8 编码字符串中不允许的字节序列.将原始模式设置为 True 会覆盖此行为并使结果按原样返回，而不是转换为 Python 类型.

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.

这篇关于如何使用 Mysql Python 连接器检索二进制数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！