Как получить MySQL ResultSet построчно в Python
Наборы результатов MySQL по умолчанию полностью извлекаются с сервера до того, как можно будет выполнить какую-либо работу. В случае огромных наборов результатов это становится непригодным для использования. Вместо этого я хотел бы фактически получать строки с сервера одну за другой.
В 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 000 строк этот код выполняется быстро. Но для таблицы с ~ 9 000 000 строк он печатает «Выполнение запроса», а затем надолго зависает. Поэтому без разницы, буду я использовать fetchone() или fetchall().
Ответы 5
Вы пробовали эту версию 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).
Вопрос в том, какая часть Python DBAPI становится эквивалентом JDBC fetch_size?
Я думаю, это может быть атрибут arrayysize курсора. Пытаться
cursor.arraysize=-2**31
И посмотрите, заставит ли это MySQL передавать результирующий набор в потоковом режиме вместо его кеширования.
Он зависает на cursor.execute (), поэтому не имеет значения, использую ли я fetchone или fetchall.
Думаю надо подключать попутно cursorclass = MySQLdb.cursors.SSCursor:
MySQLdb.connect(user = "user",
passwd = "password",
db = "mydb",
cursorclass = MySQLdb.cursors.SSCursor
)
Курсор по умолчанию выбирает все данные сразу, даже если вы не используете fetchall.
Обновлено: SSCursor или любой другой класс курсора, который поддерживает наборы результатов на стороне сервера - проверьте документацию модуля на MySQLdb.cursors.
Обратите внимание, что вам явно нужно сделать import MySQBdb.cursor. Иначе у меня ничего не вышло.
Это будет import MySQLdb.cursors;)
Я обнаружил огромное сокращение использования памяти с помощью cursors.SSDictCursor - поэтому, если вы хотите, чтобы результаты возвращались в словаре для каждой строки, используйте это.
Следует отметить, что SSCursor не позволяет вам отправлять дальнейшие запросы, пока он не будет должным образом закрыт. В противном случае вы получите сообщение об ошибке «Команды не синхронизированы; вы не можете запустить эту команду сейчас». Не уверен, есть ли способ обойти это (мне нужно выполнять небольшие запросы при анализе большого результата).
@mak Вы сможете выполнить второй запрос, используя SSCursor (или SSDictCursor), как только вы получите весь набор результатов из первого запроса. Это применимо только к операторам запроса; вы должны иметь возможность выполнять несколько операторов определения данных или манипуляций (CREATE, ALTER, INSERT и т. д.) без промежуточных функций fetchall () или fetchone (), поскольку они возвращают ответ, но не получают набор результатов.
Решение с ограничением / смещением выполняется за квадратичное время, потому что mysql должен повторно сканировать строки, чтобы найти смещение. Как вы и подозревали, курсор по умолчанию сохраняет весь набор результатов на клиенте, что может потреблять много памяти.
Вместо этого вы можете использовать курсор на стороне сервера, который поддерживает выполнение запроса и при необходимости извлекает результаты. Класс курсора можно настроить, задав значение по умолчанию самому вызову соединения или каждый раз предоставляя класс методу курсора.
from MySQLdb import cursors
cursor = conn.cursor(cursors.SSCursor)
Но это еще не все. В дополнение к сохранению результата mysql, клиентский курсор по умолчанию фактически выбирает каждую строку независимо от того. Такое поведение недокументировано и очень прискорбно. Это означает, что для всех строк создаются полные объекты python, что потребляет гораздо больше памяти, чем исходный результат mysql.
В большинстве случаев результат, хранящийся на клиенте в оболочке как итератор, дает наилучшую скорость при разумном использовании памяти. Но вам придется свернуть свою собственную, если вы этого хотите.
Попробуйте использовать 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']
Я обнаружил, что лучшие результаты немного смешиваются с некоторыми другими ответами.
Это включало настройку 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()
Насколько портативен SSCursor? Пока я вижу только страницы об этом, связанные с mysql, а не с DB-API.