Setting query results encoding in cx_Oracle / UnicodeDecodeError with Chinese characters

Question

I'm working with a database containing a lot of Chinese characters. My code goes something like this:

connection = cx_Oracle.connect("%s/%s@%s:%s/%s" % (username, password, host, port, service_name))
cursor = connection.cursor()
cursor.execute('SELECT HOTEL_ID,CREATE_TIME,SOURCE,CONTENT,TITLE,RATE,UPDATE_TIME FROM T_FX_COMMENTS')

for row in cursor:
    # Stuff goes here
    pass

But I get this error:

Traceback (most recent call last):
  File "test.py", line 17, in <module>
    for row in cursor:
UnicodeDecodeError: 'gbk' codec can't decode byte 0xaf in position 26: illegal multibyte sequence

It seems GBK is not enough. I want to make cx-oracle give me GB18030 encoded results, instead of GBK. How do I do this?

cx_Oracle.Connection.encoding is read-only... I haven't found anything in the cx-oracle documentation that suggests I can do this.

I'm on Python 3.3.2 and cx-oracle 5.1.2. There must be something I'm missing here. Help is appreciated!

Alumna

Are you using Python 2 or 3? – Shellyshelman

Same scenario for me... did you find a solution? – Veronikaveronike

Answer 1

I was facing the same issue and I solved by setting the environment variable NLS_LANG to .AL32UTF8 (it seems a sort of "wildcard" that says "use utf-8 for any language")

Veronikaveronike

3.5 years later, this also still works. Python 3.6.3 + cx_Oracle 7.1.2 – Demonology

Answer 2

Try setting the NLS_LANG environment variable at the beginning of your program:

import os
os.environ["NLS_LANG"] = ".GB18030"

Shellyshelman

It's giving me this error: cx_Oracle.DatabaseError: ORA-12705: Cannot access NLS data files or invalid environment specified. I'm on Python 3.3.2 by the way. – Alumna

Answer 3

Use this:

import os
os.environ["NLS_LANG"] = ".zhs16gbk"

os.environ["NLS_LANG"] is for Oracle.So use the format of Oracle. I solved with this with my python 2.6.8 and Oracle 11g.

Hadhramaut