Using copy_expert to copy rows to a different database

gistfile1.py

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE

conn_str = "dbname='{db}' user='john' host='localhost' password='john'"


table_sql = "CREATE TABLE bigdata (A INT PRIMARY KEY, b TEXT, c TEXT NOT NULL);"

conn1 = psycopg2.connect(conn_str.format(db='test-1'))
conn2 = psycopg2.connect(conn_str.format(db='test-2'))


for e, conn in enumerate((conn1, conn2)):
    cur = conn.cursor()
    cur.execute("DROP TABLE IF EXISTS bigdata")
    cur.execute(table_sql)
    cur.execute("COMMIT;")
    for i in range(e, 1000, 2):
        cur.execute("INSERT INTO bigdata (A, B, C) VALUES (%s, %s, %s);",
                    (i, "X%s" % i, "Y%s" % i))
    cur.execute("COMMIT;")
    cur.execute("select count(*) from bigdata;")
    print "%d in table" % cur.fetchone()[0]
    cur.close()



import sys
import cStringIO


# we need to put a special lock on the rows
lock_conn1 = psycopg2.connect(conn_str.format(db='test-1'))
lock_conn1.set_session(isolation_level=ISOLATION_LEVEL_SERIALIZABLE)
lcur = lock_conn1.cursor()
lcur.execute("SELECT * FROM bigdata where A between 100 and 300 FOR UPDATE NOWAIT")

input = cStringIO.StringIO()
cur = conn1.cursor()
cur.copy_expert('COPY (select * from bigdata where A between 100 and 300) TO STDOUT', input)
cur.close()
input.seek(0)
cur = conn2.cursor()
cur.copy_expert('COPY bigdata FROM STDOUT', input)
cur.execute("COMMIT;")
cur.execute("select count(*) from bigdata;")
print "%d in table" % cur.fetchone()[0]
cur.close()

lock_conn1.rollback()