SQLAlchemy和Postgres UnicodeDecodeError [英] SQLAlchemy and 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
PGCLIENTENCODING
environment var in you OS:export PGCLIENTENCODING=UTF8
- Edit
client_encoding
in postgres config file - Use
ALTER SYSTEM
(you must refresh config after that withSELECT 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!