SQL get data out of BEGIN; …; END; block in python

Question

I want to run many select queries at once by putting them between BEGIN; END;. I tried the following:

cur = connection.cursor()
cur.execute("""
BEGIN;
SELECT ...;
END;""")
res = cur.fetchall()

However, I get the error:

psycopg2.ProgrammingError: no results to fetch

How can I actually get data this way?

Likewise, if I just have many selects in a row, I only get data back from the latest one. Is there a way to get data out of all of them?

Answer 1

If you're just SELECTing something and you don't have a function that performs any DML or the like, you shouldn't need to make an explicit transaction for any reason I'm aware of.

Answer 2

Postgresql doesn't actually support returning multiple result sets from a single command. If you pass this input to psql:

BEGIN;
SELECT ...;
END;

it will split this up client-side and actually execute three statements, only the second of which returns a result set.

"BEGIN" and "END" are SQL-level commands to start/finish a transaction. (There may be a lower-level protocol for doing this but I can't remember). You probably don't want to issue them directly, but rather have your driver (psycopg2) handle this. For example, with Perl's DBI I specify AutoCommit=>0 when connecting and it implicitly issues a "BEGIN" before my first command; and then "END" (or "COMMIT" etc) when I explicitly call $dbh->commit; I guess Python's DB-API works rather like this, since other systems such as JDBC do as well...