如何在SQLAlchemy的`create_engine`中使用`charset`和`encoding`(创建 pandas 数据框)? [英] how to use `charset` and `encoding` in `create_engine` of SQLAlchemy (to create pandas dataframe)?

问题描述

我对charset和 encoding 在 SQLAlchemy 中工作.我了解(并已阅读)字符集和编码之间的区别，而且我对

I am very confused with the way charset and encoding work in SQLAlchemy. I understand (and have read) the difference between charsets and encodings, and I have a good picture of the history of encodings.

我在latin1_swedish_ci中的MySQL中有一个表(为什么?可能是由于

I have a table in MySQL in latin1_swedish_ci (Why? Possible because of this). I need to create a pandas dataframe in which I get the proper characters (and not weird symbols). Initially, this was in the code:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db')
sql_query = "select * from table1"
df = pandas.read_sql(sql_query, connect_engine)

我们开始遇到Š字符的麻烦(对应于u'\u0160' unicode，但是却得到了'\ x8a').我希望它能起作用:

We started having troubles with the Š character (corresponding to the u'\u0160' unicode, but instead we get '\x8a'). I expected this to work:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db', encoding='utf8') 

但是，我继续得到'\x8a'，我意识到，鉴于编码参数的默认值为utf8，这是有意义的.因此，然后，我尝试encoding='latin1'解决该问题:

but, I continue getting '\x8a', which, I realized, makes sense given that the default of the encoding parameter is utf8. So, then, I tried encoding='latin1' to tackle the problem:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db', encoding='latin1')

但是，我仍然得到相同的'\ x8a'.需要明确的是，在两种情况下(encoding='utf8'和encoding='latin1')，我都可以执行mystring.decode('latin1')，但不能执行mystring.decode('utf8').

but, I still get the same '\x8a'. To be clear, in both cases (encoding='utf8' and encoding='latin1'), I can do mystring.decode('latin1') but not mystring.decode('utf8').

然后，我在连接字符串(即'mysql://user:password@1.1.1.1/db?charset=latin1')中重新发现了charset参数.在尝试了所有可能的字符集和编码组合之后，我发现这一工作有效:

And then, I rediscovered the charset parameter in the connection string, i.e. 'mysql://user:password@1.1.1.1/db?charset=latin1'. And after trying all possible combinations of charset and encoding, I found that this one work:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db?charset=utf8')

如果有人可以向我解释如何正确使用连接字符串中的charset和create_engine中的encoding参数，我将不胜感激?

I would appreciate if somebody can explain me how to correctly use the charset in the connection string, and the encoding parameter in the create_engine?

推荐答案

encoding 是用于在SQLAlchemy 中对进行编码/解码的编解码器.从文档中:

encoding is the codec used for encoding/decoding within SQLAlchemy. From the documentation:

对于那些被检测为不支持DBAPI的情况 Python unicode对象，此编码用于确定 源/目标编码.在以下情况下不使用: DBAPI直接处理unicode.

For those scenarios where the DBAPI is detected as not supporting a Python unicode object, this encoding is used to determine the source/destination encoding. It is not used for those cases where the DBAPI handles unicode directly.

[...]

要正确配置系统以容纳Python unicode对象， DBAPI应该配置为最大程度地处理unicode 适当的学位[...]

To properly configure a system to accommodate Python unicode objects, the DBAPI should be configured to handle unicode to the greatest degree as is appropriate [...]

mysql-python直接处理unicode，因此无需使用此设置.

mysql-python handles unicode directly, so there's no need to use this setting.

charset是特定于mysql-python驱动程序的设置.从文档:

charset is a setting specific to the mysql-python driver. From the documentation:

此字符集是用于连接的客户端字符集.

此设置控制服务器上的三个变量 ，特别是 character_set_results ，是您感兴趣的内容.设置后，字符串将作为unicode对象返回.

This setting controls three variables on the server, specifically character_set_results, which is what you are interested in. When set, strings are returned as unicode objects.

请注意，这仅在数据库中具有latin1编码的数据时适用.如果您将utf-8字节存储为latin1，则最好使用encoding代替.

Note that this applies only if you have latin1 encoded data in the database. If you've stored utf-8 bytes as latin1, you may have better luck using encoding instead.

这篇关于如何在SQLAlchemy的`create_engine`中使用`charset`和`encoding`(创建 pandas 数据框)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！