Skip to content

Instantly share code, notes, and snippets.

@johndobrien
Created July 12, 2012 21:21
Show Gist options
  • Save johndobrien/3101086 to your computer and use it in GitHub Desktop.
Save johndobrien/3101086 to your computer and use it in GitHub Desktop.
Using copy_expert to copy rows to a different database
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment