What is the advantage of using multiple cursors in psycopg2 for PostgreSQL queries?

Question

What is the difference between using a single cursor in psycopg2 to perform all your queries against using multiple cursors.

I.e, say I do this:

import psycopg2 as pg2
con = psycopg2.connect(...)
cur = con.cursor()
cur.execute(...)
....
....
cur.execute(...)
...

and every time I wish to execute a query thereafter, I use the same cursor cur.

Alternatively I could do this every time I want to query my database:

with cur as con.cursor():
    cur.execute(...)

In which case, my cursor cur would be deleted after every use.

Which method is better? Does one have an advantage over another? Is one faster than the other? More generally, why are multiple cursors for one connection even needed?

Answer 1

The two options are comparable; you can always benchmark both to see if there's a meaningful difference, but psycopg2 cursors are pretty lightweight (they don't represent an actual server-side, DECLAREd cursor, unless you pass a name argument) and I wouldn't expect any substantial slowdown from either route.

The reason psycopg2 has cursors at all is twofold. The first is to be able to represent server-side cursors for situations where the result set is larger than memory, and can't be retrieved from the DB all at once; in this case the cursor serves as the client-side interface for interacting with the server-side cursor.

The second is that psycopg2 cursors are not thread-safe; a connection object can be freely used by any thread, but each cursor should be used by at most one thread. Having a cursor-per-thread allows for multithreaded applications to access the DB from any thread, while sharing the same connection.

See the psycopg2 usage docs on server side cursors and thread and process safety for more details.