CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
Arush Shah
Arush Shah

Reputation: 63

PostgreSQL Python copy table from one database to another

I'm using psycopg2 in Python to manage 2 databases. I want to copy data from every table in db1 into db2. I don't want to use pg_dump because I want to be able to update the tables in the second database as changes are made to the first and pg_dump won't run if data in the table already exists. As of now, I have 2 cursors for each database. I'm copying tables using

result = cursor1.execute('SELECT * from "table1"')

I'm trying to do something like

cursor2.execute('INSERT INTO table2 result')

I've also used pg_dump -s to replicate the same schema of the first database in the second.

How can I make this work?

Upvotes: 4

Views: 6468

Answers (1)

metmirr
metmirr

Reputation: 4302

If you iterate over the result and for every row use insert statement would work but might not be the right way to do this transfer(updated like @Jules says):

for row in result.fetchall():
    cursor2.execute('INSERT INTO table2 VALUES %s',(row,))

Upvotes: 3

Related Questions