pymssql - execute loads all results into memory!

[Aspersieman]

On Tue, 21 Oct 2008 10:14:56 +0200, ChaosKCW <da.martian at gmail.com> wrote:

> On Oct 20, 3:38 pm, Tim Golden <m... at timgolden.me.uk> wrote:
>> 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
>> rows:
>>
>> <code>
>> import pyodbc
>>
>> conn = [
>>   "Driver={SQL Server}",
>>   "Server=SVR17",
>>   "Database=TDI",
>>   "TrustedConnection=Yes"
>> ]
>> db = pyodbc.connect (";".join (conn))
>> q = db.cursor ()
>> q.execute ("SELECT * FROM revenue") # 24 million rows
>> q.fetchone ()
>> q.close ()
>>
>> </code>
>>
>> TJG
>
> Thanks for the responses, I am astounded any db api tool doesnt
> support cursors! pymssql is mostly useless, I will switch to an odbc
> interface.
> --
> http://mail.python.org/mailman/listinfo/python-list

Yes this is true, unfortunately...

However, pyODBC doesn't support return variables(parameters) in stored  
procedures (at least with MS SQL). pymssql is the only db api for python  
that I've found that can reliably do this. I've tried adodbapi, pyodbc and  
one or two others (can't think of the names now... :-/).

Regards

Nicol

-- 
Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo