Как читать национальные символы (> 127) из Oracle US7ASCII, используя Python cx_Oracle?
У меня проблема с отображением национальных символов из базы данных Oracle 11 «ENGLISH_UNITED KINGDOM.US7ASCII» с использованием переменной среды Python 3.3 cx_Oracle 5.1.2 и «NLS_LANG». Тип столбца таблицы Db — «VARCHAR2 (2000 BYTE)»
Как отобразить строку «£ aÁÁÂÃÄÅÆÇÈ» из Oracle US7ASCII в Python? Это будет своего рода хак. Хэнк работает на всех других языках программирования Perl, PHP, PL/SQL и в Python 2.7, но в Python 3.3 он не работает.
В базе данных Oracle 11 я создал SECURITY_HINTS.ANSWER = «£ aÀÁÂÃÄÅÆÇÈ». Тип столбца ANSWER — «VARCHAR2 (2000 BYTE)».
Теперь, когда вы используете cx_Oracle и по умолчанию NLS_LANG, я получаю «¿a¿¿¿¿¿¿¿¿»,
и при использовании NLS_LANG = «ENGLISH_UNITED KINGDOM.US7ASCII» я получаю
Update1 Я сделал некоторый прогресс. При переключении на Python 2.7 и cx_Oracle 5.1.2 для Python 2.7 проблема исчезает (я получаю все> 127 символов из db). В Python 2 строки представлены в виде байтов, а в Python 3+ строки представлены как unicode. Мне все еще нужно наилучшее решение для Python 3.3.
Update2 Одним из возможных решений проблемы является использование rawtohex (utl_raw.cast_to_raw см. Ниже код.
исходный код моего скрипта ниже или в GitHub и GitHub Sollution
см. выход журнала ниже.
Я пытаюсь отобразить его на веб-странице Django. Но каждый персонаж приходит как персонаж с кодом 191 или 65533.
2 ответа
Если вы хотите получить неизмененную строку ASCII в клиентском приложении, лучший способ — передать ее из БД в двоичном режиме. Таким образом, первое преобразование должно быть отключено на стороне сервера с помощью пакета UTL_RAW и стандартной функции rawtohex .
Ваш выбор в cursor.execute может выглядеть так:
На клиенте вы получаете строку шестнадцатеричных символов, которые могут быть преобразованы в строковое представление с помощью функции binascii.unhexlify :
PS Я не знал языка Python , поэтому последнее утверждение может быть неверным.
Я думаю, вы не должны возвращаться к такой злой хитрости. NLS_LANG должен быть просто установлен на кодировку по умолчанию для клиента. Посмотрите на более прочные варианты:
- Расширьте набор символов базы данных, чтобы эти символы отображались в столбце VARCHAR.
- Обновите этот столбец до NVARCHAR. Возможно, вы можете использовать новое имя для этого столбца и создать вычисляемый столбец VARCHAR со старым именем для устаревших приложений для чтения.
- Храните базу данных как есть, но проверяйте данные, когда они вводятся, и заменяйте все символы, отличные от ASCII, приемлемым эквивалентом ASCII.
Какой вариант лучше всего зависит от того, насколько распространены символы, отличные от ASCII. Если там больше таблиц с одной и той же проблемой, я бы предложил вариант 1. Если это единственная таблица, вариант 2. Если есть только пара символов, отличных от ASCII, во всей таблице, и их потеря не такая большая сделка: вариант 3.
Одна из задач базы данных состоит в том, чтобы сохранить качество ваших данных в конце концов, и если вы обманываете, когда принудительно вставляете нелегальные символы в столбец, он не может выполнить свою работу должным образом, и каждый новый клиент, или обновление или экспорт придет с интересным новым неопределенное поведение.
EDIT: см. Комментарий Oracle на примере аналогичной настройки в NLS_LANG faq (мой акцент):
cx_Oracle — encoding query result to Raw
(both sys.stdout.encoding and sys.stdin.encoding are ‘UTF-8’).
Why is the variable value different than its print value? I need to get the raw value into a variable.
Original question:
I’m having an issue querying a BD and decoding the values into Python.
I confirmed my DB NLS_LANG using
I’ve both tried (which return the same)
how to get the queries result back to the proper ‘João’ ?
1 Answer 1
You already have the proper ‘João’, methinks. The difference between >>> ‘Jo\xc3\xa3o’ and >>> print ‘Jo\xc3\xa3o’ is that the former calls repr on the object, while the latter calls str (or probably unicode , in your case). It’s just how the string is represented.
Some examples might make this more clear:
Notice how the second and third result are identical. The original ldap_username currently is an ASCII string. You can see this on the Python prompt: when it is displaying an ACSII object, it shows as ‘ASCII string’ , while Unicode objects are shown as u’Unicode string’ — the key being the leading u .
So, as your ldap_username reads as ‘Jo\xc3\xa3o’ , and is an ASCII string, the following applies:
Summed up: you need to determine the type of the string (use type when unsure), and based on that, decode to Unicode, or encode to ASCII.
Name already in use
python-cx_Oracle / doc / src / user_guide / globalization.rst
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Character Sets and Globalization
Data fetched from, and sent to, Oracle Database will be mapped between the database character set and the «Oracle client» character set of the Oracle Client libraries used by cx_Oracle. If data cannot be correctly mapped between client and server character sets, then it may be corrupted or queries may fail with :ref:`»codec can’t decode byte» <codecerror>` .
cx_Oracle uses Oracle’s National Language Support (NLS) to assist in globalizing applications. As well as character set support, there are many other features that will be useful in applications. See the Database Globalization Support Guide.
Setting the Client Character Set
In cx_Oracle 8 the default encoding used for all character data changed to «UTF-8». This universal encoding is suitable for most applications. If you have a special need, you can pass the encoding and nencoding parameters to the :meth:`cx_Oracle.connect` and :meth:`cx_Oracle.SessionPool` methods to specify different Oracle Client character sets. For example:
In a future release of cx_Oracle, only UTF-8 will be supported.
The encoding parameter affects character data such as VARCHAR2 and CLOB columns. The nencoding parameter affects «National Character» data such as NVARCHAR2 and NCLOB. If you are not using national character types, then you can omit nencoding . Both the encoding and nencoding parameters are expected to be one of the Python standard encodings such as UTF-8 . Do not accidentally use UTF8 , which Oracle uses to specify the older Unicode 3.0 Universal character set, CESU-8 . Note that Oracle does not recognize all of the encodings that Python recognizes. You can see which encodings are usable in cx_Oracle by issuing this query:
From cx_Oracle 8, it is no longer possible to change the character set using the NLS_LANG environment variable. The character set component of that variable is ignored. The language and territory components of NLS_LANG are still respected by the Oracle Client libraries.
Character Set Example
The script below tries to display data containing a Euro symbol from the database.
Because the ‘€’ symbol is not supported by the US-ASCII character set, all ‘€’ characters are replaced by ‘¿’ in the cx_Oracle output:
When the encoding parameter is removed (or set to «UTF-8») during connection:
Then the output displays the Euro symbol as desired:
Finding the Database and Client Character Set
To find the database character set, execute the query:
To find the database ‘national character set’ used for NCHAR and related types, execute the query:
To find the current «client» character set used by cx_Oracle, execute the query:
If these character sets do not match, characters transferred over Oracle Net will be mapped from one character set to another. This may impact performance and may result in invalid data.
Setting the Oracle Client Locale
You can use the NLS_LANG environment variable to set the language and territory used by the Oracle Client libraries. For example, on Linux you could set:
The language («JAPANESE» in this example) specifies conventions such as the language used for Oracle Database messages, sorting, day names, and month names. The territory («JAPAN») specifies conventions such as the default date, monetary, and numeric formats. If the language is not specified, then the value defaults to AMERICAN. If the territory is not specified, then the value is derived from the language value. See Choosing a Locale with the NLS_LANG Environment Variable
If the NLS_LANG environment variable is set in the application with os.environ[‘NLS_LANG’] , it must be set before any connection pool is created, or before any standalone connections are created.
Other Oracle globalization variables, such as NLS_DATE_FORMAT can also be set to change the behavior of cx_Oracle, see Setting NLS Parameters.
Character Sets and Globalization¶
Data fetched from, and sent to, Oracle Database will be mapped between the database character set and the “Oracle client” character set of the Oracle Client libraries used by cx_Oracle. If data cannot be correctly mapped between client and server character sets, then it may be corrupted or queries may fail with “codec can’t decode byte” .
cx_Oracle uses Oracle’s National Language Support (NLS) to assist in globalizing applications. As well as character set support, there are many other features that will be useful in applications. See the Database Globalization Support Guide.
Setting the Client Character Set¶
In cx_Oracle 8 the default encoding used for all character data changed to “UTF-8”. This universal encoding is suitable for most applications. If you have a special need, you can pass the encoding and nencoding parameters to the cx_Oracle.connect() and cx_Oracle.SessionPool() methods to specify different Oracle Client character sets. For example:
In a future release of cx_Oracle, only UTF-8 will be supported.
The encoding parameter affects character data such as VARCHAR2 and CLOB columns. The nencoding parameter affects “National Character” data such as NVARCHAR2 and NCLOB. If you are not using national character types, then you can omit nencoding . Both the encoding and nencoding parameters are expected to be one of the Python standard encodings such as UTF-8 . Do not accidentally use UTF8 , which Oracle uses to specify the older Unicode 3.0 Universal character set, CESU-8 . Note that Oracle does not recognize all of the encodings that Python recognizes. You can see which encodings are usable in cx_Oracle by issuing this query:
From cx_Oracle 8, it is no longer possible to change the character set using the NLS_LANG environment variable. The character set component of that variable is ignored. The language and territory components of NLS_LANG are still respected by the Oracle Client libraries.
Character Set Example¶
The script below tries to display data containing a Euro symbol from the database.
Because the ‘€’ symbol is not supported by the US-ASCII character set, all ‘€’ characters are replaced by ‘¿’ in the cx_Oracle output:
When the encoding parameter is removed (or set to “UTF-8”) during connection:
Then the output displays the Euro symbol as desired:
Finding the Database and Client Character Set¶
To find the database character set, execute the query:
To find the database ‘national character set’ used for NCHAR and related types, execute the query:
To find the current “client” character set used by cx_Oracle, execute the query:
If these character sets do not match, characters transferred over Oracle Net will be mapped from one character set to another. This may impact performance and may result in invalid data.
Setting the Oracle Client Locale¶
You can use the NLS_LANG environment variable to set the language and territory used by the Oracle Client libraries. For example, on Linux you could set:
The language (“JAPANESE” in this example) specifies conventions such as the language used for Oracle Database messages, sorting, day names, and month names. The territory (“JAPAN”) specifies conventions such as the default date, monetary, and numeric formats. If the language is not specified, then the value defaults to AMERICAN. If the territory is not specified, then the value is derived from the language value. See Choosing a Locale with the NLS_LANG Environment Variable
If the NLS_LANG environment variable is set in the application with os.environ[‘NLS_LANG’] , it must be set before any connection pool is created, or before any standalone connections are created.
Other Oracle globalization variables, such as NLS_DATE_FORMAT can also be set to change the behavior of cx_Oracle, see Setting NLS Parameters.
© Copyright 2016, 2020, Oracle and/or its affiliates. All rights reserved. Portions Copyright © 2007-2015, Anthony Tuininga. All rights reserved. Portions Copyright © 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta, Canada. All rights reserved. Revision fde577bf . Last updated on May 25, 2022.