Solve the problem of python connecting Oracle with cx_Oracle module

  • 2020-12-22 17:43:34
  • OfStack

Connecting to Oracle with python is always messy, most likely because the oracle client has the wrong character encoding.

I always reported that the keyword "From" did not exist when I was inserting data, and the inserted Sql printed was inserted in pl/sql, so there was no problem. Therefore, from the character set encoding to consider and solve the problem.

The python script you wrote needs to include:


import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

This can ensure that select out of the Chinese display no problem.

To be able to use insert and update Chinese properly, you also need to specify the character set password for the python source file and oracle1 send.


# -*- coding: utf-8 -*-

Example:


# -*- coding: utf-8 -*- 
import os 
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' # or os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8' 
import cx_Oracle 
db = cx_Oracle.connect(username/passwd@host:port/sevicename) 
cursor = db.cursor() 
# Other operating  
 
db.commit() 
db.close() 

Client NLS_LANG setup and code conversion

When the Oracle client submits an SQL statement to the server, the Oracle client converts the string encoding received from the application according to the NLS_LANG and the database character set. If NLS_LANG is the same as the database character set, it is not converted, otherwise it is converted to the database character set and sent to the server. After receiving the string encoding, the server stores it directly for the normal CHAR or VARCHAR2 type. For NCHAR or NVARCHAR2 types, the server converts them to the national Character set for storage.


Related articles: