Skip to content

Instantly share code, notes, and snippets.

@knoguchi
Last active August 19, 2019 15:25
Show Gist options
  • Save knoguchi/4b4be2d6a53d5cb99697 to your computer and use it in GitHub Desktop.
Save knoguchi/4b4be2d6a53d5cb99697 to your computer and use it in GitHub Desktop.
PythonとDB: DBIのcursorを理解する ref: https://qiita.com/knoguchi/items/3d5631505b3f08fa37cc
DECLARE カーソル名 CURSOR FOR SELECT文
FETCH 方向 FROM カーソル名
Connect kenji@localhost on kenji
Query set autocommit=0
Query SELECT * FROM names
Quit
Connect kenji@localhost on kenji
Query set autocommit=0
Query SELECT * FROM names
Quit
$ 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
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
kenji=> select * from names;
name
---------
keigo
tomochi
(2 rows)
MOVE 方向 FROM カーソル名
UPDATE names SET name='tomochi' WHERE CURRENT OF カーソル名
LOG: statement: BEGIN
LOG: statement: SELECT * FROM names
LOG: statement: BEGIN
LOG: statement: DECLARE "foo" CURSOR WITHOUT HOLD FOR SELECT * FROM names
LOG: statement: FETCH FORWARD 1 FROM "foo"
general_log_file = /var/log/mysql/mysql.log
general_log = 1
import MySQLdb
conn = MySQLdb.connect(db='kenji', user='kenji')
cu = conn.cursor()
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
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()
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()
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()
import psycopg2
conn = psycopg2.connect(database='kenji')
cu = conn.cursor('foo')
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment