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!


-

Popular posts from this blog

debugging - Reference - What does this error mean in PHP? -

what this? this number of answers warnings, errors , notices might encounter while programming php , have no clue how fix. community wiki, invited participate in adding , maintaining list. why this? questions "headers sent" or "calling member of non-object" pop on stack overflow. root cause of questions same. answers questions typically repeat them , show op line change in his/her particular case. these answers not add value site because apply op's particular code. other users having same error can not read solution out of because localized. sad, because once understood root cause, fixing error trivial. hence, list tries explain solution in general way apply. what should here? if question has been marked duplicate of this, please find error message below , apply fix code. answers contain further links investigate in case shouldn't clear general answer alone. if want contribute, please add "favorite" error message, warning or notic...
Read more

c++ - Why doesn't unordered_set provide an array access operator -

i'm curious why stl container unordered_set , has constant time complexity random access on average, not provide method accessing elements distance first element in container. example: t& unordered_set::operator[](size_t index) { return *(begin() + index); } accessing element "by distance" implies there meaningful way measure distance. trouble std::unordered_set is is, well, unordered . hence, there no meaningful way of explaining "some distance beginning" in non-arbitrary way. if want access distance, copy data vector: std::vector tmp(unordered.begin(), unordered.end());
Read more