How to use server side cursors with psycopg2

Question

I have a table with 4million rows and I use psycopg2 to execture a:

   SELECT * FROM ..WHERE query

I haven't heard before of the server side cursor and I am reading its a good practice when you expect lots of results.

I find the documentation a bit limited and I have some basic questions.

First I declare the server-side cursor as:

cur = conn.cursor('cursor-name')

then I execute the query as:

cur.itersize = 10000
sqlstr = "SELECT clmn1, clmn2 FROM public.table WHERE clmn1 LIKE 'At%'"
cur.execute(sqlstr)

My question is: What do I do now? How do I get the results?

Do I iterate through the rows as:

row = cur.fetchone()
while row:
   row = cur.fetchone()

or I use fetchmany() and I do this:

row = cur.fetchmany(10)

But in the second case how can I "scroll" the results?

Also what is the point of itersize?

Answer 1

Psycopg2 has a nice interface for working with server side cursors. This is a possible template to use:

with psycopg2.connect(database_connection_string) as conn:
    with conn.cursor(name='name_of_cursor') as cursor:
    
        cursor.itersize = 20000

        query = "SELECT * FROM ..."
        cursor.execute(query)

        for row in cursor:
            # process row 

The code above creates the connection and automatically places the query result into a server side cursor. The value itersize sets the number of rows that the client will pull down at a time from the server side cursor. The value you use should balance number of network calls versus memory usage on the client. For example, if your result count is three million, an itersize value of 2000 (the default value) will result in 1500 network calls. If the memory consumed by 2000 rows is light, increase that number.

When using for row in cursor you are of course working with one row at a time, but Psycopg2 will prefetch itersize rows at a time for you.

If you want to use fetchmany for some reason, you could do something like this:

while True:
    rows = cursor.fetchmany(100)
    if len(rows) > 0:
        for row in rows:
            # process row
    else:
        break

This usage of fetchmany will not trigger a network call to the server for more rows until the prefetched batch has been exhausted. (This is a convoluted example that provides nothing over the code above, but demonstrates how to use fetchmany should there be a need.)

Answer 2

I tend to do something like this when I don't want to load millions of rows at once. You can turn a program into quite a memory hog if you load millions of rows into memory. Especially if you're making python domain objects out of those rows or something like that. I'm not sure if the uuid4 in the name is necessary, but my thought is that I want individual server side cursors that don't overlap if two processes make the same query.

from uuid import uuid4
import psycopg2

def fetch_things() -> Iterable[MyDomainObject]:
    with psycopg2.connect(database_connection_string) as conn:
        with conn.cursor(name=f"my_name_{uuid4()}") as cursor:
            cursor.itersize = 500_000

            query = "SELECT * FROM ..."
            cursor.execute(query)

            for row in cursor:
                yield MyDomainObject(row)

I'm interested if anyone knows if this creates a storage problem on the SQL server or anything like that.

Answer 3

Additionally to cur.fetchmany(n) you can use PostgreSQL cursors:

cur.execute("declare foo cursor for select * from generate_series(1,1000000)")
cur.execute("fetch forward 100 from foo")
rows = cur.fetchall()
# ...
cur.execute("fetch forward 100 from foo")
rows = cur.fetchall()
# and so on