Как получить строку за строкой MySQL ResultSet в python

MySQL ResultSets по умолчанию полностью извлекаются с сервера, прежде чем любая работа может быть выполнена. В случае огромных наборов результатов это становится непригодным. Я хотел бы вместо этого фактически извлекать строки один за другим с сервера.

В Java, следуя инструкциям здесь (в разделе "ResultSet" ), я создаю инструкцию следующим образом:

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

Это хорошо работает на Java. Мой вопрос: есть ли способ сделать то же самое в python?

Одна вещь, которую я пытался, - это ограничить запрос 1000 строк за раз, например:

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

Однако, похоже, он становится медленнее, чем выше start_row.

И нет, использование fetchone() вместо fetchall() ничего не меняет.

Разъяснение:

Наивный код, который я использую для воспроизведения этой проблемы, выглядит следующим образом:

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

В таблице из 700 тысяч строк этот код выполняется быстро. Но на таблице в 9 000 000 строк он печатает "Выполнение запроса", а затем долгое время зависает. Вот почему это не имеет значения, если я использую fetchone() или fetchall().

Ответ 1

Я думаю, вам нужно подключиться к передаче cursorclass = MySQLdb.cursors.SSCursor:

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

Курсор по умолчанию извлекает все данные одновременно, даже если вы не используете fetchall.

Изменить: SSCursor или любой другой класс курсора, который поддерживает наборы результатов на стороне сервера - проверьте документы модуля на MySQLdb.cursors.

Ответ 2

Решение limit/offset выполняется в квадратичное время, потому что mysql должен повторно проверять строки, чтобы найти смещение. Как вы подозревали, курсор по умолчанию сохраняет весь набор результатов на клиенте, который может потреблять много памяти.

Вместо этого вы можете использовать курсор на стороне сервера, который поддерживает выполнение запроса и при необходимости извлекает результаты. Класс курсора может быть настроен путем предоставления по умолчанию самому вызову соединения или путем предоставления класса методу курсора каждый раз.

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

Но это не вся история. В дополнение к сохранению результата mysql, по умолчанию клиентский курсор на самом деле выбирает каждую строку независимо. Такое поведение недокументировано и очень неудачно. Это означает, что все объекты python создаются для всех строк, которые потребляют гораздо больше памяти, чем исходный результат mysql.

В большинстве случаев результат, хранящийся на клиенте, завернутый в качестве итератора, дает максимальную скорость при разумном использовании памяти. Но вам придется катиться самостоятельно, если вы этого хотите.

Ответ 3

Вы пробовали эту версию fetchone? Или что-то другое?

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

Также вы попробовали это?

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

Не все драйверы поддерживают их, поэтому вы можете получить ошибки или найти их слишком медленно.

Изменить.

Когда он зависает при выполнении, вы ожидаете базы данных. Это не строка-строка Python; что вещь MySQL.

MySQL предпочитает извлекать все строки как часть собственного управления кешем. Это отключается, предоставляя значение fetch_size для Integer.MIN_VALUE (-2147483648L).

Вопрос в том, какая часть PAPID DBAPI станет эквивалентом JDBC fetch_size?

Я думаю, что это может быть атрибут arraysize курсора. Попробуйте

cursor.arraysize=-2**31

И посмотрите, заставляет ли MySQL заставлять поток результирующего набора вместо его кеширования.

Ответ 4

Попробуйте использовать 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']

Ответ 5

Я нашел лучшие результаты, немного смешав некоторые из других ответов.

Это включало настройку cursorclass=MySQLdb.cursors.SSDictCursor (для MySQLdb) или pymysql.cursors.SSDictCursor (для PyMySQL) как часть настроек подключения. Это позволит серверу выполнить запрос/результаты ( "SS" означает серверную сторону, в отличие от курсора по умолчанию, который приносит результат на стороне клиента) и построить словарь из каждой строки (например, {'id': 1, name ':' Cookie Monster '}).

Затем, чтобы прокручивать строки, в обоих Python 2.7 и 3.4 был вызван бесконечный цикл, вызванный while rows is not None, потому что даже при вызове cur.fetchmany(size=10000) и результатов не осталось, метод возвратил пустой список ([]) вместо None.

Фактический пример:

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