Python 2.4.3
RedHat 5.5 x86
Python 基础语法参考：
Python 基础语法知识
一. cx_Oracle
Python 连接Oracle 数据库，需要使用cx_Oracle 包。
该包的下载地址：http://cx-oracle.sourceforge.net/
下载的时候，注意版本，对不同版本的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=1521)))(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,'tel':13888888888}
cr.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=13888888888)
--这里我们直接写参数
rs=cr.fetchall()
print rs
cr.close()
db.close()
[root@rac1 u01]# python db.py
This is Fetchall!
print all:([(1, 13865999999L), (2, 13888888888L)])
print by row:
(1, 13865999999L)
(2, 13888888888L)
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 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