UnicodeDecodeError with pg8000 1.9.10

[Raphael Mutschler]

since i've updated pg8000 from 1.08 to 1.9.10 i get UnicodeDecodeError when i query a databse that contains any unicode chars (in this special case german umlauts) setting the encoding='utf-8' on option in sqlalchemy seems to be ignored completely since 1.9

some traceback:

Traceback (most recent call last):
  File "debug.py", line 37, in <module>
    main.import_mediaserver()
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/synodlnatrakt/main.py", line 207, in import_mediaserver
    for result in dbresult:
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/orm/query.py", line 2176, in __iter__
    return self._execute_and_instances(context)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/orm/query.py", line 2191, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1450, in execute
    params)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1690, in _execute_context
    context)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/default.py", line 335, in do_execute
    cursor.execute(statement, parameters)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 531, in execute
    self._c.execute(self, operation, args)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1554, in execute
    self.handle_messages(cursor)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1619, in handle_messages
    self._read(data_len - 4), cursor)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1607, in handle_DATA_ROW
    row.append(func(data, data_idx, vlen))
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 976, in text_recv
    data[offset: offset + length], self._client_encoding)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 64: ordinal not in range(128)

with 1.08 it works perfeclty but sadly 1.08 gives me a different error sometimes: pg8000.errors.NotSupportedError: type oid 705 not mapped to py type

[Mike Bayer]

SQLAlchemy has no logic specific to pg8000 1.9.

Looking at their current docs, there is still no such parameter "encoding"; pg8000 always gets this from the client connection:

http://pythonhosted.org//pg8000/dbapi.html#pg8000.connect

I've searched all of pg8000's docs for "encoding" and the only match is in their release notes: http://pythonhosted.org//pg8000/release_notes.html?highlight=encoding.

SQLAlchemy doesn't do any special work with unicode and pg8000, both statements and bound parameter values are passed right through so this would appear to be a regression in pg8000 itself.

[Mike Bayer]

the docs are maybe not entirely clear on this, but that's what "Set the "encoding" parameter on create_engine(), to the same as the client encoding, usually "utf-8"" means - it does not mean SQLAlchemy is passing this throguh, it means that it is helpful that SQLAlchemy knows what encoding pg8000 is using in any case. See http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#id3.

[Mike Bayer]

• clarify that the pg8000 backend has no impact of any kind on the pg8000 DBAPI's encoding behavior, fixes #3112.

→ <<cset 6a7ef3436180>>

[Mike Bayer]

• clarify that the pg8000 backend has no impact of any kind on the pg8000 DBAPI's encoding behavior, fixes #3112.

→ <<cset 893b4964810f>>

[Raphael Mutschler]

so sqlalchemy's encoding on create_engine() has simply no effect on pg8000 itself? still strange that with version < 1.9 everything works pretty well and i get proper utf-8 encoded querys but if im using a version > 1.9 im getting UnicodeDecode Errors all the way :/

Thanks for the help i'll try to talkt to the dev of pg8000 directly