Multiple SELECTs in Psycopg2 or SQLAlchemy

Question

I want to execute multiple SELECT statements at once, as I do with

echo "SELECT * FROM x; SELECT * FROM y;" | psql

so that I send something to database only once, and receive all the result at once too.

It would be perfect to do it with SQLAlchemy, but it seems to be unsupported (isn't it?).

I thought I might reimplement some parts of SQLAlchemy to get that feature, but I didn't find the solution for this problem in psycopg2 too - executing two queries separated with a semicolon gives the results of the last query only.

So, is it possible to do it with SQLAlchemy (which would be perfect) or with psycopg2 (which would be fine too)?

Answer 1

Bartosz - I am facing a similar problem and came across your question from two years ago. One way to get around this might be to use Postgres json support. What you can do is construct two select queries that convert row output into json, union them together and then decode in python or whatever language you are using. This gets the data in one round trip to the database.

You could use other json constructors that postgres has available, such as row_to_json: http://www.postgresql.org/docs/9.3/static/functions-json.html

SELECT to_json((a, b, c))
FROM x

UNION ALL

SELECT to_json((d, e, f, g, h, i))
FROM y

Hope that helps