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の回答があるのは承知しています。それを否定するものではありませんが、実験の項で明らかになったように、重大な欠点があります。）

1. PostgreSQL用のpsycopg2では名前付きカーソルを宣言するとサーバーサイドになる。名前なしではクライアントサイドになる。
2. 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を適当に作成し、テーブルとテストデータを入れる。

names.sql

CREATE TABLE names (
  name varchar(100)
);

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

名前なしカーソル

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()に名前を与えただけである。

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

通常カーソル

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を引き渡すと有効になる。

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したら何が起きるだろうか？

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(クライアントライブラリ)のマニュアルを完全には理解していないが、どうも結果を完全に読み切っていないため、同一スレッド中で次の文が実行できないような状態にあるらしい。他のスレッドからは読み込みは出来ても書き込みが出来ない状態になる。SSCursorは使い物にならない。

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

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

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との違いはなかったので略。