Python-连接-Oracle-示例

Python 连接 Oracle 示例 RedHat 5 5 x86 Python 基础语法参考 Python 基础语法知识 一 cx Oracle Python 连接 Oracle 数据库 需要使用 cx Oracle 包 该包的下载地址 http cx O 下载的时候 注意版本 对不同版本的 Oracle 和平台 都有不同的 cx Oracle root rac1 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 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 Oracle 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 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 network jlib export THREADS FLAG native export TEMP tmp export TMPDIR tmp 然后 source 应用一下 二 Python 连 Oracle 的基本操作 2 1 DB 连接和关闭 DB 连接 2 1 1 方法一 用户名 密码和监听 分开写 root rac1 u01 cat db py import cx Oracle db cx Oracle connect system oracle 192 168 2 42 1521 dave print db version db close root rac1 u01 python db py 10 2 0 1 0 2 1 2 方法二 用户名 密码和监听写在一起 root rac1 u01 cat db py import cx Oracle db cx Oracle connect system oracle 192 168 2 42 1521 dave print db version db close root rac1 u01 python db py 10 2 0 1 0 2 1 3 方法三 配置监听并连接 root rac1 u01 cat db py import cx Oracle tns cx Oracle makedsn rac1 1521 dave1 db cx Oracle connect system oracle tns print tns print db version vs db version split print vs if vs 0 10 print This is Oracle 10g db close root rac1 u01 python db py DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST rac1 PORT 15 21 CONNECT DATA SID dave1 10 2 0 1 0 10 2 0 1 0 This is Oracle 10g 2 2 建立 cursor 并执行 SQL 语句 root rac1 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 all 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 telcr 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 root rac1 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 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 LOB 三 一个完成的示例 在这个例子里 我们将用 Python 对 DB 进行一些操作 包括 创建一张表 并插入一些 数据 在修改其中的部分数据 root rac1 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 Oracle 192 168 2 42 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 environ NLS 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 tianlesoftwaresqlDML sql db dt id 2 name dave phone 138888888888 id 3 name Oracle phoneid 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 root rac1 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 2 dave 138888888888L 3 Oracle 4 anq