python连接oracle数据库

1. 下载并安装oracle-instantclient11.2.x

linux

[root]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]

# rpm -ivh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm 

# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm 

安装完成后，ORACLE Instant Client 相关的头文件在/usr/include/oracle/11.2/client64/下；库文件在/usr/lib/oracle/11.2/client64/下(lib 和 bin两个目录.)
 
指定TNS文件目录
一般情况将TNS文件（tnsnames.ora）保存到client64/network/admin下，该目录不存在，先创建目录
#mkdir -p /usr/lib/oracle/11.2/client64/network/admin
 将已有的TNS文件（tnsnames.ora）复制到该目录下

头文件的转移
相关的头文件(.h文件)在/usr/include/oracle/11.2/client64/目录下，在client64下创建rdbms/public，把头文件复制到该目录下
#mkdir -p /usr/lib/oracle/11.2/client64/rdbms/public
[root]# cd /usr/include/oracle/11.2/client64
[root]# cp * /usr/lib/oracle/11.2/client64/rdbms/public/
[root]# cd /usr/lib/oracle/11.2/client64/rdbms/public/
[root]# ls
ldap.h     nzt.h     occiCommon.h   occiData.h  occiObjects.h  ocidef.h  ocidfn.h  oci.h      ocixmldb.h  odci.h        orid.h      orl.h  ort.h……



设置环境变量
#vi ~/.bash_profile     ( /etc/profile 修改全局变量）
加入：
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG="SimplifiedChinese_china".AL32UTF8
#export NLS_LANG='simplifiedchinese_china'.ZHS16GBK
export LD_LIBRARY_PATH=/usr/local/lib:/usr/lib/oracle/11.2/client64/lib
PATH=$JAVA_HOME/bin:$PATH:$ORACLE_HOME/bin
export PATH

windows

下载地址http://www.oracle.com/technetwork/topics/winx64soft-089540.html

假设安装在C盘

在C:\instantclient_11_2设置

tnsnames.ora

testDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

2. 下载安装cx_Oracle模块

linux

tar -xzvf cx_Oracle-6.1.tar.gz 

cx_Oracle-6.1> python setup.py build

cx_Oracle-6.1> python setup.py install

#基本连接–使用Oracle tns alias
py> python
Python 3.6.0 (default, Nov 16 2017, 13:18:00) 
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> connection =cx_Oracle.connect('t1/t1@dba')
>>> connection
<cx_Oracle.Connection to t1@dba>
>>> print(connection.version)
11.2.0.4.0
>>> 

window

安装cx_Oracle-6.3.1-cp36-cp36m-win_amd64.whl

D:\>pip install wheel
Requirement already satisfied: wheel in c:\program files\python36\lib\site-packa
ges
You are using pip version 9.0.1, however version 10.0.1 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' comm
and.

D:\>pip install cx_Oracle-6.3.1-cp36-cp36m-win_amd64.whl
Processing d:\cx_oracle-6.3.1-cp36-cp36m-win_amd64.whl
Installing collected packages: cx-Oracle
  Found existing installation: cx-Oracle 6.0.3
    Uninstalling cx-Oracle-6.0.3:
      Successfully uninstalled cx-Oracle-6.0.3
Successfully installed cx-Oracle-6.3.1

导入

D:\>python
Python 3.6.1 (v3.6.1:69c0db5) [MSC v.1900 64 bit (AMD64)]
 on win32
Type "help", "copyright", "credits" or "license" for more information.

>>> import cx_Oracle
>>> connection =cx_Oracle.connect('test/test@testDB')
>>> connection
<cx_Oracle.Connection to test@testDB>
>>> print(connection.version)
11.2.0.4.0