Python and DB: Understanding DBI cursors

I investigated the cursor that appears when operating the DB with Python because it is too unknown. We investigated psycopg2 for PostgreSQL and MySQLdb for MySQL from the perspective of the difference between SQL CURSOR and Python cursor and how faithfully SQL CURSOR is implemented.

Question

• Although it has the same name as the SQL standard CURSOR, I have never seen it used in that way. What kind of relationship is it?
• Is it true that I sometimes see the explanation that " fetch one is more memory efficient than fetch all because it does not read all the results at once"?

Conclusion

The text is long, so from the conclusion.

1. Python cursor has roughly three implementations.

• Server-side cursor: Only psycopg2 was implemented correctly. ** Equivalent to SQL CURSOR ** Operations and references (FETCH, MOVE, CURRENT OF) are possible. fetchone and fetchall execute SQL FETCH NEXT and FETCH ALL, respectively.
• Client-side cursor (buffered): All query results are loaded on the client side at once. Since fetch one and fetch many are ** emulation **, there is no difference in memory efficiency on the client side and both are bad. The current line cannot be referenced from other cursors. ** Unfortunately the most common implementation **.
• Client-side cursor (no buffer): For example, MySQLdb's SSCursor corresponds to this implementation. The SSCursor manual says "server-side cursor", but it doesn't mean SQL CURSOR. Actually, it is an outrageous implementation that blocks the socket every time one line is read in order to save memory on the client side, and the next SQL statement is blocked until it is completely read. ** Worst implementation **. (Note: I know there's a blog called SSCursor and Stackoverflow's answer for reading from large tables. I can't deny that, but as the experimental section revealed, it has serious drawbacks. .)

2. In psycopg2 for PostgreSQL, declaring a ** named cursor ** is server-side. It's client-side without a name.
3. MySQLdb for MySQL is client side only.

Python cursor implementation

Correspondence table of SQL CURSOR and Python cursor

The relationship between the server-side cursor ("SSC") and the Python method is as follows: Cannot be used with client-side cursors ("CSC") unless otherwise noted.

Investigation

I investigated SQL CURSOR and Python cursor respectively.

SQL CURSOR

The cursor can get the result of a query such as a SELECT statement line by line, or move to the previous or next line. You can also refer to the current row from other SQL statements. The details are omitted because the purpose is not to explain the grammar.

Cursor declaration

Declare the cursor like this.

DECLARE cursor name CURSOR FOR SELECT statement

Get row

To get the next line of the cursor, execute a statement similar to the following: The normal direction is NEXT or FORWARD to get the row while moving forward. It is also possible to return in the middle, in which case specify PRIOR or BACKWARD. It is also possible to move to the beginning FIRST or the end LAST at once.

FETCH direction FROM cursor name

Move cursor

Use MOVE if you just want to move without getting a row. The direction specification is the same as FETCH.

MOVE direction FROM cursor name

Cursor line reference

You can also refer to the cursor line from other statements. If you do this, you can easily perform other operations while using the result of SELECT. For example, to see a row where a cursor is currently fetched, use CURRENT OF as follows:

UPDATE names SET name='tomochi'WHERE CURRENT OF cursor name

Python cursor

cursor is created by calling the .cursor () method of the connection object. Transactions work on connection objects, so if you create multiple cursors from one connection object, they will be executed within one transaction.

cursor method (part)

Only the methods that are likely to be related to SQL CURSOR are covered.

• fetchone () Fetch one row from the current row of the query and move to the next row. Equivalent to FETCH NEXT.
• fetchmany (n) Fetch n rows from the current row of the query and move to the next row. Equivalent to FETCH FORWARD n.
• fetchall () Fetch all remaining rows from the current row of the query and move to the next row. Equivalent to FETCH ALL.
• scroll (value, mode) When mode is relative, move by the variation specified by value. When mode is absolute, value = 0 is the first work and value = -1 is the last line. Equivalent to MOVE.

Experiment

Experiment and see if the Python cursor declares and uses SQL CURSOR. The method is Enable statement logging in PostgreSQL and MySQL respectively, execute normal cursors and named cursors, and compare the actually issued SQL statements.

PostgreSQL + psycopg2 Reboot with log_statement ='all' in postgresql.conf. Create a PostgreSQL db appropriately and put the table and test data.

names.sql

CREATE TABLE names (
  name varchar(100)
);

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

Anonymous cursor

psql1.py

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

The result is as follows: There is no declaration of the cursor on the server side, and all the lines are fetched.

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

Named cursor

Next, try SELECT with the named cursor in the same way. The only difference from the above is the name given to cursor ().

psql2.py

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

The result shows that the cursor is declared on the server side and FETCH is running.

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

MySQL + MySQLdb Add the following line to the [mysqld] section in my.cnf and reboot.

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Normal cursor

msql1.py

import MySQLdb
conn = MySQLdb.connect(db='kenji', user='kenji')
cu = conn.cursor()
cu.execute('SELECT * FROM names')
print cu.fetchone()
conn.close()

The result is, as expected, a simple cursorless SELECT.

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

SSCursor Next, let's experiment using the cursor class described as "server side cursor" in the MySQLdb manual. It is enabled by passing cursorclass = MySQLdb.cursors.SSCursor to connect ().

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

The result is the same as a normal cursor. No server-side cursor was declared.

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

When I read the source, it is a surprising implementation that ** blocks by not reading data from the socket **. Wouldn't it be possible to execute other statements if it was blocked at the connection level? There are two rows in the names table, but what happens if I get the first row and then SELECT it separately?

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

The execution was refused.

$ 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". I don't fully understand the MySQL Connector (Client Library) Manual, but somehow the results It seems that the next statement cannot be executed in the same thread because it has not been completely read. It becomes a state where it can be read but not written from other threads. SSCursor is useless.

Server-side cursor reference

Use one server-side cursor and one regular cursor in PostgreSQL. Browse to the server-side cursor. A very simple example of updating'kenji'in the names table to'tomochi'.

psycopg2_named_cursor_example.py

import psycopg2

conn = psycopg2.connect(database='kenji')

cu1 = conn.cursor('foo')   #Server-side cursor 1
cu2 = conn.cursor()  #Normal cursor 2

#You don't have to lock the rows, but somehow.
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;")   #UPDATE the current row of cursor 1
cu2.close()

conn.commit()
conn.close()

The trace log is as follows. The SQL is running as expected.

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' in the names table has been updated to'tomochi'.

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

Move cursor

I also tried scroll () experiment, but it was omitted because there was no difference from SQL that should be noted.