Edited at

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

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


疑問


  • SQL標準のCURSORと名前が同じな割には、そのような使われ方をしているのを見たことがない。どういう関係なのか?

  • fetchoneは全結果を一度に読み込まないのでfetchallよりメモリ効率が良い」という説明をたまに見るが本当なのか?


結論

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



  1. Python cursorはざっくり3種類の実装がある。


    • サーバーサイドカーソル: 正しく実装されていたのは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() クエリの現在行から1行取得し、次の行へ移動。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との違いはなかったので略。