1,python操作Oracle数据库,首先需要安装cx_Oracle包。
获取安装包的地址:
http://cx-oracle.sourceforge.net/
http://cx-oracle.sourceforge.net/
2,另外还需要oracle的一些类库,此时需要在运行python的机器上安装Oracle Instant Client软件包,可以通过下面地址获得:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
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
$ 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/lib
$ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2/client/lib
3,创建一个python文件,测试安装是否成功
复制代码 代码示例:
import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("select * from dual")
row = cursor.fetchone ()
print row[0]
cursor.close ()
conn.close ()
4,python操作oracle数据库建表和插入操作
复制代码 代码示例:
import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("CREATE TABLE TEST(ID INT, COL1 VARCHAR(32), COL2 VARCHAR(32), COL3 VARCHAR(32))")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(1, 'a', 'b', 'c')")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(2, 'aa', 'bb', 'cc')")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(3, 'aaa', 'bbb', 'ccc')")
conn.commit()
cursor.close ()
conn.close ()
5,接下介绍python操作oracle查询,查询通常有两种方式:
一种是使用cursor.fetchall()获取所有查询结果,然后再一行一行的迭代;
另一种每次通过cursor.fetchone()获取一条记录,直到获取的结果为空为止。
例子:
复制代码 代码示例:
import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("SELECT * FROM TEST")
rows = cursor.fetchall()
for row in rows:
print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])
print "Number of rows returned: %d" % cursor.rowcount
cursor.execute ("SELECT * FROM TEST")
while (1):
row = cursor.fetchone()
if row == None:
break
print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close ()
conn.close ()