Python-连接-Oracle-示例.doc

Python 连接 Oracle 示例RedHat 5.5 x86Python 基础语法参考： Python 基础语法知识 一. cx_Oracle Python 连接Oracle 数据库，需要使用cx_Oracle 包。 该包的下载地址：http:/cx-O/ 下载的时候，注意版本，对不同版本的Oracle 和平台，都有不同的cx_Oracle。 rootrac1 u01# rpm -ivh cx_Oracle-5.1-10g-py24-1.i386.rpm Preparing. # 100% 1:cx_Oracl # 100% Linux 平台下，用root 用户进行安装，并且还需要将一些Oracle 的环境变量添加到root用户的.bash_profile 里。 最简单的方法，就是直接把Oracle 用户的变量copy过来： PATH=$PATH:$HOME/binexport Oracle_BASE=/u01/app/oracleexport Oracle_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORA_CRS_HOME=$Oracle_BASE/product/crsexport Oracle_SID=dave1export PATH=.:$PATH:$HOME/bin:$Oracle_HOME/bin:$ORA_CRS_HOME/binexport PATH=$PATH:/usr/bin:/bin:/usr/bin/X11:/usr/local/binexport PATH=$PATH:$Oracle_BASE/common/oracle/binexport Oracle_TERM=xtermexport TNS_ADMIN=$Oracle_HOME/network/adminexport ORA_NLS10=$Oracle_HOME/nls/dataexport LD_LIBRARY_PATH=$Oracle_HOME/libexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$Oracle_HOME/oracm/libexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib:/usr/lib:/usr/local/libexport CLASSPATH=$Oracle_HOME/JREexport CLASSPATH=$CLASSPATH:$Oracle_HOME/jlibexport CLASSPATH=$CLASSPATH:$Oracle_HOME/rdbms/jlibexport CLASSPATH=$CLASSPATH:$Oracle_HOME/network/jlibexport THREADS_FLAG=nativeexport TEMP=/tmpexport TMPDIR=/tmp然后source 应用一下。二. Python 连Oracle 的基本操作2.1 DB连接和关闭DB连接2.1.1 方法一：用户名，密码和监听 分开写 rootrac1 u01# cat db.pyimport cx_Oracledb=cx_Oracle.connect(system,oracle,2:1521/dave)print db.versiondb.close() rootrac1 u01# python db.py.0 2.1.2 方法二：用户名，密码和监听写在一起rootrac1 u01# cat db.pyimport cx_Oracledb=cx_Oracle.connect(system/oracle2:1521/dave)print db.versiondb.close() rootrac1 u01# python db.py.0 2.1.3 方法三：配置监听并连接 rootrac1 u01# cat db.pyimport cx_Oracletns=cx_Oracle.makedsn(rac1,1521,dave1)db=cx_Oracle.connect(system,oracle,tns)print tnsprint db.versionvs=db.version.split(.)print vsif vs0=10: print This is Oracle 10g!db.close() rootrac1 u01# python db.py(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521)(CONNECT_DATA=(SID=dave1).010, 2, 0, 1, 0This is Oracle 10g! 2.2 建立cursor 并执行SQL语句 rootrac1 u01# cat db.pyimport cx_Oracletns=cx_Oracle.makedsn(rac1,1521,dave1)db=cx_Oracle.connect(system,oracle,tns) -创建连接cr=db.cursor() -创建cursorsql=select * from phonecr.execute(sql) -执行sql 语句 print nThis is Fetchall!rs=cr.fetchall() -一次返回所有结果集 print print all:(%s) %rsprint n print by row:for x in rs: print x print nThis is Fetone!cr.execute(sql)while(1): rs=cr.fetchone() -一次返回一行 if rs =None:break print rs -使用参数查询print n select with parameter:pr=id:3,telr.execute(select * from phone where id=:id or phone=:tel,pr)-这里我们将参数作为一个字典来处理的rs=cr.fetchall()print rs cr.execute(select * from phone where id=:myid or phone=:myphone,myid=2,myphone-这里我们直接写参数rs=cr.fetchall()print rs cr.close()db.close() rootrac1 u01# python db.py This is Fetchall!print all:(1,), (2,) print by row:(1,)(2,) This is Fetone!(1,)(2,) select with parameter:(2,)(2,) Python 类型和Oracle 类型的对应关系： During the fetch stage, basic Oracle data types get mapped into their Python equivalents. cx_Oracle maintains a separate set of data types that helps in this transition. The Oracle - cx_Oracle - Python mappings are: Oraclecx_OraclePythonVARCHAR2NVARCHAR2LONGcx_Oracle.STRINGstrCHARcx_Oracle.FIXED_CHARNUMBERcx_Oracle.NUMBERintFLOATfloatDATEcx_Oracle.DATETIMEdatetime.datetimeTIMESTAMPcx_Oracle.TIMESTAMPCLOBcx_Oracle.CLOBcx_Oracle.LOB三. 一个完成的示例 在这个例子里，我们将用Python 对DB 进行一些操作，包括，创建一张表，并插入一些数据，在修改其中的部分数据。 rootrac1 u01# cat dave.py#!/usr/bin/python#coding=utf-8import cx_Oracleimport sysimport urllibimport os def connectDB(dbname=dave): if dbname=dave: connstr=system/Oracle2:1521/dave db=cx_Oracle.connect(connstr) return db def sqlSelect(sql,db): #include:select cr=db.cursor() cr.execute(sql) rs=cr.fetchall() cr.close() return rs def sqlDML(sql,db): #include: insert,update,delete cr=db.cursor() cr.execute(sql) cr.close() mit() def sqlDML2(sql,params,db): # execute dml with parameters cr=db.cursor() cr.execute(sql,params) cr.close() mit() def sqlDDL(sql,db): #include: create cr=db.cursor() cr.execute(sql) cr.close() if _name_=_main_: print This is a test python program,write by tianlesoftware!n os.environNLS_LANG=SIMPLIFIED CHINESE_CHINA.UTF8 #connect to database: db=connectDB() #create a table: sql=create table dave(id number,name varchar2(20),phone number) sqlDDL(sql,db) #insert data to table dave: sql=insert into dave values(1,tianlesoftware sqlDML(sql,db) dt=id:2,name:dave,phone:138888888888, id:3,name:Oracle,phone id:4,name:anqing,phonesql=insert into dave values(:id,:name,:phone) for x in dt: sqlDML2(sql,x,db) #select the result: print this is the first time select the data from dave sql=select * from dave rs=sqlSelect(sql,db) for x in rs: print x #update data where id=1,change the name to anhui sql=update dave set name=anhui where id=1 sqlDML(sql,db) #select again: print n change the nanme to anhui where id equal 1,and select the result sql=select * from dave rs=sqlSelect(sql,db) for x in rs: print x #delete data where id=3 sql=delete from dave where id=3 sqlDML(sql,db) #select again: print n delete the data where id equal 3 and select the result sql=select * from dave rs=sqlSelect(sql,db) for x in rs: print x db.close() rootrac1 u01# python dave.pyThis is a test python program,write by tianlesoftware! this is the first time select the data from dave(1, tianlesoftware,)(2, dave, 138888888888L)(3, Oracle,)(4, anqing, 13888888