pymssql - execute loads all results into memory!
mail at timgolden.me.uk
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()
>> 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
conn = [
db = pyodbc.connect (";".join (conn))
q = db.cursor ()
q.execute ("SELECT * FROM revenue") # 24 million rows
More information about the Python-list