How to make psycopg decode strings use error="replace" mode instead of "strict"

[mk-luo]

I got error:
[16613] psycopgmodule: initializing psycopg 2.9.9 (dt dec pq3 ext lo64)
[16613] psycopgmodule: configuring libpq libcrypto callbacks
[16613] psycopgmodule: initializing module constants
[16613] psycopgmodule: initializing module types
[16613] psycopgmodule: initializing datetime module
[16613] psycopgmodule: initializing encodings table
[16613] psycopgmodule: initializing adapters
[16613] psycopgmodule: initializing basic exceptions
[16613] psycopgmodule: initializing sqlstate exceptions
[16613] psycopgmodule: module initialization complete
[16613] psyco_connect: dsn = 'host=192.168.38.4 port=3999 user=csdu password=Pwd123!!! client_encoding=SQL_ASCII dbname=csducatalog', async = 0
[16613] connection_setup: init connection object at 0x7f881b18a980, async 0, refcnt = 1
[16613] con_connect: connecting in SYNC mode
[16613] conn_connect: new PG connection at 0x1fa42d0
[16613] conn_connect: server standard_conforming_strings parameter: on
[16613] conn_connect: server requires E'' quotes: NO
[16613] conn_connect: using protocol 3
[16613] conn_connect: client encoding: SQL_ASCII
[16613] clear_encoding_name: SQL_ASCII -> SQLASCII
[16613] conn_set_fast_codec: encoding=SQLASCII
[16613] conn_set_fast_codec: no fast codec
[16613] conn_connect: DateStyle ISO, YMD
[16613] connection_setup: good connection object at 0x7f881b18a980, refcnt = 1
[16613] psyco_conn_cursor: new unnamed cursor for connection at 0x7f881b18a980
[16613] cursor_setup: init cursor object at 0x7f881aa4cc00
[16613] cursor_setup: parameters: name = (null), conn = 0x7f881b18a980
[16613] cursor_setup: good cursor object at 0x7f881aa4cc00, refcnt = 5
[16613] psyco_conn_cursor: new cursor at 0x7f881aa4cc00: refcnt = 1
[16613] curs_execute: starting execution of new query
[16613] pq_execute: pg connection at 0x1fa42d0 OK
[16613] pq_begin_locked: pgconn = 0x1fa42d0, 0, status = 1
[16613] pq_execute_command_locked: pgconn = 0x1fa42d0, query = BEGIN
[16613] pq_execute: executing SYNC query: pgconn = 0x1fa42d0
[16613] SELECT * FROM Log WHERE logid=993
[16613] pq_execute: entering synchronous DBAPI compatibility mode
[16613] pq_fetch: pgstatus = PGRES_TUPLES_OK
[16613] pq_fetch: got tuples
[16613] _pq_fetch_tuples: looking for cast 23:
[16613] curs_get_cast: per-connection dict: (nil)
[16613] curs_get_cast: global dict: 0x7f881b1a1830
[16613] _pq_fetch_tuples: using cast at 0x7f881b1a1830 for type 23
[16613] _pq_fetch_tuples: looking for cast 23:
[16613] curs_get_cast: per-connection dict: (nil)
[16613] curs_get_cast: global dict: 0x7f881b1a1830
[16613] _pq_fetch_tuples: using cast at 0x7f881b1a1830 for type 23
[16613] _pq_fetch_tuples: looking for cast 1114:
[16613] curs_get_cast: per-connection dict: (nil)
[16613] curs_get_cast: global dict: 0x7f881b1cf950
[16613] _pq_fetch_tuples: using cast at 0x7f881b1cf950 for type 1114
[16613] _pq_fetch_tuples: looking for cast 25:
[16613] curs_get_cast: per-connection dict: (nil)
[16613] curs_get_cast: global dict: 0x7f881b1abe30
[16613] _pq_fetch_tuples: using cast at 0x7f881b1abe30 for type 25
[16613] curs_execute: res = 1, pgres = 0x1e751f0
[16613] _psyco_curs_prefetch: result = 0
[16613] _psyco_curs_buildrow: row 0, element 0, len 3
[16613] _psyco_curs_buildrow: val->refcnt = 1
[16613] _psyco_curs_buildrow: row 0, element 1, len 2
[16613] _psyco_curs_buildrow: val->refcnt = 15
[16613] _psyco_curs_buildrow: row 0, element 2, len 19
[16613] typecast_PYDATETIMETZ_cast: s = 2024-04-13 13:38:41
[16613] typecast_parse_date: len = 19, s = 2024-04-13 13:38:41
[16613] typecast_PYDATE_cast: tp = 0x1fc1e7a n = 3, len = 8, y = 2024, m = 4, d = 13
[16613] typecast_parse_time: len = 8, s = 13:38:41
[16613] typecast_PYDATETIMETZ_cast: n = 3, len = 0, hh = 13, mm = 38, ss = 41, us = 0, tzsec = 0
[16613] typecast_PYDATETIMETZ_cast: tzinfo: 0x7f88232517d0, refcnt = 5930
[16613] _psyco_curs_buildrow: val->refcnt = 1
[16613] _psyco_curs_buildrow: row 0, element 3, len 245
[16613] curs_close: cursor at 0x7f881aa4cc00 closed
Traceback (most recent call last):
File "test_utf8.py", line 32, in
jobobjs = cursor.fetchall()
File "/make-installer/envs/package_venv/lib/python3.7/site-packages/psycopg2-2.9.9-py3.7-linux-x86_64.egg/psycopg2/extras.py", line 104, in fetchall
res = super().fetchall()
UnicodeDecodeError: 'ascii' codec can't decode byte 0xce in position 65: ordinal not in range(128)
[16613] conn_close: PQfinish called
[16613] connection_dealloc: deleted connection object at 0x7f881b18a980, refcnt = 0
[16613] cursor_dealloc: deleted cursor object at 0x7f881aa4cc00, refcnt = 0

There is some non-utf8 characters in my database , is there any way to ignore the errors when decode error?

Python3 has buildin decode function , it can set errors='replace' . But I don't know how make psycopg use 'replace' mode instead of default 'stict'.

[dvarrazzo]

If you have a database with non-ascii characters the data cannot be decoded. This is not managed well by psycopg2 but is managed by psycopg 3 the right way: returning data without decoding. See documentation here: https://www.psycopg.org/psycopg3/docs/basic/adapt.html#strings-adaptation