[cx_Oracle入門](第4回) 結果セットのFetchとスクロール

# 検証環境
- 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回](https://qiita.com/nakaie/items/48a2142671c3125fac5e)で解説したように、Cursorオブジェクトのexecute()メソッドのコール後、Cursorオブジェクトに対してループを回すことでレコードのfetchが可能です。

```python:sample03b.py(抜粋)
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)](https://www.python.org/dev/peps/pep-0249/)に準拠した、以下のメソッドによるfetchも可能です。

### fetchone()
名前から想像がつくと思いますが、1行だけfetchするメソッドです。下記サンプルのようにループの中で1行ずつfetchするよりも、
`select 1+1 from dual`
のような、1行だけ戻ることが分かっているSELECT文や、後述のスクローラブルカーソル向けです。

```python:sample04a.py(抜粋)
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するメソッドです。

```python:sample04b.py(抜粋)
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しています。

```python:sample04c.py(抜粋)
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()で取得するようにしています。

```python:sample04d.py(抜粋)
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 | 最初の行にスクロールします。<br>value引数は無視されます。 |
| last | 最後の行にスクロールします。<br>value引数は無視されます。 |
| absolute | value引数に指定した行数目の行に移動します。 |
| relative | mode引数のデフォルト値です。<br>value引数に指定した行数分移動します。<BR>戻る場合はマイナス値を指定します。 |

以下サンプルです。なお、スクローラブルカーソルは通常のカーソルより負荷が高いため、必要時以外は使用しないようにしましょう。


```python:sample04e.py
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')
```