Detailed introduction to the installation and use of python cx_Oracle module

Installation of python cx_Oracle module

Recently I need to write a data migration script to migrate the data in a single Oracle to the MySQL Sharding cluster. It feels a little troublesome to install cx_Oracle under Linux. Let’s sort it out and do it. a summary.

For the Oracle client, you not only need to install the corresponding python module (here I used Oracle's official python module - cx_Oracle), but also need to install the Oracle Client. Generally, it is enough to choose Instant Client, and you also need to configure it. tnsnames.ora (of course it can also be accessed simply through host:port/schema).

Installation:

1. First determine the version. Because our Oracle data is a bit old, I chose an older version-Oracle Instant Client 10.2.0.4.

2. Download instantclient-basic. Download address: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. . This is a serious BS against Oracle. You have to register before you can download it. Forget it. The key is that when registering, the password requires numbers and letters, and the letters must be in upper and lower case, and must be at least 8 characters. It forced me to get a password that is more secure than my bank password (well, now I have forgotten what I filled in...), and just download basic.

$wget http://download.oracle.com/otn/linux/instantclient/10204/basic-10.2.0.4.0-linux-x86_64.zip

3. Installation and configuration

$unzip instantclient-basic-linux.x64-10.2.0.4.0.zip
$cd instantclient_10_2
$cp * /usr/lib  #直接放到动态库搜索路径中，不需要额外的环境配置

或
$unzip instantclient-basic-linux.x64-10.2.0.4.0.zip
$cp -rf instantclient_10_2 /opt/
$vi /etc/profile
   export ORACLE_HOME=/opt/instantclient_10_2
　　 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

$source /etc/profile

4. Configure tnsnames.ora (can No need to configure tns)

Note that tnsnames.ora does not actually exist, you have to create it yourself (this is also disgusting, I thought at first I needed to install something...), I did not use this method , if you are interested, you can google it.

5. Download and install the cx_Oracle python module

$wget http://downloads.sourceforge.net/project/cx-oracle/5.1.2/cx_Oracle-5.1.2-10g-py26-1.x86_64.rpm
$rpm -ivh cx_Oracle-5.1.2-10g-py26-1.x86_64.rpm 
$ls /usr/lib/python2.6/site-packages/cx_Oracle.so #有这个文件表示安装成功，根据python的位置，也可能在其他地方，自己找一下吧

6. Verification and problem solving

$python
>>import cx_Oracle

If an error is reported: import cx_Oracle gave ImportError: libclntsh.so.10.1: cannot open shared object file: No such file or directory

means that the dynamic library of the instant client was not found, check the environment Whether the variables are configured, whether they are effective, and whether the version is correct.

If an error is reported: ImportError: ./cx_Oracle.so: undefined symbol: PyUnicodeUCS4_Decode

Google的信息：There is nothing wrong with Debian. Python supports two incompatible 
 modes of operation for Unicode, UCS2 (the default), and UCS4. Debian uses the default,
 Redhat uses UCS4. You need to recompile the extension for UCS-2 mode
 (i.e. using a Debian installation); this would fix the undefined symbol: PyUnicodeUCS4_Decode

So recompile python

$./configure --prefix=/usr/local/python2.6.5 --enable-shared -enable-unicode=ucs4
$make;make install

Verified again and finally imported normally.

Usage:

1. Basic connection – using Oracle tns alias

connection =cx_Oracle.connect("tp/tp@ocn_test")
#查看tns alias命令
cmd>tnsping ocn_test
TNS Ping Utility forLinux: Version 9.2.0.8.0-Production on 27-SEP-201110:47:48
Copyright (c) 1997, 2006, Oracle Corporation. Allrights reserved.
Used parameter files:
/opt/……/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL =TCP)(HOST =10.20.36.19)(PORT =1520))) (CONNECT_DATA =(SID =ocntest)))
OK (10msec)

2. User enters password Connection

pwd =getpass.getpass()
connection =cx_Oracle.connect("tp",pwd,"ocn_test")

3. The user directly enters the connection account information in the Python command, in the format of python script.py tp/tp@ocn_test

connection =cx_Oracle.connect(sys.argv[1])

4. Use Easy Connect syntax to connect to the database through Drive

connection =cx_Oracle.connect('tp','tp','10.20.36.19:1521/ocntest')
#or
connection =cx_Oracle.connect('tp/tp@10.20.36.19:1521/ocntest')

5. First use DSN to form TNSNAME

tns_name =cx_Oracle.makedsn('10.20.36.19','1521',' ocntest ')
connection =cx_Oracle.connect('tp','tp',tns_name)

6. Log in as SYSDBA

connection =cx_Oracle.connect('tp/tp@ocn_test', mode=cx_Oracle.SYSDBA)
#or as SYSOPER
connection =cx_Oracle.connect('tp/tp@ocn_test', mode=cx_Oracle.SYSOPER)

When performing Oracle operations on the Linux server An error occurred:

TNS:listener does not currently know of service requested in connect descriptor

Solution:

For problem analysis, see http://ora-12514.ora-code.com/, After a lot of fiddling, I finally used the fifth connection method, which solved the problem instantly.

Thank you for reading, I hope it can help you, thank you for your support of this site!

For more detailed introduction to the installation and use of the python cx_Oracle module, please pay attention to the PHP Chinese website!