psycopg2: connect copy_from and copy_to

[Chris]

On Feb 19, 5:06 pm, Thomas Guettler <h... at tbz-pariv.de> wrote:
> Hi,
>
> I want to copy data from a production database to
> a testing database. Up to now I used psycopg2 and
> copy_from and copy_to with two cursors and a temporary file.
>
> This works, but it would be better, if the data
> gets piped from one cursor to the next without a temporary
> file.
>
> The psycopg2 methods look like this:
> copy_to(fd, table)   # read data
> copy_from(fd, table) # write data
>
> Using select (wait until fd has data) does not work, since
> I pass in the fd. If copy_to would return the file descriptor,
> I could use it to pass the data to copy_from.
>
> I found no way to connect them. Maybe it could be done
> with threading, but I try to avoid threads.
>
> Since the script will run only on Unix, I could use pipes. But
> this would fail, too, since copy_to() would block before I can
> call copy_from (since buffer is full and no one is reading the data
> from the pipe).
>
> Any suggestions?
>
>   Thomas

Doesn't PostGres come with Export/Import apps ? That would be easiest
(and faster).

Else,

prod_cursor.execute('select data from production')
for each_record in cursor.fetchall():
    dev_cursor.execute('insert into testing')

that is one way of doing it