Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python / PostgreSQL - How to copy a table to another using the psycopg2 library?

I'm writing the code in Python and am using the psycopg2 library to deal with my PostgreSQL database.

I have the table Orders with about 30 rows. I want to copy this entire table to an empty one with the same structure called Orders2.

How can I do this?

Thank you.

like image 288
Diogo Magalhães Avatar asked Sep 01 '25 16:09

Diogo Magalhães


2 Answers

The answer is:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
import sys
import cStringIO

con = psycopg2.connect(database="xxxx", user="xxxx", password="xxxx", host="localhost")
cur = con.cursor()

input = cStringIO.StringIO()
cur.copy_expert('COPY (select * from Orders) TO STDOUT', input)
input.seek(0)
cur.copy_expert('COPY Orders2 FROM STDOUT', input)
con.commit()
like image 70
Diogo Magalhães Avatar answered Sep 04 '25 06:09

Diogo Magalhães


While your own answer is correct, there is a better way that does not involve dumping to a file and is generally more flexible.

INSERT INTO orders2 SELECT * FROM orders
like image 26
e4c5 Avatar answered Sep 04 '25 05:09

e4c5