Sqlalchemy And Postgres 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

Solution 1:

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

SHOW client_encoding; --Equivalentto: SELECT current_setting('client_encoding');
SHOW server_encoding; --Equivalentto: SELECT current_setting('server_encoding');

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

• 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';

Update: Unfortunately it's not possible to enable automatic conversion from SQL_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.

Quote from Postgres documentation.