Python 连接 Oracle 示例

Python连接Oracle示例RedHat5.5x86Python基础语法参考：Python基础语法知识一.cx_OraclePython连接Oracle数据库，需要使用cx_Oracle包。该包的下载地址：下载的时候，注意版本，对不同版本的Oracle和平台，都有不同的cx_Oracle。[root@rac1u01]#rpm-ivhcx_Oracle-5.1-10g-py24-1.i386.rpmPreparing...###########################################[100%]1:cx_Oracl###########################################[100%]Linux平台下，用root用户进行安装，并且还需要将一些Oracle的环境变量添加到root用户的.bash_profile里。最简单的方法，就是直接把Oracle用户的变量copy过来：PATH=$PATH:$HOME/binexportOracle_BASE=/u01/app/oracleexportOracle_HOME=$ORACLE_BASE/product/10.2.0/db_1exportORA_CRS_HOME=$Oracle_BASE/product/crsexportOracle_SID=dave1exportPATH=.:${PATH}:$HOME/bin:$Oracle_HOME/bin:$ORA_CRS_HOME/binexportPATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/binexportPATH=${PATH}:$Oracle_BASE/common/oracle/binexportOracle_TERM=xtermexportTNS_ADMIN=$Oracle_HOME/network/adminexportORA_NLS10=$Oracle_HOME/nls/dataexportLD_LIBRARY_PATH=$Oracle_HOME/libexportLD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$Oracle_HOME/oracm/libexportLD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/libexportCLASSPATH=$Oracle_HOME/JREexportCLASSPATH=${CLASSPATH}:$Oracle_HOME/jlibexportCLASSPATH=${CLASSPATH}:$Oracle_HOME/rdbms/jlibexportCLASSPATH=${CLASSPATH}:$Oracle_HOME/network/jlibexportTHREADS_FLAG=nativeexportTEMP=/tmpexportTMPDIR=/tmp然后source应用一下。二.Python连Oracle的基本操作2.1DB连接和关闭DB连接2.1.1方法一：用户名，密码和监听分开写[root@rac1u01]#catdb.pyimportcx_Oracledb=cx_Oracle.connect('system','oracle','192.168.2.42:1521/dave')printdb.versiondb.close()[root@rac1u01]#pythondb.py10.2.0.1.02.1.2方法二：用户名，密码和监听写在一起[root@rac1u01]#catdb.pyimportcx_Oracledb=cx_Oracle.connect('system/oracle@192.168.2.42:1521/dave')printdb.versiondb.close()[root@rac1u01]#pythondb.py10.2.0.1.02.1.3方法三：配置监听并连接[root@rac1u01]#catdb.pyimportcx_Oracletns=cx_Oracle.makedsn('rac1',1521,'dave1')db=cx_Oracle.connect('system','oracle',tns)printtnsprintdb.versionvs=db.version.split('.')printvsifvs[0]=='10':printThisisOracle10g!db.close()[root@rac1u01]#pythondb.py(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521)))(CONNECT_DATA=(SID=dave1)))10.2.0.1.0['10','2','0','1','0']ThisisOracle10g!2.2建立cursor并执行SQL语句[root@rac1u01]#catdb.pyimportcx_Oracletns=cx_Oracle.makedsn('rac1',1521,'dave1')db=cx_Oracle.connect('system','oracle',tns)--创建连接cr=db.cursor()--创建cursorsql='select*fromphone'cr.execute(sql)--执行sql语句print\nThisisFetchall!rs=cr.fetchall()--一次返回所有结果集printprintall:(%s)%rsprint\nprintbyrow:forxinrs:printxprint\nThisisFetone!cr.execute(sql)while(1):rs=cr.fetchone()--一次返回一行ifrs==None:breakprintrs--使用参数查询print\nselectwithparameter:pr={'id':3,'tel':13888888888}cr.execute('select*fromphonewhereid=:idorphone=:tel',pr)--这里我们将参数作为一个字典来处理的rs=cr.fetchall()printrscr.execute('select*fromphonewhereid=:myidorphone=:myphone',myid=2,myphone=13888888888)--这里我们直接写参数rs=cr.fetchall()printrscr.close()db.close()[root@rac1u01]#pythondb.pyThisisFetchall!printall:([(1,13865999999L),(2,13888888888L)])printbyrow:(1,13865999999L)(2,13888888888L)ThisisFetone!(1,13865999999L)(2,13888888888L)selectwithparameter:[(2,13888888888L)][(2,13888888888L)]Python类型和Oracle类型的对应关系：Duringthefetchstage,basicOracledatatypesgetmappedintotheirPythonequivalents.cx_Oraclemaintainsaseparatesetofdatatypesthathelpsinthistransition.TheOracle-cx_Oracle-Pythonmappingsare:Oraclecx_OraclePythonVARCHAR2NVARCHAR2LONGcx_Oracle.STRINGstrCHARcx_Oracle.FIXED_CHARNUMBERcx_Oracle.NUMBERintFLOATfloatDATEcx_Oracle.DATETIMEdatetime.datetimeTIMESTAMPcx_Oracle.TIMESTAMPCLOBcx_Oracle.CLOBcx_Oracle.LOB三.一个完成的示例在这个例子里，我们将用Python对DB进行一些操作，包括，创建一张表，并插入一些数据，在修改其中的部分数据。[root@rac1u01]#catdave.py#!/usr/bin/python#coding=utf-8importcx_OracleimportsysimporturllibimportosdefconnectDB(dbname='dave'):ifdbname=='dave':connstr='system/Oracle@192.168.2.42:1521/dave'db=cx_Oracle.connect(connstr)returndbdefsqlSelect(sql,db):#include:selectcr=db.cursor()cr.execute(sql)rs=cr.fetchall()cr.close()returnrsdefsqlDML(sql,db):#include:insert,update,deletecr=db.cursor()cr.execute(sql)cr.close()db.commit()defsqlDML2(sql,params,db):#executedmlwithparameterscr=db.cursor()cr.execute(sql,params)cr.close()db.commit()defsqlDDL(sql,db):#include:createcr=db.cursor()cr.execute(sql)cr.close()if__name__=='__main__':printThisisatestpythonprogram,writebytianlesoftware!\nos.environ['NLS_LANG']='SIMPLIFIEDCHINESE_CHINA.UTF8'#connecttodatabase:db=connectDB()#createatable:sql='createtabledave(idnumber,namevarchar2(20),phonenumber)'sqlDDL(sql,db)#insertdatatotabledave:sql='insertintodavevalues(1,\'tianlesoftware\',13888888888)'sqlDML(sql,db)dt=[{'id':2,'name':'dave','phone':138888888888},{'id':3,'name':'Oracle','phone':13888888888},{'id':4,'name':'anqing','phone':13888888888}]sql='insertintodavevalues(:id,:name,:phone)'forxindt:sqlDML2(sql,x,db)#selecttheresult:printthisisthefirsttimeselectthedatafromdavesql='select*fromdave'rs=sqlSelect(sql,db)forxinrs:printx#updatedatawhereid=1,changethenametoanhuisql='updatedavesetname=\'anhui\'whereid=1'sqlDML(sql,db)#selectagain:print\nchangethenanmetoanhuiwhereidequal1,andselectth