Oracle python cx_Oracle 入门
〇、使用数据库驻留连接池
数据库驻留连接池是 Oracle Database 11g 的一个新特性。它对 Web 应用程序常用的短期脚本非常有用。它允许随着 Web 站点吞吐量的增长对连接数量进行扩充。它还支持多台计算机上的多个 Apache 进程共享一个小规模的数据库服务器进程池。没有 DRCP，Python 连接必须启动和终止一个服务器进程。
所有脚本都可使用来自服务器池的数据库服务器，不再需要时将退回服务器。
一、Oracle 简单查询
查询的基础始终是相同的：
1. 分析要执行的语句。
2. 绑定数据值（可选）。
3. 执行语句。
4. 从数据库中获取结果。
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')cur = con.cursor() #cursor() 方法打开语句要使用的游标。cur.execute('select * from departments order by department_id') #execute() 方法分析并执行语句。for result in cur: #循环从游标获取每一行并输出该行。print resultcur.close()con.close()
二、Oracle 获取数据
从 Oracle 数据库中获取数据的方式有多种
1）使用cursor.fetchone()方法
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.01/orcl')cur = con.cursor()cur.execute('select * from departments order by department_id')row = cur.fetchone() # fetchone() 方法只返回一行作为一个字节组print rowrow = cur.fetchone() # 多次调用该方法后，返回连续的多行print rowcur.close()con.close()
执行完毕后，两次 fetchone() 调用输出两条记录
2）使用cursor.fetchmany()方法
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')cur = con.cursor()cur.execute('select * from departments order by department_id')res = cur.fetchmany(numRows=3) #返回一个字节组列表，numRows 参数指定应返回三行print rescur.close()con.close()
执行完毕后，以字节组列表形式返回了表的头三行
3）使用cursor.fetchall()方法
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')cur = con.cursor()cur.execute('select * from departments order by department_id')res = cur.fetchall() #返回所有行print res #输出是一个字节组列表，每个字节组包含一行的数据for r in res: #每个字节组分别输出，选择使用哪种获取方法主要取决于您希望如何处理返回的数据print rcur.close()con.close()
三、提高查询性能
通过增加每批从 Oracle 返回到 Python 程序的行数来提高查询性能的方法。
import timeimport cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')start = time.time()cur = con.cursor()cur.arraysize =100 #每次从数据库向 Python 的缓存返回 100 条记录cur.execute('select * from bigtab')res = cur.fetchall()# print res # uncomment to display the query resultselapsed = (time.time() - start)print elapsed, " seconds"cur.close()con.close()
cursor.arraysize 合理设置，可以减少对数据库的“往返”次数，通常还会降低网络负载并减少数据库服务器上下文切换次数
从数据库请求更多的数据之前，fetchone()、fetchmany()、甚至 fetchall() 方法都将从缓存读取数据。
要增加 arraysize，需要在时间/空间方面进行权衡。arraysizes 越大，Python 中用于缓存记录需要的内存也越大。
四、使用绑定变量
绑定变量允许您使用新值重新执行语句，避免了重新分析语句的开销。绑定变量提高了代码可重用性，降低了 SQL 注入攻击的风险。
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')cur = con.cursor()cur.prepare('select * from departments where department_id = :id') #绑定变量前的准备cur.execute(None, {'id': 210}) #第一次绑定变量执行，通过 python 字典的方式进行传参res = cur.fetchall()print rescur.execute(None, {'id': 110}) #第二次绑定变量执行res = cur.fetchall()print rescur.close()con.close()
该语句包含一个绑定变量“:id”。该语句只准备了一次，但针对 WHERE 子句的不同值执行了两次。
因为 prepare() 方法已经对该语句进行了设置，因此对 execute() 使用特殊符号“None”代替该语句的文本参数。
cx_Oracle 驱动程序支持 INSERT 语句的数组绑定，这样可以大大提高单行插入的性能。
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')rows =[(1,"First"),(2,"Second"),(3,"Third"),(4,"Fourth"),(5,"Fifth"),(6,"Sixth"),(7,"Seventh")]cur = con.cursor()cur.bindarraysize =7 # bindarraysize 设置为 7，意味着一次就插入全部七行cur.setinputsizes(int, 20) # setinputsizes() 调用描述了列的情况，第一列是整数。第二列最多为 20 个字节cur.executemany("insert into mytab(id, data) values (:1, :2)", rows) #调用插入全部七行。#con.commit() #commit() 调用被注释掉了，因此不会真正被插入数据库中# Now query the results backcur2 = con.cursor() #当执行第二次查询的时候，新建了一个游标cur2.execute('select * from mytab')res = cur2.fetchall()print rescur.close()cur2.close()con.close()
五、创建事务
在 Oracle 数据库中操作数据（插入、更新或删除数据）时，更改的数据或新数据在提交至数据库前仅在数据库会话中可用。更改的数据提交至数据库，然后可供其他用户和会话使用。这是一个数据库事务。
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')rows =[(1,"First"),(2,"Second"),(3,"Third"),(4,"Fourth"),(5,"Fifth"),(6,"Sixth"),(7,"Seventh")]cur = con.cursor()cur.bindarraysize =7cur.setinputsizes(int,20)cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)con.commit() #commit() 是针对连接执行，而非针对游标，因此前面使用的是数据库连接的对象# Now query the results backcur2 = con.cursor()cur2.execute('select * from mytab')res = cur2.fetchall()print rescur.close()cur2.close()con.close()
如果需要在脚本中启动回滚，则可使用 con.rollback() 方法。
六、使用 PL/SQL 存储函数和过程
PL/SQL 是 Oracle 对 SQL 的过程语言扩展。PL/SQL 过程和函数在数据库中存储和运行。使用 PL/SQL 允许所有数据库应用程序重用逻辑，无论应用程序以何种方式访问数据库。许多与数据相关的操作在 PL/SQL 中的执行速度比将数据提取到一个程序中（例如，Python）然后再进行处理的速度快。Oracle 还支持 Java 存储过程。
import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')cur = con.cursor()res = cur.callfunc('myfunc', cx_Oracle.NUMBER,('abc',2))print rescur.close()con.close()
该脚本使用 callfunc() 执行此函数。常量 cx_oracle.NUMBER 指示返回值是数字。PL/SQL 函数的两个参数作为一个字节组传输并绑定到该函数的参数。
要调用 PL/SQL 过程，使用 cur.callproc() 方法。
七、连续查询通知
连续查询通知（也称为数据库更改通知）允许应用程序在表更改时（例如，向表中插入行）接收通知。在许多情况下这一功能非常有用，包括中间层缓存无效的情况。缓存可能会容纳一些与表数据有依赖关系的值。如果表发生更改，缓存的值也必须按照新信息进行更新。
One Response