Postgresql querying two different databases that are having same named tables

Question

Greetings, by using the psycopg2 library of python, I can currently connect to a DB and retrieve such like this:

conn = psycopg2.connect("dbname='db1' user='postgres' host='xxxxxx' password='mypass'");
qr = conn.cursor()
qr.execute("SELECT avg(pavg) FROM mytable WHERE id =5")

Now beside the database named "db1", I have to query another database from another ip that contains the same table, append the queries, i.e.

conn1 = psycopg2.connect("dbname='mydb' user='postgres' host='xxxxxx' password='mypass'");

conn2 = psycopg2.connect("dbname='mydb' user='postgres' host='yyyyyy' password='mypass'");

qr1 = conn1.cursor()
qr1.execute("SELECT avg(pavg) FROM mytable WHERE id =5")

qr2 = conn1.cursor()
qr2.execute("SELECT avg(pavg) FROM mytable WHERE id =5")

How can I achieve this ?

Answer 1

not a python expert, but is there any reason you cannot do :

conn2 = psycopg2.connect("dbname='db1' user='postgres' host='yyyyyy' password='mypass'");
qr2 = conn2.cursor()
qr2.execute("SELECT avg(pavg) FROM mytable WHERE id =5")

then fetch the results for qr qnd qr2 objects :

rows = qr .fetchall()
for row in rows:
   do you have to do ....
and then 
rows = qr2 .fetchall()
for row in rows:
   do you have to do ....

Answer 2

Sounds like a dubious design. Anyway, IIRC, cursor.fetchall() returns a list, so you can do rows = qr.fetchall() + qr2.fetchall().

You'll have to handle duplicates yourself. If you were using different schemas you could do SELECT ... FROM schema1.foo ... UNION SELECT ... FROM schema2.foo.