Python psycopg2 - Work with large data

Question

I'm trying to transfer a large data (15 B) from one database (postgresql) to other with python/psycopg2 on docker. My docker has 4 GB of memory and is getting out of memory.

What I am doing wrong?

cursor = conn.cursor()
cursor.execute('select * from schema.table')
for row in cursor:
   tp = tuple(map(lambda x: x.encode('utf-8'), row)
   cursor.execute('Insert into table2 values {}'.format(tp))
   conn.commit()

Take a look here: http://stackoverflow.com/a/10147451/771848. Hope that helps. — alecxe, Jul 07 '16 at 19:52
Thanks. But my problem is with cursor, that is getting all data to memory. — gabriel.almeida, Jul 07 '16 at 20:06

score 1 · Accepted Answer · answered Jul 07 '16 at 20:20

1

Use copy_to and copy_from

f = open('t.txt', 'wb')
conn = psycopg2.connect(database='source_db') 
cursor = conn.cursor() 
cursor.copy_to(f, 'source_table')
conn.close()
f.close()

f = open('t.txt', 'r')
conn = psycopg2.connect(database='target_db') 
cursor = conn.cursor() 
cursor.copy_from(f, 'target_table')
conn.close()
f.close()

answered Jul 07 '16 at 20:20

Clodoaldo Neto

118,695
26
233
260

This solve the problem. Thanks. And with data streaming, what i have to do? – gabriel.almeida Jul 08 '16 at 13:05

Python psycopg2 - Work with large data

1 Answers1