SQLAlchemy和Postgres UnicodeDecodeError

本文介绍了SQLAlchemy和Postgres UnicodeDecodeError的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我面临的问题与此处发布的问题相同 SQLAlchemy和UnicodeDecodeError 。当我从表中获取结果时，出现错误：

The problem I am facing is same as posted here SQLAlchemy and UnicodeDecodeError. When I fetch results from a table I get the error:

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

建议的两个解决方案都使用sy.setdefaultencoding（'utf8 '）在python 中，并将附加字符集参数传递给连接字符串，如下所示：

The two solutions proposed are using sy.setdefaultencoding('utf8') in python and passing additional charset argument to the connection string as follows:

conn_str='postgresql+psycopg2://'+str(dbhost)+':' + str(port) + '/postgres?charset=utf8'

两个解决方案似乎都不能解决问题。我还能调试什么？该表实质上是各个国家的GIS数据。因此表具有特殊性。

Both solution seem not to fix the problem. What else can I debug? The table is essentially GIS data for various countries. So the tables have special character.

推荐答案

服务器和客户端的编码似乎不同。您可以通过以下命令来验证是否执行此操作：

It seems that encoding is different from server to client. You can verify this issuing these commands:

SHOW client_encoding; --Equivalent to: SELECT current_setting('client_encoding'); SHOW server_encoding; --Equivalent to: SELECT current_setting('server_encoding');

PostgreSQL自动转换为客户端编码。在您的环境中，两者可能都不同。您可以通过多种方式配置 client_encoding ：

PostgreSQL automatic converts to client encoding. Probably both are different in your environment. You can configure client_encoding by many ways:

• 使用在应用中打开连接时使用SET 命令： SET client_encoding ='UTF-8';
• 使用在您的应用中打开连接时 set_config 函数： SELECT set_config（'client_encoding'，'UTF-8'，true）;
• 在您的操作系统中配置 PGCLIENTENCODING 环境变量： export PGCLIENTENCODING = UTF8
• 在postgres配置文件中编辑 client_encoding
• 使用 ALTER SYSTEM （之后必须使用 SELECT pg_reload_conf（）; 刷新配置）： ALTER SYSTEM SET client_encoding ='UTF-8 ';

• Using SET command when open connection in you app: SET client_encoding = 'UTF-8';
• Using set_config function when open connection in you app: SELECT set_config('client_encoding', 'UTF-8', true);
• Configure PGCLIENTENCODINGenvironment var in you OS: export PGCLIENTENCODING=UTF8
• Edit client_encoding in postgres config file
• Use ALTER SYSTEM (you must refresh config after that with SELECT pg_reload_conf();): ALTER SYSTEM SET client_encoding = 'UTF-8';

更新：不幸的是，无法从SQL_ASCII。

Update: Unfortunately it's not possible to enable automatic conversion from SQL_ASCII.

如果客户端字符集定义为SQL_ASCII，则无论服务器的字符集如何，都将禁用编码转换。就服务器而言，仅就不适合使用SQL_ASCII，除非您使用处理全ASCII数据。

If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.

来自 Postgres文档的报价。