3

I saw in the docs that you need to use server-side ("named") cursors to avoid loading all results into memory at once. But then I found that server-side cursors aren't reusable. You can only do a single .execute on them. (Otherwise you get ProgrammingError: can't call .execute() on named cursors more than once.)

Is there any way to get a cursor that I can re-use, but that also will not load all the data into memory at once?

I'm open to using a different Postgres driver if it will make things easier and more performant. I looked at PyGreSQL but it seems noticeably slower than a Postgres named cursor.

BrenBarn
  • 242,874
  • 37
  • 412
  • 384
  • As the question is it is a long answer. Please narrow it down. You mean the Python side processing is expensive so you close the cursor (and the connection) while doing it? Did you consider [`with hold`?](http://initd.org/psycopg/docs/usage.html#server-side-cursors). Did you consider Postgresql side processing in instead? – Clodoaldo Neto Mar 17 '17 at 09:19
  • Not sure what you mean. I just want a cursor object so that I can do `cur.execute(...)`, and when I do that it will not load all the results into memory at once. Then after I process them by iterating over the cursor, or using `fetchone` or whatever, I want to be able to call `cur.execute(...)` again on the same cursor object. Will `with hold` accomplish that? – BrenBarn Mar 17 '17 at 17:37
  • Do `execute` only once. From then on just `fetch` from the cursor. – Clodoaldo Neto Mar 17 '17 at 20:13
  • @ClodoaldoNeto: What if I want to do multiple queries? – BrenBarn Mar 18 '17 at 01:20
  • Use a different cursor for each query. That's why I asked you to narrow the question down. – Clodoaldo Neto Mar 18 '17 at 09:46
  • If you will do multiple queries and combine them in Python then it is likely wrong. – Clodoaldo Neto Mar 18 '17 at 09:50
  • Is that the only option? It's just a pain to have to recreate cursors everywhere, instead of passing one cursor around. – BrenBarn Mar 23 '17 at 18:28
  • create a wrapper, I have that for my PG query so I don't have to recreate it everywhere. – gold_cy Apr 17 '17 at 18:26

1 Answers1

7

Just don't reuse the cursors. Reuse the connections.

piro
  • 13,378
  • 5
  • 34
  • 38