Python 连接 Oracle 示例.docx

《Python 连接 Oracle 示例.docx》由会员分享，可在线阅读，更多相关《Python 连接 Oracle 示例.docx（13页珍藏版）》请在三一办公上搜索。

1、Python 连接 Oracle 示例Python 连接 Oracle 示例 RedHat 5.5 x86 Python 基础语法参考： 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 平台下，用

2、root 用户进行安装，并且还需要将一些Oracle 的环境变量添加到root用户的.bash_profile 里。 最简单的方法，就是直接把Oracle 用户的变量copy过来： PATH=$PATH:$HOME/bin export Oracle_BASE=/u01/app/oracle export Oracle_HOME=$ORACLE_BASE/product/10.2.0/db_1 export ORA_CRS_HOME=$Oracle_BASE/product/crs export Oracle_SID=dave1 export PATH=.:$PATH:$HOME/bin:$O

3、racle_HOME/bin:$ORA_CRS_HOME/bin export PATH=$PATH:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=$PATH:$Oracle_BASE/common/oracle/bin export Oracle_TERM=xterm export TNS_ADMIN=$Oracle_HOME/network/admin export ORA_NLS10=$Oracle_HOME/nls/data export LD_LIBRARY_PATH=$Oracle_HOME/lib export LD_

4、LIBRARY_PATH=$LD_LIBRARY_PATH:$Oracle_HOME/oracm/lib export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$Oracle_HOME/JRE export CLASSPATH=$CLASSPATH:$Oracle_HOME/jlib export CLASSPATH=$CLASSPATH:$Oracle_HOME/rdbms/jlib export CLASSPATH=$CLASSPATH:$Oracle_HOME/netwo

5、rk/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp 然后source 应用一下。 二. Python 连Oracle 的基本操作 2.1 DB连接和关闭DB连接 2.1.1 方法一：用户名，密码和监听 分开写 rootrac1 u01# cat db.py import cx_Oracle db=cx_Oracle.connect(system,oracle,192.168.2.42:1521/dave) print db.version db.close rootrac1 u01# python db.

6、py 10.2.0.1.0 2.1.2 方法二：用户名，密码和监听写在一起 rootrac1 u01# cat db.py import cx_Oracle db=cx_Oracle.connect(system/oracle192.168.2.42:1521/dave) print db.version db.close rootrac1 u01# python db.py 10.2.0.1.0 2.1.3 方法三：配置监听并连接 rootrac1 u01# cat db.py import cx_Oracle tns=cx_Oracle.makedsn(rac1,1521,dave1) d

7、b=cx_Oracle.connect(system,oracle,tns) print tns print db.version vs=db.version.split(.) print vs if 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) 10.2.0.1.0 10, 2, 0, 1, 0 This

8、is Oracle 10g! 2.2 建立cursor 并执行SQL语句 rootrac1 u01# cat db.py import cx_Oracle tns=cx_Oracle.makedsn(rac1,1521,dave1) db=cx_Oracle.connect(system,oracle,tns) -创建连接 cr=db.cursor -创建cursor sql=select * from phone cr.execute(sql) -执行sql 语句 print nThis is Fetchall! rs=cr.fetchall -一次返回所有结果集 print print a

9、ll:(%s) %rs print 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,tel:13888888888 cr.execute(select * from phone where id=:id or phone=:tel,pr) -这里我们将参数作为一个字典来处理的 rs

10、=cr.fetchall print rs cr.execute(select * from phone phone=:myphone,myid=2,myphone=13888888888) -这里我们直接写参数 rs=cr.fetchall print rs cr.close db.close rootrac1 u01# python db.py This is Fetchall! print all:(1, 13865999999L), (2, 13888888888L) where id=:myid or print by row: (1, 13865999999L) (2, 13888

11、888888L) This is Fetone! (1, 13865999999L) (2, 13888888888L) select with parameter: (2, 13888888888L) (2, 13888888888L) 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 t

12、his transition. The Oracle - cx_Oracle - Python mappings are: Oracle cx_Oracle Python VARCHAR2 NVARCHAR2 LONG cx_Oracle.STRING str CHAR cx_Oracle.FIXED_CHAR NUMBER cx_Oracle.NUMBER int FLOAT float DATE cx_Oracle.DATETIME datetime.datetime TIMESTAMP cx_Oracle.TIMESTAMP CLOB cx_Oracle.CLOB cx_Oracle.L

13、OB 三. 一个完成的示例 在这个例子里，我们将用Python 对DB 进行一些操作，包括，创建一张表，并插入一些数据，在修改其中的部分数据。 rootrac1 u01# cat dave.py #!/usr/bin/python #coding=utf-8 import cx_Oracle import sys import urllib import os def connectDB(dbname=dave): if dbname=dave: connstr=system/Oracle192.168.2.42:1521/dave db=cx_Oracle.connect(connstr)

14、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) c

15、r.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,

16、name varchar2(20),phone number) sqlDDL(sql,db) #insert data to table dave: sql=insert into dave values(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=insert into dave values(:id,:name,:phon

17、e) 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

18、 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

19、dave rs=sqlSelect(sql,db) for x in rs: print x db.close rootrac1 u01# python dave.py This is a test python program,write by tianlesoftware! this is the first time select the data from dave (1, tianlesoftware, 13888888888L) (2, dave, 138888888888L) (3, Oracle, 13888888888L) (4, anqing, 13888888888L) change the nanme to anhui where id equal 1,and select the result (1, anhui, 13888888888L) (2, dave, 138888888888L) (3, Oracle, 13888888888L) (4, anqing, 13888888888L) delete the data where id equal 3 and select the result (1, anhui, 13888888888L) (2, dave, 138888888888L) (4, anqing, 13888888888L)