What's psycopg2 doing when I iterate a cursor?

Question

I'm trying to understand what this code is doing behind the scenes:

import psycopg2

c = psycopg2.connect('db=some_db user=me').cursor()
c.execute('select * from some_table')
for row in c:
    pass

Per PEP 249 my understanding was that this was repeatedly calling Cursor.next() which is the equivalent of calling Cursor.fetchone(). However, the psycopg2 docs say the following:

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process.

So I'm confused -- when I run the code above, is it storing the results on the server and fetching them one by one, or is it bringing over everything at once?

Answer 1

It depends on how you configure psycopg2. See itersize and server side cursors.

By default it fetches all rows into client memory, then just iterates over the fetched rows with the cursor. But per the above docs, you can configure batch fetches from a server-side cursor instead.