我面临的问题与此处发布的问题相同 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文档的报价。
更多推荐
SQLAlchemy和Postgres UnicodeDecodeError
发布评论