pymssql - execute loads all results into memory!

[ChaosKCW]

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.