使用Python将UTF-8字符串写入MySQL

Question

我正在尝试将用户帐户数据从Active Directory推送到我们的MySQL服务器。这可以无缝完成，但某些字符串以编码版本显示了umlauts和其他特殊字符。

Active Directory返回一个使用此示例格式的字符串：M\xc3\xbcller 实际上，这是“Müller”的UTF-8编码，但我想要写入数据库的是Müller而不是M\xc3\xbcller。

我尝试使用以下代码转换字符串，但结果在数据库中仍是相同的字符串： tempEntry[1] = tempEntry[1].decode("utf-8") 如果我在python控制台上运行print "M\xc3\xbcller".decode("utf-8")，输出就是正确的。

有没有办法正确地插入这个字符串？我需要这种特定的格式给一个Web开发人员，他想要确切的格式，我不知道为什么他不能直接使用PHP转换字符串。

额外信息：我正在使用MySQLdb；表格和列编码是utf8_general_ci。

- Raptor

Answer 1

如@marr75所建议的，确保在您的连接上设置charset ='utf8'。设置use_unicode=True并不是严格必要的，因为它被设置字符集所隐含。

然后请确保您传递的是unicode对象给数据库连接，因为它将使用您传递给游标的字符集进行编码。如果您传递的是一个utf8编码的字符串，则当其到达数据库时会进行双重编码。

所以，请参考以下内容：

conn = MySQLdb.connect(host="localhost", user='root', password='', db='', charset='utf8')
data_from_ldap = 'M\xc3\xbcller'
name = data_from_ldap.decode('utf8')
cursor = conn.cursor()
cursor.execute(u"INSERT INTO mytable SET name = %s", (name,))

你也可以尝试通过传递 init_command 参数来强制连接使用 utf8，不过我不确定是否需要这样做。测试 5 分钟应该能帮助你决定。

conn = MySQLdb.connect(charset='utf8', init_command='SET NAMES UTF8')

另外，虽然4.1版本已经很旧了，但还是要确保您使用的是MySQL >= 4.1

- Rob Cowie

---

所有MySQLdb.execute语句都需要像这样解码为Unicode并以字符串形式提交吗？我的测试结果是数据库中的数据是Unicode而不是UTF8编码，但如果我直接提交Unicode，则会得到Latin1垃圾数据。 - Marc Maxmeister

Answer 2

假设你正在使用MySQLdb，创建连接时需要传递use_unicode=True和charset="utf8"参数。

更新: 如果我针对一个测试表运行以下代码，将会得到-

>>> db = MySQLdb.connect(host="localhost", user='root', passwd='passwd', db='sandbox', use_unicode=True, charset="utf8")
>>> c = db.cursor()
>>> c.execute("INSERT INTO last_names VALUES(%s)", (u'M\xfcller', ))
1L
>>> c.execute("SELECT * FROM last_names")
1L
>>> print c.fetchall()
(('M\xc3\xbcller',),)

这是“正确的方式”，字符被正确地存储和检索，你的朋友编写的PHP脚本只是在输出时没有正确处理编码。

正如Rob所指出的那样，使用use_unicode和charset结合起来在连接方面很啰嗦，但我对标准库之外的任何最有用的Python库都有一种自然的偏执，所以我试图明确表达，以便如果库发生变化，易于找到错误。

- marr75

---

你的数据库列使用了什么编码集？尝试使用 utf8-bin 编码。你可能已经完全正确地传输了数据，但它是用一些不包含你正在使用的字符的编码方式编写的。 - marr75

编码方式为utf8_general_ci，我尝试将表格和列设置为utf8_bin，但没有效果。 - Raptor

我认为这是正确的方向，我尝试手动设置字符串，这似乎有效。我认为我的Python转换有些问题。我将在接下来的几天继续调试，并在可能的情况下发布解决方案。顺便说一句：谢谢你的测试！ - Raptor

@Raptor：这里的关键是将_unicode_对象传递给c.execute，而不是utf8编码的字符串。MySQLdb会在传入时为您进行编码（使用charset参数），并在传出时为您解码（如果use_unicode为True）。 - Rob Cowie

@Rob Cowie 我认为你对raptor遇到的问题有点头绪，u'M\xfcller'是"M\xc3\xbcller".decode("utf8")的输出。既然这个步骤出现在他的回答中，我就假设他已经解码了字符串，我会更新我的示例代码以反映这一步骤。 - marr75

Answer 3

import MySQLdb

# connect to the database
db = MySQLdb.connect("****", "****", "****", "****") #don't use charset here

# setup a cursor object using cursor() method
cursor = db.cursor()

cursor.execute("SET NAMES utf8mb4;") #or utf8 or any other charset you want to handle

cursor.execute("SET CHARACTER SET utf8mb4;") #same as above

cursor.execute("SET character_set_connection=utf8mb4;") #same as above

# run a SQL question
cursor.execute("****")

#and make sure the MySQL settings are correct, data too

- YEH

---

这是唯一一个包含所有可能的Unicode字符，甚至包括表情符号的答案。谢谢。 - james-see

Answer 4

我找到了解决问题的方法。将字符串使用.decode('unicode_escape').encode('iso8859-1').decode('utf8')进行解码后，最终确实有效了。现在一切都已经按照预期插入。完整的其他解决方案可以在这里找到：Working with unicode encoded Strings from Active Directory via python-ldap

- Raptor

Answer 5

最近我遇到了一个问题，即字段值是字节字符串而不是Unicode。以下是一些分析。

概述

通常来说，要从游标得到Unicode值，唯一需要做的就是在连接构造函数中传递charset参数，并且拥有非二进制表字段（例如utf8_general_ci）。传递use_unicode是无用的，因为只要charset有值，它就被设置为true。

MySQLdb尊重游标描述字段类型，因此如果游标中有一个DATETIME列，则这些值将被转换为Python datatime.datetime实例，DECIMAL转换为decimal.Decimal等等，但二进制值将用字节字符串表示。大多数解码器都在MySQLdb.converters中定义，可以通过为连接构造函数提供conv参数来按实例覆盖它们。

但是Unicode解码器在这里是个例外，这很可能是设计上的缺陷。它们直接附加到连接实例转换器中的构造函数。因此，只能在实例上覆盖它们。

解决方法

让我们来看看出现问题的代码。

import MySQLdb

connection = MySQLdb.connect(user = 'guest', db = 'test', charset = 'utf8')
cursor     = connection.cursor()

cursor.execute(u"SELECT 'abcdё' `s`, ExtractValue('<a>abcdё</a>', '/a') `b`")

print cursor.fetchone() 
# (u'abcd\u0451', 'abcd\xd1\x91')
print cursor.description 
# (('s', 253, 6, 15, 15, 31, 0), ('b', 251, 6, 50331648, 50331648, 31, 1))
print cursor.description_flags 
# (1, 0)

它显示b字段返回为字节字符串而不是Unicode。但它不是二进制的，MySQLdb.constants.FLAG.BINARY & cursor.description_flags[1] (MySQLdb字段标志)。这似乎是库中的错误（已经开放了#90）。但我认为原因是MySQLdb.constants.FIELD_TYPE.LONG_BLOB (cursor.description[1][1] == 251, MySQLdb字段类型)根本没有转换器。

import MySQLdb
import MySQLdb.converters as conv
import MySQLdb.constants as const

connection = MySQLdb.connect(user = 'guest', db = 'test', charset = 'utf8')
connection.converter[const.FIELD_TYPE.LONG_BLOB] = connection.converter[const.FIELD_TYPE.BLOB]
cursor = connection.cursor()

cursor.execute(u"SELECT 'abcdё' `s`, ExtractValue('<a>abcdё</a>', '/a') `b`")

print cursor.fetchone()
# (u'abcd\u0451', u'abcd\u0451')
print cursor.description
# (('s', 253, 6, 15, 15, 31, 0), ('b', 251, 6, 50331648, 50331648, 31, 1))
print cursor.description_flags
# (1, 0)

因此，通过操作连接实例converter字典，可以实现所需的Unicode解码行为。

如果您想要覆盖默认行为，以下是构造函数后可能文本字段的字典条目。

import MySQLdb
import MySQLdb.constants as const

connection = MySQLdb.connect(user = 'guest', db = 'test', charset = 'utf8')
print connection.converter[const.FIELD_TYPE.BLOB]
# [(128, <type 'str'>), (None, <function string_decoder at 0x7fa472dda488>)]

MySQLdb.constants.FLAG.BINARY == 128。这意味着，如果一个字段有二进制标志，它将是str，否则将应用unicode解码器。因此，如果您想尝试转换二进制值，可以弹出第一个元组。

- saaj

Answer 6

“想要回答上面的问题，但是没有足够的声望...”

“在这种情况下你无法得到Unicode结果的原因是：”

>>> print c.fetchall()
(('M\xc3\xbcller',),)

这是来自MySQLdb 1.2.x的一个bug，涉及到*_bin排序规则，请参见：

http://sourceforge.net/tracker/index.php?func=detail&aid=1693363&group_id=22307&atid=374932 http://sourceforge.net/tracker/index.php?func=detail&aid=2663436&group_id=22307&atid=374932

在这种特殊情况下（排序规则为utf8_bin或[任何其他]_bin...），您必须期望“原始”值，这里是utf-8（是的，这很糟糕，因为没有通用的解决方法）。

- lacorbeille

Answer 7

还有一种情况可能比较罕见。

如果您首先在mysqlworkbench中创建模式，您将遇到编码错误，并且无法通过添加字符集配置来解决它。

这是因为mysqlworkbench默认使用latin1创建模式，所以您应该首先设置字符集！

- dogewang

Answer 8

而 db.set_character_set('utf8')，是否意味着 use_unicode=True？

- Sérgio

---

抱歉回复晚了：是的，字符集意味着使用 use_unicode = True。 - Raptor