SQL gets data from BEGIN; ...; END; block in python

Question

I want to run several queries at once, 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 message:

psycopg2.ProgrammingError: no results to fetch

How can I get data this way?

Similarly, if I have only a few samples in a row, I only get data from the last. Is there a way to get data from all of them?

Answer 1

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

BEGIN;
SELECT ...;
END;

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

"BEGIN" "END" - SQL / . ( , ). , , , , , (psycopg2) . , Perl DBI AutoCommit = > 0 "BEGIN" ; "END" ( "COMMIT" ..), $dbh- > commit; , Python DB-API , , JDBC, ...

Answer 2

-, , DML , , .