Copying a table with psycopg2 copy_from and copy_expert?

Question

I want to copy table rows from the old database to the new one using the copy commands available in psycopg2. I thought I could redirect StringIO as below

io = StringIO.StringIO('') whereClause = " SELECT %s FROM %s WHERE home_city='%s' "%( ','.join(columns), tablename, city, ) old_db_cursor.execute(whereClause) rows = old_db_cursor.fetchall() logger.info('Should get %d rows',len([r for r in rows])) sql = 'COPY (%s) to STDOUT'%(whereClause,) old_db_cursor.copy_expert( sql, io, ) new_db_cursor.copy_from( io, tablename, columns=columns) new_db_connection.commit() 

where the log is displayed, I should get 30,000 lines. But I do not see new lines, despite the absence of error messages. For what it's worth, checking the length of io.read() shows that it is zero.

How can I do this job?

Answer 1

Answering my own question, you need to rewind the StringIO object using

 io.seek(0)