Python Postgres Best way to insert data from table on one DB to another table on another DB

Question

I have the following python code that copies content of a table on postgres DB1 and INSERTS into a similar table on postgres DB2.

I want to speed it up by using BULK INSERTS. How do I achieve this

import psycopg2
import sys
import os


all_data = []


try:
    connec = psycopg2.connect("host = server1 dbname = DB1 ")
    connecc = psycopg2.connect("host = server2 dbname = DB2 ")
    connec.autocommit = True
    connecc.autocommit = True
except:
    print("I am unable to connect to the database.")


cur = connec.cursor()

curr = connecc.cursor()



cur.execute("""SELECT * FROM TABLE1""")


curr.execute("TRUNCATE table TABLE2")

rows = cur.fetchall()

for row in rows:
    all_data = row


    curr.execute("INSERT INTO TABLE2 "
                 "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,     %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,"
             " %s)"
    ,(all_data[0],  all_data[1],all_data[2],all_data[3],all_data[4],     all_data[5], all_data[6], all_data[7],
      all_data[8], all_data[9], all_data[10], all_data[11],     all_data[12], all_data[13], all_data[14],all_data[15],
      all_data[16], all_data[17], all_data[18], all_data[19],
      all_data[20], all_data[21], all_data[22]))

    connecc.commit()

connec.close()

connecc.close()

Check Copy To and Copy From

Clodoaldo Neto
– Clodoaldo Neto

2017-01-31 20:15:46 +00:00
Commented Jan 31, 2017 at 20:15 — Clodoaldo Neto
– Clodoaldo Neto, Commented Jan 31, 2017 at 20:15

Roman Tkachuk · Accepted Answer · 2017-01-31 19:50:01Z

1

Simpliest way is to use FDW (foreign data wrappers) for connect both servers (https://www.postgresql.org/docs/9.5/static/postgres-fdw.html). And operate by both table in one server.

Second way is use dblink (https://www.postgresql.org/docs/9.6/static/dblink.html).

By both this way data directly pass from one server to second (no via your program)

answered Jan 31, 2017 at 19:50

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Appiah Sarfo Over a year ago

DBLINK to the rescue

Roman Tkachuk Over a year ago

FDW is newest way, but needed more actions. But allow to do queries to foreign tables as local. More than foreign tables may be not only postgresql - but also oracle, mysql and even just csv file :-)

Collectives™ on Stack Overflow

Python Postgres Best way to insert data from table on one DB to another table on another DB

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related