mysql - Python: How to use a generator to avoid sql memory issue -

i have following method access mysql database , query executed in server don't have access change on regarding increasing memory. new generators , started read more , thought convert use generator.

def getunames(self):     globaluserquery = ur'''select gu_name globaluser gu_locked = 0'''     global_user_list = []     try:         self.gdbcursor.execute(globaluserquery)         rows = self.gdbcursor.fetchall()         row in rows:             uname = unicode(row['gu_name'], 'utf-8')             global_user_list.append(uname)         return global_user_list     except exception, e:         traceback.print_exc() 

and use code follow:

for user_name in getunames(): ... 

this error getting server side:

^gout of memory (needed 725528 bytes) traceback (most recent call last): ... packages/mysqldb/connections.py", line 36, in defaulterrorhandler     raise errorclass, errorvalue operationalerror: (2008, 'mysql client ran out of memory') 

how should using generator avoid this:

while true:    self.gdbcursor.execute(globaluserquery)    row = self.gdbcursor.fetchone()    if row none: break    yield row 

not sure if above right way go since expecting list result of database method. think great chunk query , return list , once done generator give next set long query return results.

with mysqldb, default cursor loads entire result set python list when call cursor.execute(..) made. large query may cause memoryerror whether or not use generator.

instead, use sscursor or ssdictcursor. these keep result set on server side, , allow interate through items in result set on client side:

import mysqldb   import mysqldb.cursors cursors  def getunames(self):     # may of course want define `self.gdbcursor` somewhere else...     conn = mysqldb.connect(..., cursorclass=cursors.sscursor)     #                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^     #                       set cursor class sscursor here     self.gdbcursor = conn.cursor()      globaluserquery = ur'''select gu_name globaluser gu_locked = 0'''     try:         self.gdbcursor.execute(globaluserquery)         row in self.gdbcursor:             uname = unicode(row['gu_name'], 'utf-8')             yield uname     except exception, e:         traceback.print_exc() 

there isn't documentation on difference between default cursor , sscursor. best source know docstrings of cursor mixin classes themselves:

the default cursor uses cursorstoreresultmixin:

in [2]: import mysqldb.cursors cursors in [8]: print(cursors.cursorstoreresultmixin.__doc__) mixin class causes entire result set     stored on client side, i.e. uses mysql_store_result(). if     result set can large, consider adding limit clause     query, or using cursoruseresultmixin instead. 

and sscursor uses cursoruseresultmixin:

in [9]: print(cursors.cursoruseresultmixin.__doc__) mixin class causes result set stored     in server , sent row-by-row client side, i.e. uses     mysql_use_result(). must retrieve entire result set ,     close() cursor before additional queries can peformed on     connection. 

---

since changed getunames generator, used this:

for row in self.getunames():     ...