Python實戰之Oracle資料庫操作

本文地址：http://blog.csdn.net/kongxx/article/details/7107661

1. 要想使Python可以操作Oracle資料庫，首先需要安裝cx_Oracle包，可以通過下面的地址來擷取安裝包

http://cx-oracle.sourceforge.net/2. 另外還需要oracle的一些類庫，此時需要在運行python的機器上安裝Oracle Instant Client軟體包，可以通過下面地址獲得
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html找到符合自己平台的包，然後安裝，這裡我使用的是rpm包，所以使用以下命令安裝
$ sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm裝完畢後還需要設定一下環境變數，如下$ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2/client/lib3. 建立一個簡單的python檔案，測試安裝是否成功import cx_Oracle</p><p>conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')<br />cursor = conn.cursor ()<br />cursor.execute ("select * from dual")<br />row = cursor.fetchone ()<br />print row[0]</p><p>cursor.close ()<br />conn.close ()4. 下面看一個資料庫建表和插入操作import cx_Oracle</p><p>conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')<br />cursor = conn.cursor ()</p><p>cursor.execute ("CREATE TABLE TEST(ID INT, COL1 VARCHAR(32), COL2 VARCHAR(32), COL3 VARCHAR(32))")</p><p>cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(1, 'a', 'b', 'c')")<br />cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(2, 'aa', 'bb', 'cc')")<br />cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(3, 'aaa', 'bbb', 'ccc')")<br />conn.commit()</p><p>cursor.close ()<br />conn.close ()5. 下面再來看看查詢，查詢通常有兩種方式：一種是使用cursor.fetchall（）擷取所有查詢結果，然後再一行一行的迭代；另一種每次通過cursor.fetchone()擷取一條記錄，直到擷取的結果為空白為止。看一下下面的例子：import cx_Oracle</p><p>conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')<br />cursor = conn.cursor ()</p><p>cursor.execute ("SELECT * FROM TEST")<br />rows = cursor.fetchall()<br />for row in rows:<br />print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])</p><p>print "Number of rows returned: %d" % cursor.rowcount</p><p>cursor.execute ("SELECT * FROM TEST")<br />while (1):<br />row = cursor.fetchone()<br />if row == None:<br />break<br />print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])</p><p>print "Number of rows returned: %d" % cursor.rowcount</p><p>cursor.close ()<br />conn.close ()