I may be confused but that's exactly why I ask. Can a program using psycopg2 execute commands against the database without using a cursor? Every example I've ever seen uses cur = conn.cursor() and then an execute statement like cur.execute("""SELECT datname from pg_database""")
My question is can SELECT datname from pg_database be done without cur?
Is the psycopg2 idea of a cursor the same idea of the actual database cursor i.e. is there a 1-1 relationship?
After read What are the benefits of using database cursor? I was a bit confused as they seem to speak as if cursors aren't nescecary (but I guess when directly interacting with the db through e.g. pgadmin they are).
That form is part of the Python DB-API. It's written to accommodate databases where one connection can multiplex multiple statements with open result sets, and to emulate that where the database does not support it directly.
Rather than have two ways of doing the same thing - with a cursor, or without - the DB-API expects database drivers always use a cursor object, even if they're only ever keeping one result set open at once. The interface is the same for client-side and database-side cursors.
By default psycopg2's cursors are client-side. Its "cursors" aren't really anything to do with a database cursor. When you run a statement, the result set (if any) is read into the psycopg2 cursor object. You can then iterate over it client-side. In that sense it is a cursor, in that you can read the next row, scroll the cursor, etc.
You can ask psycopg2 to use true database-side cursors. It doesn't use PostgreSQL's wire protocol cursor support (unfortunately), instead doing the cursors at the SQL level with DECLARE, FETCH, etc statements. Server-side cursors don't require lots of memory on the client (or server) and they can deliver the first results to the application before the whole result set has been transferred. However, by default they're forward-only, unlike psycopg2's in-memory client-side cursors, and they consume database resources until they are released.
Both have different advantages and disadvantages.