PythonとDB: DBIのcursorを理解する ref: https://qiita.com/knoguchi/items/3d5631505b3f08fa37cc

file0.txt

DECLARE カーソル名　CURSOR FOR　SELECT文

file1.txt

FETCH 方向 FROM カーソル名

file11.txt

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

file13.txt

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

file15.txt

$ 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

file17.txt

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

file18.txt

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

file2.txt

MOVE 方向 FROM カーソル名

file3.txt

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

file6.txt

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

file8.txt

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

file9.txt

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()

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()

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()

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()

psql2.py

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

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()