随笔记：如何使用Python连接（/操作）Oracle数据库（Windows平台下）

dyok

　　遇到需求，我们需要用Python对Oracle数据库进行操作。
　　这次我们使用cx_Oracle
　　
　　Oracle Client
　　在安装cx_Oracle之前，先安装Oracle客户端。
　　
　　cx_Oracle
　　cx_Oracle是一个遵循Python数据库API接口的扩展模块，可通过她对Oracle进行操作。
　　目前，可从此地址下载：http://cx-oracle.sourceforge.net/
　　我下载的是针对Python2.7、Oracle11g、Win32的版本：cx_Oracle-5.1.3-11g.win32-py2.7.exe (md5)
　　其安装过程如一般软件。
　　
　　在代码中即可引入cx_Oracle对数据库进行操作。如以下代码：
　　查询数据





#coding=utf-8
#!/usr/bin/python
import cx_Oracle;
conn = None;
cursor = None;
try:
conn = cx_Oracle.connect('userid/password@xx.xx.xx.xx/sid');
cursor = conn.cursor();
cursor.execute('select t.empno, t.ename from scott.emp t');
# 取回的是列表，列表中包含元组
list = cursor.fetchall();
print list;
for record in list:
print "Record %d is %s!" % (record[0], record[1]);
except Exception as e:
print ('Error : {}'.format(e));
finally:
cursor.close;
print 'cursor closed';
conn.close;
print 'connection closed';

View Code　　
　　插入数据





#coding=utf-8
#!/usr/bin/python
import cx_Oracle;
import time;
conn = None;
cursor = None;
try:
conn = cx_Oracle.connect('userid/password@xx.xx.xx.xx/sid');
cursor = conn.cursor();
tuple = ('1001', 'Nick Huang');
cursor.execute("insert into scott.emp (empno, ename) values (:1, :2)", tuple);
conn.commit();
print 'Insert successfully.';
except Exception as e:
print ('Error : {}'.format(e));
finally:
cursor.close;
print 'cursor closed';
conn.close;
print 'connection closed';

View Code　　
　　更新数据





#coding=utf-8
#!/usr/bin/python
import cx_Oracle;
import time;
conn = None;
cursor = None;
try:
conn = cx_Oracle.connect('userid/password@xx.xx.xx.xx/sid');
cursor = conn.cursor();
tuple = ('Robin Chen', '1001');
cursor.execute("update scott.emp t set t.ename = :1 where t.empno = :2", tuple);
conn.commit();
print 'Update successfully.';
except Exception as e:
print ('Error : {}'.format(e));
finally:
cursor.close;
print 'cursor closed';
conn.close;
print 'connection closed';

View Code　　
　　删除数据





#coding=utf-8
#!/usr/bin/python
import cx_Oracle;
import time;
conn = None;
cursor = None;
try:
conn = cx_Oracle.connect('userid/password@xx.xx.xx.xx/sid');
cursor = conn.cursor();
param_map = {'id' : '1001'};
cursor.execute("delete from scott.emp t where t.empno = :id", param_map);
conn.commit();
print 'Delete successfully.';
except Exception as e:
print ('Error : {}'.format(e));
finally:
cursor.close;
print 'cursor closed';
conn.close;
print 'connection closed';

View Code　　
　　崎岖
　　1、运行时，报如下异常，发现是Oracle客户端与cx_Oracle版本不一致导致的。
　　我的Oracle客户端安装的是10g，cx_Oracle是11g的，我重新安装了10g的cx_Oracle就可以了。





D:\python27_workspace>"019.edit oracle.py"
Error : ORA-24315: illegal attribute type
View Code　　
　　2、报如下异常，一般为cx_Oracle不支持此种参数绑定方式





Error : ORA-01036: illegal variable name/number
View Code　　如下面方式绑定一个参数，不知为何帮上述异常，后来换成元组的形式绑定参数，就OK了





#coding=utf-8
#!/usr/bin/python
import cx_Oracle;
import time;
conn = None;
cursor = None;
try:
conn = cx_Oracle.connect('apps/apps@192.168.0.206/PROD');
cursor = conn.cursor();
tuple = ('1001');
cursor.execute("delete from scott.emp t where t.empno = :1", tuple);
conn.commit();
print 'Delete successfully.';
except Exception as e:
print ('Error : {}'.format(e));
finally:
cursor.close;
print 'cursor closed';
conn.close;
print 'connection closed';

View Code　　
　　参考：
　　http://blog.csdn.net/kongxx/article/details/7107661
　　http://evil850209.iyunv.com/blog/1394932