検証環境
- Oracle Cloud利用
- Oracle Linux 7.7 (VM.Standard2.1)
- Python 3.6
- cx_Oracle 7.3
- Oracle Database 19.5 (ATP, 1OCPU)
- Oracle Instant Client 18.5
結果セットのFetch
基本的には第3回で解説したように、Cursorオブジェクトのexecute()メソッドのコール後、Cursorオブジェクトに対してループを回すことでレコードのfetchが可能です。
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
for row in cursor.execute(SQL):
print(row)
一方、PEP 249 (Python Database API Specification v2.0)に準拠した、以下のメソッドによるfetchも可能です。
fetchone()
名前から想像がつくと思いますが、1行だけfetchするメソッドです。下記サンプルのようにループの中で1行ずつfetchするよりも、
select 1+1 from dual
のような、1行だけ戻ることが分かっているSELECT文や、後述のスクローラブルカーソル向けです。
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
while True:
row = cursor.fetchone()
if row is None:
break
print(row)
fetchall()
fetchone()とは逆に、一度に全行fetchするメソッドです。
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
rows = cursor.fetchall()
for row in rows:
print(row)
fetchmany()
fetchone()とfetchall()の中間で、引数に指定した行数ずつfetchするメソッドです。下記例だと3件ずつfetchしています。
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
while True:
rows = cursor.fetchmany(3)
if not rows:
break
for row in rows:
print(row)
arraysizeの調整
fetch系のメソッドを発行した際、実際には各メソッドを実行した都度、取得しようとしている件数のfetchが行われているわけではありません。Cursorオブジェクトのarraysizeという変数に指定された件数(デフォルトは100件)ずつOracle Clientのバッファに読み込み、このバッファを元にcx_Oracleのfetchが行われます。これは、データベースとのラウンドトリップの回数を減らすための措置で、cx_Oracleに限らず、Oracle Databaseのアクセスドライバの多くに備わっている仕組みです。
単純に考えると、取得予定件数を一度で取得できるだけのarraysizeを指定してfetchall()で取得するのが最高高率に思えます。しかし実際には、取得予定件数が判明している方が稀です。また、サーバー側の処理能力やSQLの重さ、該当アプリケーションで使用してもよいCPU使用量やメモリ量、ネットワーク帯域などの制約を受けるため、arraysizeはこれらを考慮した値に設定する必要があります。以下は、arraysizeを1000に設定してSQLを発行する場合のサンプルです。arraysizeと同じ件数をfetchmany()で取得するようにしています。
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.arraysize = 1000
cursor.execute(SQL)
while True:
rows = cursor.fetchmany(cursor.arraysize)
if not rows:
break
for row in rows:
print(row)
結果セットのスクロール
通常、fetchは後方に進むのみです。一方、cx_Oracleでは、前方を含め任意のレコードに現在位置を変更させることができる、スクローラブルカーソルを実装しています。
スクローラブルカーソルを使用するには、Cursorオブジェクト生成時に、引数scrollableをTrueに設定する必要があります。カーソルのスクロールにはscroll()メソッドを使用します。このメソッドはvalueという引数とmodeという引数が存在します。mode引数に指定した値によってvalue引数は意味が変わります。以下、mode引数をベースに、どのような動きが可能なのかを一覧にしました。
| mode引数の値 | 意味とvalue引数の動き |
|---|---|
| first | 最初の行にスクロールします。 value引数は無視されます。 |
| last | 最後の行にスクロールします。 value引数は無視されます。 |
| absolute | value引数に指定した行数目の行に移動します。 |
| relative | mode引数のデフォルト値です。 value引数に指定した行数分移動します。 戻る場合はマイナス値を指定します。 |
以下サンプルです。なお、スクローラブルカーソルは通常のカーソルより負荷が高いため、必要時以外は使用しないようにしましょう。
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select object_id - 1, owner, object_name, object_type
from all_objects
order by object_id
fetch first 10 rows only
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor(scrollable=True) as cursor:
cursor.execute(SQL)
print("******** 全10行表示 ********")
for row in cursor:
print(row)
print("******** 最初の行を表示(value引数が無視される) ********")
cursor.scroll(8, "first")
print(cursor.fetchone())
print("******** 最後の行を表示 ********")
cursor.scroll(mode="last")
print(cursor.fetchone())
print("******** 3行目を表示 ********")
cursor.scroll(3, "absolute")
print(cursor.fetchone())
print("******** 4行後を表示 ********")
cursor.scroll(4)
print(cursor.fetchone())
print("******** 2行前を表示 ********")
cursor.scroll(-2, "relative")
print(cursor.fetchone())
$ python sample04e.py
******** 全10行表示 ********
(1, 'SYS', 'C_OBJ#', 'CLUSTER')
(2, 'SYS', 'I_OBJ#', 'INDEX')
(3, 'SYS', 'TAB$', 'TABLE')
(4, 'SYS', 'CLU$', 'TABLE')
(5, 'SYS', 'C_TS#', 'CLUSTER')
(6, 'SYS', 'I_TS#', 'INDEX')
(7, 'SYS', 'C_FILE#_BLOCK#', 'CLUSTER')
(8, 'SYS', 'I_FILE#_BLOCK#', 'INDEX')
(9, 'SYS', 'C_USER#', 'CLUSTER')
(10, 'SYS', 'I_USER#', 'INDEX')
******** 最初の行を表示(value引数が無視される) ********
(1, 'SYS', 'C_OBJ#', 'CLUSTER')
******** 最後の行を表示 ********
(10, 'SYS', 'I_USER#', 'INDEX')
******** 3行目を表示 ********
(3, 'SYS', 'TAB$', 'TABLE')
******** 4行後を表示 ********
(7, 'SYS', 'C_FILE#_BLOCK#', 'CLUSTER')
******** 2行前を表示 ********
(5, 'SYS', 'C_TS#', 'CLUSTER')