PythonでDBを操作するときに出てくる
cursor
について、あまりにも実体不明なので調べた。SQL CURSORとPython cursorの違い、SQL CURSORをどれだけ忠実に実装しているか、という視点でPostgreSQL用のpsycopg2とMySQL用のMySQLdbについて調査した。
CURSOR
と名前が同じな割には、そのような使われ方をしているのを見たことがない。どういう関係なのか？
fetchone
は全結果を一度に読み込まないので
fetchall
よりメモリ効率が良い」という説明をたまに見るが本当なのか？
本文は長いので結論から。
Python
cursor
はざっくり３種類の実装がある。
FETCH NEXT
,
FETCH ALL
を実行する。
PostgreSQL用のpsycopg2では 名前付きカーソル を宣言するとサーバーサイドになる。名前なしではクライアントサイドになる。
MySQL用のMySQLdbはクライアントサイドだけ。
カーソルは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');
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
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
ソースを読んでみるとソケットからデータ読み込まないことでブロックという驚愕の実装。コネクションレベルでブロックしていたら他の文が実行できないのではないだろうか？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'に更新された。