编程Python

Python-使用cx_Oracle操作Oracle数据库

作者：聂勇 欢迎转载，请保留作者信息并说明文章来源！

Python是一种动态语言，非常适合于处理数据逻辑，管理任务和程序的安装脚本。在Linux/Unix操作系统中，python已经默认安装。最近，花了一天多时间学习python，用于编写oracle数据库存储过程的自动化测试。下面是python操作Oracle的配置过程：

一、安装Oracle客户端| Install oracle client

Oracle提供了express版本的Oracle Client和Oracle Server，相对于企业版，express占用的资源更少，非常适合在开发环境使用。Oracle也提供了Ubuntu下的安装包，安装过程如下：

1、添加apt服务器

1) 编辑/etc/apt/source.list，在末尾添加如下配置：

1
deb http://oss.oracle.com/debian unstable main non-free

2) 获取服务器资源的公钥。如果在代理环境下，使用wget获取有问题，可以用浏览器直接打开如下http地址，并将内容保存为一个文件。

1
wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle

3) 添加公钥到apt系统的密钥库中。

1
sudo apt-key add RPM-GPG-KEY-oracle

4) 更新软件包索引信息。

1
sudo apt-get update

2、安装Oracle client

1) 执行如下命令，安装express 版本的Oracle client。

1
sudo apt-get install oracle-xe-client

2) 设置环境变量，编辑 ~/.profile，增加如下配置：

1
2
3
4
5
6
7
8
9
10
11
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
export ORACLE_HOME
TNS_ADMIN=/usr/lib/oracle
export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME
export PATH

3)使配置生效。执行如下命令：

1
source ~/.profile

二、安装cx_Oracle | Install cx_Oracle

1、在浏览器中打开如下http地址，下载cx_Oracle的源码：

1
http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.1.tar.gz?download

２、解压。

1
tar -zxvf cx_Oracle-5.1.tar.gz

３、进入解压后的目录，编译源代码。

1
python setup.py build

running build
running build_ext
building ‘cx_Oracle’ extension
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/demo -I/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public -I/usr/include/python2.6 -c cx_Oracle.c -o build/temp.linux-i686-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.1
In file included from /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/oci.h:2655,
from cx_Oracle.c:10:/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/oci1.h:148: warning: function declaration isn’t a prototype In file included from /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/ociap.h:222,from /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/oci.h:2679,from cx_Oracle.c:10:
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:676: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2667: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2676: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2686: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2695: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2704: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2713: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2721: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2731: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2738: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/nzt.h:2746: warning: function declaration isn’t a prototype
In file included from /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/oci.h:2679, from cx_Oracle.c:10:
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/ociap.h:10069: warning: function declaration isn’t a prototype
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/ociap.h:10075: warning: function declaration isn’t a prototype
creating build/lib.linux-i686-2.6-10g
gcc -pthread -shared -Wl,-O1 -Wl,-Bsymbolic-functions build/temp.linux-i686-2.6-10g/cx_Oracle.o -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/client -lclntsh -o build/lib.linux-i686-2.6-10g/cx_Oracle.so

1
python setup.py install

running install
running build
running build_ext
running install_lib
copying build/lib.linux-i686-2.6-10g/cx_Oracle.so -> /usr/local/lib/python2.6/dist-packages

三、编写第一个用cx_Oracle操作Oracle的代码段 | Hello cx_Oracle

１、编写cx_Oracle查询数据库的示例，其代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#! /usr/bin/python
#coding=UTF-8
import cx_Oracle
def hello():
    '''Hello cx_Oracle示例：
    
    1)打印数据库版本信息.
    2)查询表数据.'''
    
    conn = cx_Oracle.connect("codomain/codomain@10.3.3.92:1521/oracle1")
    cur = conn.cursor()
    try:
        print "Oracle Version:%s" % conn.version
        print "Table SUB_POLICY rows:"
        cur.execute('select * from SUB_POLICY')
        for row in cur:
            print row
    finally:
        cur.close()
        conn.close()
hello()

2、运行代码。

1
python ./DBOperate.py

结果如下：

Oracle Version:10.2.0.4.0
Table SUB_POLICY rows:
(4, 4, 1, 1, 2, 1, 1, None, None, None, None, None, None, 2, None, datetime.datetime(2011, 6, 1, 0, 0), 1, 0, None)
(1, 3, 1, 1, 1, 1, 1, None, None, None, None, None, None, 1, None, datetime.datetime(2011, 5, 31, 0, 0), 2, 0, None)
(2, 2, 2, 2, 2, 2, 2, None, None, None, None, None, None, 2, None, datetime.datetime(2011, 5, 31, 0, 0), 1, 0, None)
(3, 4, 3, 3, 3, 3, 3, None, None, None, None, None, None, 3, None, datetime.datetime(2011, 5, 25, 0, 0), 4, 0, None)

四、编译cx_Oracle常见问题的解决方法 | Solve problems

１、执行python setup.py build出现如下错误提示：

cx_Oracle.c:6: fatal error: Python.h: 没有那个文件或目录
compilation terminated.
error: command ‘gcc’ failed with exit status 1

解决方法：安装python dev library。

２、执行python setup.py install出现如下错误提示：

copying build/lib.linux-i686-2.6-10g/cx_Oracle.so -> /usr/local/lib/python2.6/dist-packages
error: /usr/local/lib/python2.6/dist-packages/cx_Oracle.so: Permission denied

解决方法：手动将 cx_Oracle.so 复制到 /usr/local/lib/python2.6/dist-packages 目录下。

参考 | References

分享到： QQ空间 微信 新浪微博 腾讯微博 人人网