Python und DB: DBI-Cursor verstehen

Ich habe den "Cursor" untersucht, der beim Betrieb der Datenbank mit Python angezeigt wird, da er zu unbekannt ist. Wir haben psycopg2 für PostgreSQL und MySQLdb für MySQL unter dem Gesichtspunkt des Unterschieds zwischen SQL CURSOR und Python-Cursor und der genauen Implementierung von SQL CURSOR untersucht.

Frage

• Obwohl es den gleichen Namen wie der SQL-Standard "CURSOR" hat, habe ich noch nie gesehen, dass es auf diese Weise verwendet wird. Was ist das für eine Beziehung?
• Stimmt es, dass ich manchmal die Erklärung sehe, dass "eins abrufen" speichereffizienter ist als "alle abrufen", weil nicht alle Ergebnisse auf einmal gelesen werden "?

Fazit

Der Text ist lang, also aus der Schlussfolgerung.

1. Python Cursor hat ungefähr drei Arten von Implementierungen.

• Serverseitiger Cursor: Nur psycopg2 wurde korrekt implementiert. ** Entspricht SQL CURSOR ** Operationen und Referenzen (FETCH, MOVE, CURRENT OF) sind möglich. fetchone und fetchall führen SQL FETCH NEXT bzw. FETCH ALL aus.
• Clientseitiger Cursor (mit Puffer): Alle Abfrageergebnisse werden gleichzeitig auf der Clientseite geladen. Da Fetch One und Fetch Many ** Emulation ** sind, gibt es auf der Clientseite keinen Unterschied in der Speichereffizienz und beide sind schlecht. Die aktuelle Zeile kann nicht von anderen Cursorn referenziert werden. ** Leider die häufigste Implementierung **.
• Clientseitiger Cursor (kein Puffer): Beispielsweise entspricht der SSCursor von MySQLdb dieser Implementierung. Das SSCursor-Handbuch sagt "serverseitiger Cursor", aber es bedeutet nicht SQL CURSOR. Tatsächlich ist es eine empörende Implementierung, die den Socket jedes Mal blockiert, wenn eine Zeile gelesen wird, um Speicher auf der Clientseite zu sparen, und die nächste SQL-Anweisung wird blockiert, bis sie vollständig gelesen ist. ** Schlechteste Implementierung **. (Hinweis: Ich weiß, dass es einen Blog namens SSCursor und Stackoverflows Antwort zum Lesen aus großen Tabellen gibt. Ich kann das nicht leugnen, aber es hat schwerwiegende Nachteile, wie im experimentellen Abschnitt gezeigt. .)

2. In psycopg2 für PostgreSQL ist das Deklarieren eines ** benannten Cursors ** serverseitig. Es wäre clientseitig ohne Namen.
3. MySQLdb für MySQL ist nur clientseitig.

Implementierung des Python-Cursors

Korrespondenztabelle von SQL CURSOR und Python-Cursor

Die Beziehung zwischen dem serverseitigen Cursor ("SSC") und der Python-Methode ist wie folgt: Sofern nicht anders angegeben, kann es nicht mit dem clientseitigen Cursor ("CSC") verwendet werden.

Umfrage

Ich habe SQL CURSOR und Python Cursor untersucht.

SQL CURSOR

Der Cursor kann die Ergebnisse von Abfragen wie SELECT-Anweisungen einzeln abrufen oder zur vorherigen oder nächsten Zeile wechseln. Sie können auch aus anderen SQL-Anweisungen auf die aktuelle Zeile verweisen. Die Details werden weggelassen, da der Zweck nicht darin besteht, die Grammatik zu erklären.

Deklaration des Cursors

Der Cursor wird so deklariert.

DECLARE Cursorname CURSOR FOR SELECT Anweisung

Holen Sie sich Reihe

Führen Sie eine Anweisung ähnlich der folgenden aus, um die nächste Zeile des Cursors zu erhalten: Die normale Richtung ist "NEXT" oder "FORWARD", um die Reihe zu erhalten, während Sie sich vorwärts bewegen. Es ist auch möglich, in der Mitte zurückzukehren. In diesem Fall geben Sie "PRIOR" oder "BACKWARD" an. Es ist auch möglich, gleichzeitig zum Anfang "ERST" oder zum Ende "LETZT" zu wechseln.

FETCH-Richtung VON Cursorname

Bewegen Sie den Cursor

Verwenden Sie MOVE, wenn Sie sich nur bewegen möchten, ohne eine Zeile zu erhalten. Die Richtungsspezifikation ist dieselbe wie bei FETCH.

Verschieben Sie die Richtung vom Cursornamen

Verweis auf Cursorzeile

Sie können auch auf die Cursorzeile anderer Anweisungen verweisen. In diesem Fall können Sie problemlos andere Vorgänge ausführen, während Sie das Ergebnis von SELECT verwenden. Verwenden Sie beispielsweise "CURRENT OF" wie folgt, um eine Zeile anzuzeigen, in der gerade ein Cursor abgerufen wird:

UPDATE names SET name='tomochi'WO AKTUELLER Cursorname

Python cursor

cursor wird durch Aufrufen der .cursor () -Methode des Verbindungsobjekts erstellt. Transaktionen funktionieren für Verbindungsobjekte. Wenn Sie also mehrere Cursor aus einem Verbindungsobjekt erstellen, werden diese innerhalb einer Transaktion ausgeführt.

Cursormethode (Teil)

Es werden nur die Methoden behandelt, die wahrscheinlich mit SQL CURSOR zusammenhängen.

• fetchone () Ruft eine Zeile aus der aktuellen Zeile der Abfrage ab und wechselt zur nächsten Zeile. Entspricht "FETCH NEXT".
• fetchmany (n) Ruft n Zeilen aus der aktuellen Zeile der Abfrage ab und wechselt zur nächsten Zeile. Entspricht "FETCH FORWARD n".
• fetchall () Ruft alle verbleibenden Zeilen aus der aktuellen Zeile der Abfrage ab und wechselt zur nächsten Zeile. Entspricht "FETCH ALL".
• scroll (Wert, Modus) Wenn der Modus relativ ist, bewegen Sie sich um die durch den Wert angegebene Variation. Wenn der Modus absolut ist, ist Wert = 0 die erste Zeile und Wert = -1 die letzte Zeile. Entspricht "MOVE".

Experiment

Experimentieren Sie, um festzustellen, ob der Python-Cursor SQL CURSOR deklariert und verwendet. Die Methode ist Aktivieren Sie die Anweisungsprotokollierung in PostgreSQL bzw. MySQL, führen Sie normale Cursor und benannte Cursor aus und vergleichen Sie die tatsächlich ausgegebenen SQL-Anweisungen.

PostgreSQL + psycopg2 Starten Sie mit log_statement = 'all' in postgresql.conf neu. Erstellen Sie eine entsprechende PostgreSQL-Datenbank und legen Sie die Tabelle und die Testdaten darin ab.

names.sql

CREATE TABLE names (
  name varchar(100)
);

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

Namenloser Cursor

psql1.py

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

Das Ergebnis lautet wie folgt: Auf der Serverseite gibt es keine Deklaration des Cursors, und alle Zeilen werden abgerufen.

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

Benannter Cursor

Versuchen Sie als nächstes SELECT mit dem benannten Cursor auf die gleiche Weise. Der einzige Unterschied zu dem oben genannten ist der Name von cursor ().

psql2.py

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

Das Ergebnis zeigt, dass der Cursor auf der Serverseite deklariert ist und FETCH ausgeführt wird.

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

MySQL + MySQLdb Fügen Sie die folgende Zeile zum Abschnitt [mysqld] in my.cnf hinzu und starten Sie neu.

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

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

Das Ergebnis ist erwartungsgemäß ein einfaches cursorloses SELECT.

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

SSCursor Als nächstes experimentieren wir mit der Cursor-Klasse, die im MySQLdb-Handbuch als "serverseitiger Cursor" beschrieben wird. Es wird aktiviert, indem cursorclass = MySQLdb.cursors.SSCursor an connect () übergeben wird.

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

Das Ergebnis ist das gleiche wie bei einem normalen Cursor. Es wurde kein serverseitiger Cursor deklariert.

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

Wenn ich die Quelle lese, ist es eine überraschende Implementierung, die blockiert, indem keine Daten aus dem Socket gelesen werden. Wäre es nicht möglich, andere Anweisungen auszuführen, wenn sie auf Verbindungsebene blockiert wären? Die Namenstabelle enthält zwei Zeilen. Was passiert jedoch, wenn ich die erste Zeile erhalte und sie dann separat auswähle?

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

Die Hinrichtung wurde abgelehnt.

$ 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

"Sie können diesen Befehl jetzt nicht ausführen". Ich verstehe das MySQL Connector (Client Library) Handbuch nicht vollständig, aber irgendwie die Ergebnisse Es scheint, dass die nächste Anweisung nicht im selben Thread ausgeführt werden kann, da sie nicht vollständig gelesen wurde. Es befindet sich in einem Zustand, in dem es gelesen, aber nicht aus anderen Threads geschrieben werden kann. SSCursor ist nutzlos.

Verweis auf den serverseitigen Cursor

Verwenden Sie in PostgreSQL einen serverseitigen Cursor und einen regulären Cursor. Navigieren Sie zum serverseitigen Cursor. Ein sehr einfaches Beispiel für die Aktualisierung von 'kenji' in der Namenstabelle auf 'tomochi'.

psycopg2_named_cursor_example.py

import psycopg2

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

cu1 = conn.cursor('foo')   #Serverseitiger Cursor 1
cu2 = conn.cursor()  #Normaler Cursor 2

#Sie müssen die Leitung nicht sperren, aber irgendwie.
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 der aktuellen Zeile von Cursor 1
cu2.close()

conn.commit()
conn.close()

Das Ablaufverfolgungsprotokoll lautet wie folgt. Das SQL wird wie erwartet ausgeführt.

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 der Namenstabelle wurde auf 'tomochi' aktualisiert.

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

Bewegen Sie den Cursor

Ich habe auch das scroll () -Experiment ausprobiert, aber weggelassen, da es keinen Unterschied zu SQL gab, der beachtet werden sollte.