ya, i know it’s months old 😛
DB-API’s cursor appears to be closely modeled after SQL cursors. AFA resource(rows) management is concerned, DB-API does not specify whether the client must retrieve all the rows or DECLARE an actual SQL cursor. As long as the fetchXXX interfaces do what they’re supposed to, DB-API is happy.
AFA psycopg2 cursors are concerned(as you may well know), “unnamed DB-API cursors” will fetch the entire result set–AFAIK buffered in memory by libpq. “named DB-API cursors”(a psycopg2 concept that may not be portable), will request the rows on demand(fetchXXX methods).
As cited by “unbeknown”, executemany can be used to optimize multiple runs of the same command. However, it doesn’t accommodate for the need of prepared statements; when repeat executions of a statement with different parameter sets is not directly sequential, executemany() will perform just as well as execute(). DB-API does “provide” driver authors with the ability to cache executed statements, but its implementation(what’s the scope/lifetime of the statement?) is undefined, so it’s impossible to set expectations across DB-API implementations.
If you are loading lots of data into PostgreSQL, I would strongly recommend trying to find a way to use COPY.