[PYTHON] [Introduction to cx_Oracle] (5th) Handling of Japanese data

Verification environment

--Using Oracle Cloud

Introduction

In the past serialization, we proceeded in a way that did not handle Japanese data. However, most of the people who read this series are probably working in a Japanese environment and using the Oracle Database for Japanese data. This time, I will explain how to handle SELECT of the table where Japanese is stored without garbled characters.

Advance preparation

Please run the following script using SQL * Plus, SQL Developer, etc. You can substitute an existing table that contains Japanese data.

sample05a.sql


create table sample05a (col1 varchar2(50));
insert into sample05a values('Oracle Corporation Japan');
commit;

NLS_LANG The following applications are prepared as samples. If you have prepared another table in advance, change the SELECT statement so that the Japanese data column is specified in the first column and execute it. In that case, since this sample application displays only one line, if you can narrow down to one by specifying the primary key in the WHERE clause and take measures such as limiting the number of lines to the first line, extra processing time Do not take.

sample05b.py


import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = "select * from sample05a"

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                print((cursor.execute(SQL).fetchone())[0])

Generally, if you need to support Japanese when running an application that accesses Oracle Database, you need to set the environment variable NLS_LANG in most environments. NLS_LANG is also valid for cx_Oracle. The following is a comparison of the above applications with and without NLS_LANG.

$ echo $LANG
en_US.UTF-8
$ echo $NLS_LANG

$ python sample05b.py
??????????
$ export NLS_LANG=japanese_japan.al32utf8
$ python sample05b.py
Oracle Corporation Japan

Before setting NLS_LANG, the data is not displayed correctly as "??????????". In Oracle Database, "?" Is displayed when the character code conversion between DB and client is not possible. After setting NLS_LANG, it is displayed correctly.

encoding argument

In cx_Oracle, apart from NLS_LANG, the character encoding can be specified in the argument encoding at the time of connection (cx_Oracle's connect () method). The default encoding argument is None, which is not set. UTF-8 is the standard in Python3, so if you connect by specifying UTF-8 in encoding, you can display Japanese data without setting NLS_LANG. However, for example, if you want to receive Oracle Database error messages in Japanese, you need NLS_LANG. You also need to consider non-Python applications such as SQL * Plus, so it's a good idea to move it to NLS_LANG or set both NLS_LANG and encoding.

sample05c.py(Excerpt)


with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:

Recommended Posts

[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (16th) Handling of LOB types
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
Series: Introduction to cx_Oracle Contents
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to cx_Oracle] (10th) Update DML and DDL / DCL statements
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
Data handling 2 Analysis of various data formats
From the introduction of JUMAN ++ to morphological analysis of Japanese with Python
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to Data Scientists] Basics of Python ♬ Conditional branching and loops
[Introduction to Data Scientists] Basics of Python ♬ Functions and anonymous functions, etc.
[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.1-8.2.5)
[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.3-8.3.6.1)
[Introduction to Python3 Day 19] Chapter 8 Data Destinations (8.4-8.5)
[Introduction to Udemy Python3 + Application] 65. Exception handling
[Introduction to Data Scientists] Basics of Probability and Statistics ♬ Probability / Random Variables and Probability Distribution
Introduction to Scapy ① (From installation to execution of Scapy)
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
Introduction to Statistical Modeling for Data Analysis Expanding the range of applications of GLM
[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
About the handling of ZIP files including Japanese files when upgrading from Python2 to Python3
An introduction to data analysis using Python-To increase the number of video views-
[Introduction to Python] How to get the index of data with a for statement
[Introduction to SEIR model] Try fitting COVID-19 data ♬
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
Convert data with shape (number of data, 1) to (number of data,) with numpy.
[Introduction to Udemy Python 3 + Application] 19. Copy of list
[PyTorch] Introduction to Japanese document classification using BERT
[Introduction to Python] How to handle JSON format data
Introduction of ferenOS 2 (settings after installation, Japanese input settings)
I want to get League of Legends data ③
Djnago Memo Set Japanese to filename of Content-Disposition
I want to get League of Legends data ①
From the introduction of pyethapp to the execution of contract
[Data science memorandum] Handling of missing values ​​[python]
[Introduction to Python] Basic usage of lambda expressions
Introduction to Deep Learning for the first time (Chainer) Japanese character recognition Chapter 4 [Improvement of recognition accuracy by expanding data]
Introduction to MQTT (Introduction)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction of Python
Introduction of scikit-optimize
Introduction to Tkinter 1: Introduction
Introduction of PyGMT
Introduction to PyQt
Introduction to Scrapy (2)
Multi-condition data handling
[Linux] Introduction to Linux
Introduction to machine learning ~ Let's show the table of K-nearest neighbor method ~ (+ error handling)
Introduction to Scrapy (4)
Introduction to discord.py (2)
Introduction of cymel
Introduction to discord.py