Comment obtenir un ResultSet MySQL ligne par ligne en python

Question

Par défaut, les jeux de résultats MySQL sont entièrement récupérés sur le serveur avant toute intervention. Dans le cas de jeux de résultats volumineux, cela devient inutilisable. Je voudrais plutôt récupérer les lignes une par une sur le serveur.

En Java, en suivant les instructions ici (sous "ResultSet"), je crée une déclaration comme celle-ci :

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Cela fonctionne très bien en Java. Ma question est la suivante : existe-t-il un moyen de faire la même chose en Python ?

J'ai essayé de limiter la requête à 1000 lignes à la fois, comme ceci :

start_row = 0
while True:
    cursor = conn.cursor()
    cursor.execute("SELECT item FROM items LIMIT %d,1000" % start_row)
    rows = cursor.fetchall()
    if not rows:
        break
    start_row += 1000
    # Do something with rows...

Toutefois, plus la valeur de start_row est élevée, plus le processus semble lent.

Et non, utiliser fetchone() au lieu de fetchall() ne change rien.

Clarification :

Le code naïf que j'utilise pour reproduire ce problème ressemble à ceci :

import MySQLdb

conn = MySQLdb.connect(user="user", passwd="password", db="mydb")
cur = conn.cursor()
print "Executing query"
cur.execute("SELECT * FROM bigtable");

print "Starting loop"
row = cur.fetchone()
while row is not None:
    print ", ".join([str(c) for c in row])
    row = cur.fetchone()

cur.close()
conn.close()

Sur une table de ~700 000 lignes, ce code s'exécute rapidement. Mais sur une table de ~9 000 000 de lignes, il affiche "Executing Query" et se bloque ensuite pendant un long moment. C'est pourquoi cela ne fait aucune différence si j'utilise fetchone() ou fetchall() .

Answer 1

Je pense que vous devez vous connecter en passant cursorclass = MySQLdb.cursors.SSCursor :

 MySQLdb.connect(user="user", 
                 passwd="password",
                 db="mydb",
                 cursorclass = MySQLdb.cursors.SSCursor
                )

Le curseur par défaut récupère toutes les données en une seule fois, même si vous n'utilisez pas l'option fetchall .

Edit : SSCursor ou toute autre classe de curseur qui prend en charge les jeux de résultats côté serveur - consultez la documentation du module à l'adresse suivante MySQLdb.cursors .

Answer 2

La solution limite/décalage s'exécute en un temps quadratique car mysql doit rescanner les lignes pour trouver le décalage. Comme vous vous en doutez, le curseur par défaut stocke l'ensemble des résultats sur le client, ce qui peut consommer beaucoup de mémoire.

Au lieu de cela, vous pouvez utiliser un curseur côté serveur, qui maintient la requête en cours et récupère les résultats si nécessaire. La classe du curseur peut être personnalisée en fournissant une valeur par défaut à l'appel de connexion lui-même, ou en fournissant une classe à la méthode du curseur à chaque fois.

from MySQLdb import cursors
cursor = conn.cursor(cursors.SSCursor)

Mais ce n'est pas toute l'histoire. En plus de stocker le résultat mysql, le curseur côté client par défaut récupère en fait chaque ligne sans tenir compte des autres. Ce comportement est non documenté et très malheureux. Cela signifie que des objets python complets sont créés pour toutes les lignes, ce qui consomme beaucoup plus de mémoire que le résultat mysql original.

Dans la plupart des cas, un résultat stocké sur le client enveloppé sous forme d'itérateur offre la meilleure vitesse avec une utilisation raisonnable de la mémoire. Mais vous devrez mettre en place votre propre système si vous le souhaitez.

Answer 3

Avez-vous essayé cette version de fetchone ? Ou quelque chose de différent ?

row = cursor.fetchone() 
while row is not None:
    # process
    row = cursor.fetchone()

Aussi, avez-vous essayé ceci ?

 row = cursor.fetchmany(size=1)
 while row is not None:
     # process
     row = cursor.fetchmany( size=1 )

Tous les pilotes ne les prennent pas en charge, il se peut donc que vous ayez obtenu des erreurs ou que vous les trouviez trop lents.

---

Edit.

Quand ça se bloque à l'exécution, vous attendez la base de données. Ce n'est pas un truc Python ligne par ligne, c'est un truc MySQL.

MySQL préfère récupérer toutes les lignes dans le cadre de sa propre gestion du cache. Ceci est désactivé en fournissant un fetch_size de Integer.MIN_VALUE (-2147483648L).

La question est de savoir quelle partie de la DBAPI Python devient l'équivalent du fetch_size de JDBC.

Je pense que cela pourrait être l'attribut arraysize du curseur. Essayez

cursor.arraysize=-2**31

Et voyez si cela force MySQL à diffuser le jeu de résultats au lieu de le mettre en cache.

Answer 4

Essayez d'utiliser MySQLdb.cursors.SSDictCursor

con = MySQLdb.connect(host=host,
                  user=user,
                  passwd=pwd,
                  charset=charset,
                  port=port,
                  cursorclass=MySQLdb.cursors.SSDictCursor);
cur = con.cursor()
cur.execute("select f1, f2 from table")
for row in cur:
    print row['f1'], row['f2']