一点一滴努力,满仓满谷收成

1 概述
python操作Oracle库建议使用cx_Oracle组件,cx_Oracle 的主要开发人员 Anthony Tuininga 添加了一组丰富的属性和方法，以向开发人员揭示 Oracle 独有的特性。本文把安装Oracle客户端这一项略了，测试的环境是CentOS linux 6.6。

2 cx_Oracle源码安装
1)下载cx_Oracle源码
下载网址：https://pypi.python.org/pypi/cx_Oracle/5.2
选择源码文件：cx_Oracle-5.2.tar.gz,下载后上传到服务器并解压缩。
2)设置Oracle环境变量

[root@python qiuyb]# cd cx_Oracle-5.2
[root@python cx_Oracle-5.2]# ls -l setup*
-rw-rw-r-- 1 1000 1000    59 Jun 20 17:52 setup.cfg
-rw-rw-r-- 1 1000 1000 14635 Apr  3  2015 setup.py
[root@python cx_Oracle-5.2]# 
[root@python cx_Oracle-5.2]# export ORACLE_BASE=/home/oracle/app/oracle
[root@python cx_Oracle-5.2]# export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
[root@python cx_Oracle-5.2]# export PATH=$ORACLE_HOME/bin:$PATH:.
[root@python cx_Oracle-5.2]# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
[root@python cx_Oracle-5.2]# export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
[root@python cx_Oracle-5.2]# 

3)build

[root@python cx_Oracle-5.2]# python -V
Python 3.4.3
[root@python cx_Oracle-5.2]# python setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.linux-i686-3.4-10g
gcc -pthread -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -fPIC -I/home/oracle/app/oracle/product/10.2.0/rdbms/demo -I/home/oracle/app/oracle/product/10.2.0/rdbms/public -I/usr/local/python-3.4.3/include/python3.4m -c cx_Oracle.c -o build/temp.linux-i686-3.4-10g/cx_Oracle.o -DBUILD_VERSION=5.2
In file included from /home/oracle/app/oracle/product/10.2.0/rdbms/public/oci.h:2651,
                 from cx_Oracle.c:10:
/home/oracle/app/oracle/product/10.2.0/rdbms/public/oci1.h:148: warning: function declaration isn’t a prototype
In file included from /home/oracle/app/oracle/product/10.2.0/rdbms/public/ociap.h:225,
                 from /home/oracle/app/oracle/product/10.2.0/rdbms/public/oci.h:2675,
                 from cx_Oracle.c:10:
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:676: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2667: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2676: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2686: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2695: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2704: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2713: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2721: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2731: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2738: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/nzt.h:2746: warning: function declaration isn’t a prototype
In file included from /home/oracle/app/oracle/product/10.2.0/rdbms/public/oci.h:2675,
                 from cx_Oracle.c:10:
/home/oracle/app/oracle/product/10.2.0/rdbms/public/ociap.h:10068: warning: function declaration isn’t a prototype
/home/oracle/app/oracle/product/10.2.0/rdbms/public/ociap.h:10074: warning: function declaration isn’t a prototype
In file included from Connection.c:765,
                 from SessionPool.c:139,
                 from cx_Oracle.c:203:
Cursor.c: In function ‘Cursor_FreeHandle’:
Cursor.c:232: warning: ‘buffer.size’ may be used uninitialized in this function
Cursor.c:232: warning: ‘buffer.ptr’ may be used uninitialized in this function
Cursor.c:235: warning: ‘buffer.obj’ may be used uninitialized in this function
creating build/lib.linux-i686-3.4-10g
gcc -pthread -shared build/temp.linux-i686-3.4-10g/cx_Oracle.o -L/home/oracle/app/oracle/product/10.2.0/lib -lclntsh -o build/lib.linux-i686-3.4-10g/cx_Oracle.cpython-34m.so

4) install

[root@python cx_Oracle-5.2]# python setup.py install
running install
running bdist_egg
running egg_info
writing top-level names to cx_Oracle.egg-info/top_level.txt
writing dependency_links to cx_Oracle.egg-info/dependency_links.txt
writing cx_Oracle.egg-info/PKG-INFO
reading manifest file 'cx_Oracle.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'cx_Oracle.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-i686/egg
running install_lib
running build_ext
creating build/bdist.linux-i686
creating build/bdist.linux-i686/egg
copying build/lib.linux-i686-3.4-10g/cx_Oracle.cpython-34m.so -> build/bdist.linux-i686/egg
creating stub loader for cx_Oracle.cpython-34m.so
byte-compiling build/bdist.linux-i686/egg/cx_Oracle.py to cx_Oracle.cpython-34.pyc
creating build/bdist.linux-i686/egg/EGG-INFO
copying cx_Oracle.egg-info/PKG-INFO -> build/bdist.linux-i686/egg/EGG-INFO
copying cx_Oracle.egg-info/SOURCES.txt -> build/bdist.linux-i686/egg/EGG-INFO
copying cx_Oracle.egg-info/dependency_links.txt -> build/bdist.linux-i686/egg/EGG-INFO
copying cx_Oracle.egg-info/top_level.txt -> build/bdist.linux-i686/egg/EGG-INFO
writing build/bdist.linux-i686/egg/EGG-INFO/native_libs.txt
zip_safe flag not set; analyzing archive contents...
__pycache__.cx_Oracle.cpython-34: module references __file__
creating dist
creating 'dist/cx_Oracle-5.2-py3.4-linux-i686.egg' and adding 'build/bdist.linux-i686/egg' to it
removing 'build/bdist.linux-i686/egg' (and everything under it)
Processing cx_Oracle-5.2-py3.4-linux-i686.egg
creating /usr/local/python-3.4.3/lib/python3.4/site-packages/cx_Oracle-5.2-py3.4-linux-i686.egg
Extracting cx_Oracle-5.2-py3.4-linux-i686.egg to /usr/local/python-3.4.3/lib/python3.4/site-packages
Adding cx-Oracle 5.2 to easy-install.pth file

Installed /usr/local/python-3.4.3/lib/python3.4/site-packages/cx_Oracle-5.2-py3.4-linux-i686.egg
Processing dependencies for cx-Oracle==5.2
Finished processing dependencies for cx-Oracle==5.2

无报错，安装成功！

3、测试
1)测试cx_Oracle模块工作是否正常

[root@python cx_Oracle-5.2]# python
Python 3.4.3 (default, Sep 17 2015, 05:54:48) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-11)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> con = cx_Oracle.connect("qiuyb/qiuyb@xlt1")
>>> print(con.version)
10.2.0.5.0
>>> print(con.dsn)
xlt1
>>> versioning = con.version.split('.')
>>> print(versioning)
['10', '2', '0', '5', '0']
>>> if versioning[0] == '10':
...     print('you database version is 10g')
... 
you database version is 10g

2)查询一张表并显示结果

>>> import cx_Oracle
>>> con = cx_Oracle.connect("qiuyb/qiuyb@xlt1")
>>> cursor = con.cursor()
>>> from pprint import pprint
>>> cursor.execute('select * from t1')
<cx_Oracle.Cursor on <cx_Oracle.Connection to qiuyb@xlt1>>
>>> pprint(cursor.fetchall())
[(13, 'luoyu', 1),
 (1, 'yangbaoqiu', 1),
 (2, 'liujianyu', 1),
 (3, 'xinzhida', 1),
 (4, 'hhhhhh', 1),
 (9, 'xudan', 1),
 (10, 'libin', 1),
 (11, 'menglingjiang', 1),
 (12, 'caodefeng', 1),
 (5, 'chenliangchun', 1),
 (6, 'sunzhongbin', 1),
 (7, 'yanghua', 1),
 (8, 'zhuangbin', 1)]

>>> cursor.execute('select * from t1')
<cx_Oracle.Cursor on <cx_Oracle.Connection to qiuyb@xlt1>>
>>> for row in cursor:
...     print(row)
... 
(13, 'luoyu', 1)
(1, 'yangbaoqiu', 1)
(2, 'liujianyu', 1)
(3, 'xinzhida', 1)
(4, 'hhhhhh', 1)
(9, 'xudan', 1)
(10, 'libin', 1)
(11, 'menglingjiang', 1)
(12, 'caodefeng', 1)
(5, 'chenliangchun', 1)
(6, 'sunzhongbin', 1)
(7, 'yanghua', 1)
(8, 'zhuangbin', 1)

3)绑定变量

>>> import cx_Oracle
>>> con = cx_Oracle.connect("qiuyb/qiuyb@xlt1")
>>> cursor = con.cursor()
>>> from pprint import pprint
>>> cursor.prepare('select * from t1 where id=:id')
>>> cursor.execute(None,{'id':11})
<cx_Oracle.Cursor on <cx_Oracle.Connection to qiuyb@xlt1>>
>>> r = cursor.execute(None,{'id':11})
>>> pprint(r)
<cx_Oracle.Cursor on <cx_Oracle.Connection to qiuyb@xlt1>>
>>> pprint(r.fetchall())
[(11, 'menglingjiang', 1)]
>>> r = cursor.execute(None,{'id':1})
>>> pprint(r)
<cx_Oracle.Cursor on <cx_Oracle.Connection to qiuyb@xlt1>>
>>> for row in r:
...     print(row)
... 
(1, 'yangbaoqiu', 1)

4)建表，共享变量插入

>>> create_table = '''
... create table python_modules(
... module_name varchar2(50) not null,
... file_path varchar2(300) not null
... )
... '''
>>> from sys import modules
>>> cursor.execute(create_table)
>>> M = []
>>> for m_name,m_info in modules.items():
...     try:
...         M.append((m_name,m_info.__file__))
...     except AttributeError:
...         pass
... 
>>> len(M)
51
>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")
>>> cursor.executemany(None, M)
>>> con.commit()
>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")
>>> pprint(r.fetchall())
[(51,)]
>>> 

5)库里数据卸载成文本

import csv
import cx_Oracle
from pprint import pprint

con = cx_Oracle.connect('qiuyb/qiuyb@xlt1')
cursor = con.cursor()
cursor.execute('select * from t1 order by id')

f = open('t_db_csv.csv','w',newline='')
writer = csv.writer(f,delimiter='|',quoting=csv.QUOTE_ALL)

for row in cursor:
    writer.writerow(row)

f.close()