Python 使用 cx_Oracle 教程

cx_Oracle的使用需要调用Oracle环境，因此需要安装Oracle客户端（推荐oracle instantclient）
官网地址：http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

一、 Windows环境

1.下载Windows的即时客户端

官网下载：http://www.oracle.com/technetwork/topics/winx64soft-089540.html

下载安装包：
instantclient-basic-windows.x64-11.2.0.4.0
instantclient-sdk-windows.x64-11.2.0.4.0
instantclient-sqlplus-windows.x64-11.2.0.4.0

2.安装instantclient

a.解压在同一个目录下
b.配置环境变量 E:\software\instantclient_11_2
c.测试sqlplus

E:\software\instantclient_11_2>sqlplus.exe username/[email protected]//host:port/sid
SQL> select * from database."tablename";

3.安装cx_Oracle

pip install cx_Oracle==5.3

报错：
error: Microsoft Visual C++ 9.0 is required. Get it from http://aka.ms/vcpython27
打开 http://aka.ms/vcpython27    下载

报错：
InterfaceError: Unable to acquire Oracle environmen
将oracle目录下的几个dll文件copy到python site-package目录下

4.测试cx_Oracle

>>> import cx_Oracle
>>> conn = cx_Oracle.connect(username, password, host:port/sid)
>>> result = conn.cursor().execute("SELECT * FROM TABLE")
>>> for item in result:
...     print item

>>> conn.close()
>>> exit()

二、Linux环境

1.下载Linux的即时客户端

官网下载：http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

下载安装包：
（1）zip安装
instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sdk-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

2.安装instantclient

解压三个文件到同一个目录下

unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /software/
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /software/
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /software/

3.配置监听文件

/software/instantclient_11_2/network/admin要自己创建，包括tnsnames.ora文件
$ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

4.配置环境变量

# vim /etc/profile #文件尾部添加
export ORACLE_HOME=/software/oracle/instantclient_12_2
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
export  TNS_ADMIN=$ORACLE_HOME/network/admin  # 安装了sqlplus 要设置监听
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK    # 设置编码集

PATH = $ORACLE_HOME:...
# source /etc/profile

5.设置软连接

cd /software/instantclient_11_2/
ln -s libclntsh.so.11.1 libclntsh.so

6.安装cx_Oracle

pip install cx_Oracle==5.3

7.测试cx_Oracle

(venv) [root@localhost /]# python
>>> import cx_Oracle
>>> print cx_Oracle.version
5.3
>>> exit()