UTF-8列的SQLAlchemy结果是'str'类型，为什么？ [英] SQLAlchemy result for UTF-8 column is of type 'str', why?

问题描述

  result = engine.execute（'SELECT utf_8_field FROM table'）
  

数据库是MySQL，列类型是TEXT，具有UTF-8编码。返回的utf_8_field的类型是str，即使在创建引擎时设置选项convert_unicode = True。现在发生的是，如果我的字符串中有一个字符，如é（不是7位ASCII，但是在扩展ASCII集合中），则在尝试执行此操作时会收到UnicodeDecodeError：

  utf_8_field.encode（utf-8）
  

确切的错误是：

  UnicodeDecodeError：'ascii'编解码器无法解码字节0xe9在位置1：序数不在范围内（128）
  

当查看这个，我发现str。编码不支持扩展的ASCII字符集！我发现这真的很奇怪，但这是另一个问题。

我不明白是为什么SQLAlchemy不给我一个unicode字符串。我以前使用DB-API，这是正常工作。我也没有SQLAlchemy表对象的表，这就是为什么我正在使用一个执行命令。

任何想法？

解决方案

如果要自动转换数据，您应该

  create_engine（' mysql + mysqldb：/// mydb？charset = utf8'）
  

设置 use_unicode 单独不会告诉sqlalchemy要使用哪个字符集。

I have a SQL query that I execute like this with an SQLAlchemy engine:

result = engine.execute('SELECT utf_8_field FROM table')

The database is MySQL and the column type is TEXT with UTF-8 encoding. The type of the returned utf_8_field is "str", even if I set the option convert_unicode=True when creating the engine. What happens now is that if I have a character like 'é' in my string (which is not in 7-bit ASCII, but is in the extended ASCII set), I get a UnicodeDecodeError when trying to execute this:

utf_8_field.encode("utf-8")

The exact error is:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 in position 1: ordinal not in range(128)

When looking into this, I found that str.encode do not support the extended ASCII character set! I find this really strange, but that's another question.

What I don't understand is why SQLAlchemy is not giving me a unicode string. I was previously using DB-API and that was working fine. I also don't have SQLAlchemy table objects for my tables yet, that's why I'm using an execute command.

Any idea?

解决方案

If you want the data converted automatically, you should specify the charset when you create the engine:

create_engine('mysql+mysqldb:///mydb?charset=utf8')

Setting use_unicode alone won't tell sqlalchemy which charset to use.

这篇关于UTF-8列的SQLAlchemy结果是'str'类型，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！