[Tutor] Re: memory problem (II part)

[Danny Yoo]

> I am perplexed, what product are you using and how heavily are you
> pounding on it?
>
> Why are you using a cursor, which is a known memory hog?

We should clarify: the DB API specifies that we access the database
through a Python Cursor:

    http://python.org/peps/pep-0249.html

So if we follow the API, there's no way we can get around "cursors".
*grin*

I think, though, that we're just confusing multiple related uses of the
word "cursor".

> I was forbidden using cursors (as were the others in our group), because
> it impacts performance so extremely under Sybase.  Oracle seems to have
> relied upon cursors much longer than competing products due to its tie
> in with the user interface (and packaged tools).  However, finally they

Your use of "cursor" here may be different from the "cursor" that the DB
API is talking about.



As a concrete example, Pysqlite actually doesn't support real database
cursors --- they are simulated by storing all the results in a temporary
list, and the "cursor" object there is just iterating across that
temporary list.

Ah!  Ok, I think I'm understanding what's happening.  Pysqlite's
connections themselves hold onto cursors in a list, so that when a
connection closes, all its associated cursors can be collected.

But as a result, large queries may end up in memory while we're holding
onto the connection, since those cursors may not necessarily be freed till
much later.

I see that the pysqlite code is using weak references to keep track of all
the cursors associated with a connection:

    http://www.python.org/doc/lib/module-weakref.html

so the cursors should get reclaimed eventually when the garbage collector
kicks in.


Guillermo notes that:

> The only thing that makes the RAM go down is to close the database. I'll
> try to live with that (closing and opening the database for each
> command...),

Instead of closing the connections, what happens if you call each cursor's
close() method after you do a fetch?  Doing a close() on the connection
may be working because it calls close() on all open cursors.  Let's verify
that its the cursors that are holding onto database results in memory.


Hope this helps!