随笔记:如何使用Python连接(/操作)Oracle数据库(Windows平台下)
遇到需求,我们需要用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对数据库进行操作。如以下代码:
查询数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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';
插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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';
更新数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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';
删除数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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';
崎岖
1、运行时,报如下异常,发现是Oracle客户端与cx_Oracle版本不一致导致的。
我的Oracle客户端安装的是10g,cx_Oracle是11g的,我重新安装了10g的cx_Oracle就可以了。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
D:\python27_workspace>"019.edit oracle.py" Error : ORA-24315: illegal attribute type
2、报如下异常,一般为cx_Oracle不支持此种参数绑定方式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Error : ORA-01036: illegal variable name/number
如下面方式绑定一个参数,不知为何帮上述异常,后来换成元组的形式绑定参数,就OK了
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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';
参考:
http://blog.csdn.net/kongxx/article/details/7107661
http://evil850209.iteye.com/blog/1394932
作者:Nick Huang 博客:http://www.cnblogs.com/nick-huang/
本博客为学习、笔记之用,以笔记形式记录学习的知识与感悟。学习过程中可能参考各种资料,如觉文中表述过分引用,请务必告知,以便迅速处理。如有错漏,不吝赐教。
如果本文对您有用,点赞或评论哦;如果您喜欢我的文章,请点击关注我哦~
本博客为学习、笔记之用,以笔记形式记录学习的知识与感悟。学习过程中可能参考各种资料,如觉文中表述过分引用,请务必告知,以便迅速处理。如有错漏,不吝赐教。
如果本文对您有用,点赞或评论哦;如果您喜欢我的文章,请点击关注我哦~