python 读写Oracle10g数据简介

浏览数：18 / 时间：2015年06月12日

1、测试环境：

Centos6 X86_64
python 2.6
Oracle 10g

2、安装cx_Oracle 和 Oracle InstantClient:

http://www.rpmfind.net/linux/rpm2html/search.php?query=cx_oracle
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

3、编辑当前用户的 .bash_profile, 在文件末尾增加下行：

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/10.2.0.3/client64/lib

命令行执行 source .bash_profile

4、现在就可以用 python 脚本非常轻松的读写Oracle数据库

数据查询示范脚本 select_ora.py

# This script prints the data in Oracle Sample table scott.emp .
# Run with a parameter ‘PageSize‘ in integer form, the output pauses at the end of every page.
# Run without a parameter or the parameter is not in integer form, the output doesn‘t pasue.
# Like: 
# $ python select_ora.py 30 
# $ python select_ora.py 
#
import sys
import cx_Oracle
try:
    intPageSize = int(sys.argv[1])
except:
    intPageSize = -1
    #print "Please input an integer."
    #quit()
 
conn = cx_Oracle.connect(‘scott/[email protected]/c6115‘)
cursor = conn.cursor ()
cursor.execute ("select * from emp")
print "EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO"
print "========================================================"
while (1):
    row = cursor.fetchone()
    if row == None: break
    print "%d, %s, %s, %s, %s, %s, %s, %s" % (row[0], row[1], row[2], row[3], row[4],row[5],row[6],row[7])
    if intPageSize <> -1 and cursor.rowcount % intPageSize == 0 :
        strPress = raw_input( "Row: %d" % cursor.rowcount + ". Press Enter to continue, q to quit..." )
        if strPress == ‘q‘: break
 
print "Number of rows returned: %d" % cursor.rowcount
cursor.close ()
conn.close ()

数据插入示范脚本 insert_ora.py

import cx_Oracle
startNum = raw_input("Start Num:")
endNum = raw_input("End Num:")
 
conn = cx_Oracle.connect(‘scott/[email protected]/c6115‘)
cursor = conn.cursor()
i = int(startNum)
while (1):
    i = i+1
    if i > int(endNum):
        break 
    theNum=str(i)
    cursor.execute("insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values("+theNum+",‘FORD"+theNum+"‘,‘CLERK‘,7782,‘09-JUN-81‘,2500,0,30)")
    conn.commit()
    print "Line "+ theNum +" inserted."
cursor.close()
conn.close()