pymssql - execute loads all results into memory!

Tim Golden mail at
Mon Oct 20 16:38:57 CEST 2008

Eric Wertman wrote:
>> I am trying to use pymssql, and have an issue where by the execute
>> (not the fetch) is appearing to load all records into memory.
>> if I execute
>> con = pymssql.connect(...)
>> cur = con.cursor()
>> cur.execute(sql)
>> rec  = cur.fetchone()
>> if I put in a query which returns a lot of records into "sql" then the
>> execute never returns, pythons memory usage slowly ballons till the
>> machine cant give anymore. If I put a sql query returning only few
>> rows, then it works fine.
>> So I am not sure why an execute would feel the need to load all rows,
>> but its seriously crippling. Does anyone know if this is a bug or
>> something I can "turn off"
> I ran into this myself.  After some digging I discovered that what you
> are after is a server-side cursor that isn't implemented yet in
> pymssql.  There is one in MySQLdb, but it's not the default behavior.
> Regardless of your usage (fetchone vs fetchmany), the result set is
> held client side.  AFAIK the only workaround is to keep your result
> set small (enough).  If you use fetchmany and iterate over it
> directly, it may keep your memory usage down,  I can't remember if
> that worked.  I definitely tried making a generator with it, that did
> not help.

... or just switch to pyodbc, for example, which behaves
perfectly well with this snippet against a table of >24 million

import pyodbc

conn = [
  "Driver={SQL Server}",
db = pyodbc.connect (";".join (conn))
q = db.cursor ()
q.execute ("SELECT * FROM revenue") # 24 million rows
q.fetchone ()
q.close ()



More information about the Python-list mailing list