PythonとDB: DBIのcursorを理解する

PythonでDBを操作するときに出てくる`cursor`について、あまりにも実体不明なので調べた。SQL CURSORとPython cursorの違い、SQL CURSORをどれだけ忠実に実装しているか、という視点でPostgreSQL用のpsycopg2とMySQL用のMySQLdbについて調査した。

# 疑問
* SQL標準の`CURSOR`と名前が同じな割には、そのような使われ方をしているのを見たことがない。どういう関係なのか？
* 「`fetchone`は全結果を一度に読み込まないので`fetchall`よりメモリ効率が良い」という説明をたまに見るが本当なのか？


# 結論
本文は長いので結論から。

1. Python `cursor`はざっくり３種類の実装がある。
 * サーバーサイドカーソル: 正しく実装されていたのはpsycopg2だけ。**SQLのCURSORと同等**の操作と参照(FETCH, MOVE, CURRENT OF)が可能。fetchone, fetchallはそれぞれSQLの`FETCH NEXT`, `FETCH ALL`を実行する。
 * クライアントサイドカーソル(バッファあり): クエリーの結果はすべてクライアント側に一度にロードされる。fetchoneとfetchmanyは**エミュレーション**のため、クライアント側のメモリ効率に差はなくどちらも悪い。現在の行を他のカーソルから参照することもできない。**残念ながらもっとも一般的な実装**。
 * クライアントサイドカーソル(バッファなし): 例えばMySQLdbのSSCursorがこの実装に相当する。SSCursorのマニュアルでは"server-side cursor"と書かれているが、SQLのCURSORという意味ではない。実はクライアント側のメモリを節約するために一行読む度にソケットをブロックするという暴挙にでた実装であり、完全に読み込みが終わるまで次のSQL文がブロックされる。**最悪の実装**である。(注：大きなテーブルから読み込むにはSSCursorというブログやStackoverflowの回答があるのは承知しています。それを否定するものではありませんが、実験の項で明らかになったように、重大な欠点があります。）

2. PostgreSQL用のpsycopg2では**名前付きカーソル**を宣言するとサーバーサイドになる。名前なしではクライアントサイドになる。
3. MySQL用のMySQLdbはクライアントサイドだけ。

## Python cursorの実装
|タイプ| DB         | DBI      | cursor宣言方法 | 
|:--|:-----------|:---------|:-------------|
| サーバーサイド  | PostgreSQL | psycopg2 | cursor('名前') |
| クライアントサイド(バッファあり)| PostgreSQL | psycopg2 | cursor()|
| クライアントサイド(バッファあり)| MySQL | MySQLdb | cursor() |
| クライアントサイド (バッファなし)| MySQL | MySQLdb | connect( cursorclass = MySQLdb.cursors.SSCursor )としてcursor() |

## SQL CURSORとPython cursorの対応表
サーバーサイドカーソル("SSC")とPythonのメソッドの関係は次のとおり。クライアントサイドカーソル("CSC")では特記のない限り使えない。

| SQL | Python | 備考 |
|:-----------|:---------|:------|
| DECLARE CURSOR name| psycopg2 ではcursor('name') | ドライバの実装次第で標準化されていない。Pythonの弱いところの一つ。 |
| FETCH NEXT | fetchone() | CSCではエミュレーション |
| FETCH FORWARD n    | fetchmany(n) | CSCではエミュレーション |
| FETCH ALL    | fetchall() | CSCではエミュレーション　|
| FETCH PRIOR | 該当なし | scroll(-2, mode='relative'); fetchone()で代替可 |
| FETCH FIRST | 該当なし | scroll(-1, mode='absolute'); fetchone()で代替可 |
| FETCH LAST | 該当なし | scroll(-2, mode='absolute'); fetchone()で代替可 |
| MOVE mode value | scroll(value, mode) | CSCではエミュレーションになり、先に進ませることしかできない。後戻りさせようとするとNotSupportedError例外が発生する。 |
| CURRENT OF | 同等 | カーソル行の参照　|


# 調査
SQL CURSORとPython cursorについてそれぞれ調べた。

## SQL CURSOR

カーソルはSELECT文などのクエリの結果を1行ずつ逐次取得したり、前後の行に移動したりすることができる。また現在行を他のSQL文から参照することもできる。なお文法の解説が目的ではないので詳細は省略。

### カーソルの宣言

カーソルはこんな感じで宣言する。

```
DECLARE カーソル名　CURSOR FOR　SELECT文
```

### 行の取得

カーソルの次の行を取得するには、次のような文を実行する。通常方向は`NEXT`または`FORWARD`で先に進みながら行を取得する。途中で戻ることも可能で、その場合は`PRIOR`または`BACKWARD`を指定する。先頭`FIRST`や末尾`LAST`に一気に移動することも可能である。

```
FETCH 方向 FROM カーソル名
```

### カーソルの移動

行を取得せずに移動だけ行う場合は`MOVE`を使う。方向の指定はFETCHと同じ。

```
MOVE 方向 FROM カーソル名
```
### カーソル行の参照

カーソル行を他の文から参照することもできる。これをやるとSELECTの結果を利用しながら他の操作を行うことが簡単にできる。例えばあるカーソルが現在取得済みの行を参照するには次のように`CURRENT OF`を使う。

```
UPDATE names SET name='tomochi' WHERE CURRENT OF カーソル名
```

## Python cursor

`cursor`はコネクションオブジェクトの.cursor()メソッドを呼ぶことで作成する。トランザクションはコネクションオブジェクトに対して働くため、一つのコネクションオブジェクトから複数のカーソルを作成した場合、それらは一つのトランザクション内で実行される。

### cursor メソッド(一部)
SQL CURSORに関係のありそうなメソッドだけ取り上げる。

* fetchone() クエリの現在行から１行取得し、次の行へ移動。`FETCH NEXT`に相当。
* fetchmany(n) クエリの現在行からn行取得し、次の行へ移動。`FETCH FORWARD n`に相当。
* fetchall() クエリの現在行から残り全行を取得し、次の行へ移動。`FETCH ALL`に相当。
* scroll(value, mode) modeがrelativeの場合valueで指定された変分だけ移動。modeがabsoluteの場合value=0は先頭業、value=-1は最終行である。`MOVE`に相当。


# 実験
Python cursorがSQL CURSORを宣言、利用しているのか実験して確かめる。方法は
PostgreSQLとMySQLそれぞれでステートメントログを有効にして、通常のカーソル、名前付きカーソルを実行し、実際に発行されたSQL文を比較する。

## PostgreSQL + psycopg2
postgresql.confにてlog_statement = 'all'として再起動する。
PostgreSQLのdbを適当に作成し、テーブルとテストデータを入れる。

```sql:names.sql
CREATE TABLE names (
  name varchar(100)
);

INSERT INTO names VALUES ('kenji');
INSERT INTO names VALUES ('keigo');
```

### 名前なしカーソル

```py:psql1.py
import psycopg2
conn = psycopg2.connect(database='kenji')
cu = conn.cursor()
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
```

結果は次のとおり、サーバー側のカーソルの宣言はなく、全行を取得している。

```
LOG:  statement: BEGIN
LOG:  statement: SELECT * FROM names
```

### 名前付きカーソル
次に名前付きカーソルで同様にSELECTしてみる。上との違いはcursor()に名前を与えただけである。

```py:psql2.py
import psycopg2
conn = psycopg2.connect(database='kenji')
cu = conn.cursor('foo')
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
```

結果は、サーバー側でカーソルが宣言され、FETCHが実行されているのがわかる。

```
LOG:  statement: BEGIN
LOG:  statement: DECLARE "foo" CURSOR WITHOUT HOLD FOR SELECT * FROM names
LOG:  statement: FETCH FORWARD 1 FROM "foo"
```

## MySQL + MySQLdb
my.cnf内の[mysqld]セクションに次の行追加して、再起動する。

```
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
```

### 通常カーソル
```py:msql1.py
import MySQLdb
conn = MySQLdb.connect(db='kenji', user='kenji')
cu = conn.cursor()
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
```

結果は、予想通り単純なカーソルなしのSELECTである。

```
Connect	kenji@localhost on kenji
Query	set autocommit=0
Query	SELECT * FROM names
Quit
```

### SSCursor
次にMySQLdbのマニュアルで"server side cursor"と書かれているカーソルクラスを使って実験してみる。connect()に`cursorclass=MySQLdb.cursors.SSCursor`を引き渡すと有効になる。

```py:msql2.py
import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.connect(db='kenji', user='kenji',
                       cursorclass = MySQLdb.cursors.SSCursor)
cu = conn.cursor()
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
```

結果は、通常のカーソルと変わらず。サーバー側のカーソルは宣言されなかった。

```
Connect	kenji@localhost on kenji
Query	set autocommit=0
Query	SELECT * FROM names
Quit
```

ソースを読んでみると**ソケットからデータ読み込まないことでブロック**という驚愕の実装。コネクションレベルでブロックしていたら他の文が実行できないのではないだろうか？namesテーブルには2行入っているが、1行目を取得したあと、別途SELECTしたら何が起きるだろうか？

```py:msql3.py
import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.connect(db='kenji', user='kenji',
                       cursorclass = MySQLdb.cursors.SSCursor)
cu1 = conn.cursor()
cu2 = conn.cursor()
cu1.execute('SELECT name as name1 FROM names')
print "CU1", cu1.fetchone()
cu2.execute('SELECT name as name2 FROM names')
print "CU2", cu2.fetchone()
conn.close()
```

なんと実行が拒否された。

```
$ python msql3.py
CU1 ('kenji',)
Traceback (most recent call last):
  File "msql3.py", line 9, in <module>
    cu2.execute('SELECT name as name2 FROM names')
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in <bound method SSCursor.__del__ of <MySQLdb.cursors.SSCursor object at 0x7f6d9b542f50>> ignored
Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in <bound method SSCursor.__del__ of <MySQLdb.cursors.SSCursor object at 0x7f6d9b542e50>> ignored
```

"you can't run this command now"だと。[MySQL Connector(クライアントライブラリ)のマニュアル](http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html)を完全には理解していないが、どうも結果を完全に読み切っていないため、同一スレッド中で次の文が実行できないような状態にあるらしい。他のスレッドからは読み込みは出来ても書き込みが出来ない状態になる。SSCursorは使い物にならない。

## サーバーサイドカーソルの参照

PostgreSQLでサーバーサイドカーソルと通常のカーソルを一つずつ使い。サーバーサイドカーソルを参照する。namesのテーブル内の'kenji'を'tomochi'に更新するという極簡単な例。


```py:psycopg2_named_cursor_example.py
import psycopg2

conn = psycopg2.connect(database='kenji')

cu1 = conn.cursor('foo')   # サーバーサイドカーソル1
cu2 = conn.cursor()  # 通常のカーソル2

# 行ロックをする必要はないけど、なんとなく。
cu1.execute("SELECT name FROM names WHERE name=%s FOR UPDATE;", ('kenji',))

print cu1.fetchone()

cu2.execute("UPDATE names SET name='tomochi' WHERE CURRENT OF foo;")   # カーソル1の現在行をUPDATEする
cu2.close()

conn.commit()
conn.close()
```

トレースログは次のとおり。期待どおりのSQLが実行されている。

```
LOG:  statement: BEGIN
LOG:  statement: DECLARE "foo" CURSOR WITHOUT HOLD FOR SELECT name FROM names WHERE name='kenji' FOR UPDATE
LOG:  statement: FETCH FORWARD 1 FROM "foo"
LOG:  statement: UPDATE names SET name='tomochi' WHERE CURRENT OF foo
LOG:  statement: COMMIT
```

namesのテーブル内の'kenji'が'tomochi'に更新された。

```
kenji=> select * from names;
  name
---------
 keigo
 tomochi
(2 rows)
```


## カーソルの移動
scroll()の実験もやってみたが、特記すべきSQLとの違いはなかったので略。