Re: pymssql - execute loads all results into memory!

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
.

Relevant Pages

  • Re: When and where do I do Set m_FormVar = Nothing?
    ... "Differences Among Form's Initialize, Load, and Activate Events" ... MS has stated that "Forms get loaded into memory" ... Life Cycle of Visual Basic Forms ... can execute without forcing the form to load are Sub, ...
    (microsoft.public.vb.general.discussion)
  • Re: When and where do I do Set m_FormVar = Nothing?
    ... "Differences Among Form's Initialize, Load, and Activate Events" ... MS has stated that "Forms get loaded into memory" ... Life Cycle of Visual Basic Forms ... can execute without forcing the form to load are Sub, ...
    (microsoft.public.vb.general.discussion)
  • Re: pymssql - execute loads all results into memory!
    ... is appearing to load all records into memory. ... execute never returns, pythons memory usage slowly ballons till the ... So I am not sure why an execute would feel the need to load all rows, ...
    (comp.lang.python)
  • Re: ADODB Command memory leak
    ... > I'm tracking down a memory leak and have narrowed it down to the single ... > of my code that calls the Execute() method of the ADODB Command object. ... If I comment out the Executecall, memory usage does not ...
    (microsoft.public.data.ado)
  • Re: only load userform if range empty
    ... and the form will then be in memory but not ... If you use Show and the form is in memory, it will execute any code in the ... I presume you mean Show the UserForm, but if you meant Load then use ...
    (microsoft.public.excel.programming)
Loading