consulta - Cómo obtener un ResultSet MySQL fila por fila en python

consulta mysql python

Question

Los ResultSets de MySQL se recuperan por completo del servidor por completo antes de poder realizar cualquier trabajo. En casos de grandes conjuntos de resultados, esto se vuelve inutilizable. En su lugar, me gustaría recuperar las filas una por una desde el servidor.

En Java, siguiendo las instrucciones aquí (bajo "ResultSet"), creo una declaración como esta:

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

Esto funciona muy bien en Java. Mi pregunta es: ¿hay alguna manera de hacer lo mismo en Python?

Una cosa que probé es limitar la consulta a 1000 filas a la vez, como esta:

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...

Sin embargo, esto parece hacerse más lento cuanto mayor sea start_row.

Y no, usar fetchone() lugar de fetchall() no cambia nada.

Aclaración:

El código ingenuo que uso para reproducir este problema se ve así:

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

En una tabla de ~ 700,000 filas, este código se ejecuta rápidamente. Pero en una tabla de ~ 9,000,000 filas imprime "Executing Query" y luego se cuelga durante mucho tiempo. Es por eso que no fetchone() si utilizo fetchone() o fetchall() .

Answer 1

¿Has probado esta versión de fetchone? O algo diferente?

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

Además, ¿probaste esto?

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

No todos los controladores son compatibles con estos, por lo que es posible que haya recibido errores o los encuentre demasiado lentos.

Editar.

Cuando se bloquea en ejecutar, estás esperando la base de datos. Eso no es una cosa de Python fila por fila; eso es algo de MySQL

MySQL prefiere buscar todas las filas como parte de su propia gestión de caché. Esto se desactiva al proporcionar un valor fetch_size de Integer.MIN_VALUE (-2147483648L).

La pregunta es, ¿qué parte de la DBAPI de Python se convierte en el equivalente de JDBC fetch_size?

Creo que podría ser el atributo arraysize del cursor. Tratar

cursor.arraysize=-2**31

Y vea si eso obliga a MySQL a transmitir el conjunto de resultados en lugar de almacenarlo en caché.

Answer 2

Creo que tienes que conectar pasando cursorclass = MySQLdb.cursors.SSCursor :

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

El cursor predeterminado recupera todos los datos a la vez, incluso si no usa fetchall .

Edición: SSCursor o cualquier otra clase de cursor que admita los resultados del lado del servidor - verifique los documentos del módulo en MySQLdb.cursors .

Answer 3

La solución de límite / desplazamiento se ejecuta en tiempo cuadrático porque mysql tiene que volver a escanear las filas para encontrar el desplazamiento. Como sospechaba, el cursor predeterminado almacena todo el conjunto de resultados en el cliente, lo que puede consumir mucha memoria.

En su lugar, puede usar un cursor del lado del servidor, que mantiene la consulta ejecutándose y obtiene resultados según sea necesario. La clase de cursor se puede personalizar suministrando un valor predeterminado a la llamada de conexión en sí, o suministrando una clase al método del cursor cada vez.

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

Pero esa no es toda la historia. Además de almacenar el resultado de mysql, el cursor predeterminado del lado del cliente en realidad busca todas las filas independientemente. Este comportamiento no está documentado y es muy desafortunado. Significa que se crean objetos completos de python para todas las filas, lo que consume mucha más memoria que el resultado original de mysql.

En la mayoría de los casos, un resultado almacenado en el cliente envuelto como un iterador produciría la mejor velocidad con un uso de memoria razonable. Pero tendrás que hacer tu propia bola si quieres eso.

Answer 4

Intenta usar 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'']

Answer 5

Encontré los mejores resultados mezclando un poco de algunas de las otras respuestas.

Esto incluía establecer cursorclass=MySQLdb.cursors.SSDictCursor (para MySQLdb) o pymysql.cursors.SSDictCursor (para PyMySQL) como parte de la configuración de conexión. Esto permitirá que el servidor retenga la consulta / resultados (la "SS" representa el lado del servidor en oposición al cursor predeterminado que trae los resultados del lado del cliente) y construye un diccionario de cada fila (ej. {''Id'': 1, '' nombre '':'' Cookie Monster ''}).

A continuación, para recorrer las filas, hubo un bucle infinito en Python 2.7 y 3.4 causado por while rows is not None porque incluso cuando se cur.fetchmany(size=10000) y no quedaban resultados, el método devolvió un valor vacío. list ( [] ) en lugar de None.

Ejemplo real:

query = """SELECT * FROM my_table"""
conn = pymysql.connect(host=MYSQL_CREDENTIALS[''host''], user=MYSQL_CREDENTIALS[''user''],
                          passwd=MYSQL_CREDENTIALS[''passwd''], charset=''utf8'', cursorclass = pymysql.cursors.SSDictCursor)
cur = conn.cursor()
results = cur.execute(query)
rows = cur.fetchmany(size=100)
while rows:
    for row in rows: 
        process(row)
    rows = cur.fetchmany(size=100)
cur.close()
conn.close()