mysql connector - python script hangs when calling cursor.fetchall() with large data set -

i have query returns on 125k rows.

the goal write script iterates through rows, , each, populate second table data processed result of query.

to develop script, created duplicate database small subset of data (4126 rows)

on small database, following code works:

import os import sys import random  import mysql.connector  cnx = mysql.connector.connect(user='dbuser', password='thepassword',                           host='127.0.0.1',                           database='db') cnx_out = mysql.connector.connect(user='dbuser', password='thepassword',                           host='127.0.0.1',                           database='db')  ins_curs = cnx_out.cursor()  curs = cnx.cursor(dictionary=true) #curs = cnx.cursor(dictionary=true,buffered=true) #fail  open('sql\\getrawdata.sql') fh:     sql = fh.read()  curs.execute(sql, params=none, multi=false) result = curs.fetchall()  #<=== script stops @ point print len(result) #<=== line never executes  print curs.column_names  curs.close() cnx.close() cnx_out.close() sys.exit() 

the line curs.execute(sql, params=none, multi=false) succeeds on both large , small databases. if use curs.fetchone() in loop, can read records.

if alter line:

curs = cnx.cursor(dictionary=true) 

to read:

curs = cnx.cursor(dictionary=true,buffered=true) 

the script hangs @ curs.execute(sql, params=none, multi=false).

i can find no documentation on limits fetchall(), nor can find way increase buffer size, , no way tell how large buffer need.

there no exceptions raised.

how can resolve this?

i having same issue, first on query returned ~70k rows , on 1 returned around 2k rows (and me ram not limiting factor). switched using mysql.connector (i.e. mysql-connector-python package) mysqldb (i.e. mysql-python package) , able fetchall() on large queries no problem. both packages seem follow python db api, me mysqldb drop-in replacement mysql.connector, no code changes necessary beyond line sets connection. ymmv if you're leveraging specific mysql.connector.

pragmatically speaking, if don't have specific reason using mysql.connector solution switch package works better!