用psycopg2在python中管道化postgres COPY [英] piping postgres COPY in python with psycopg2

问题描述

我正在编写一个脚本，以便使用psycopg2在同一网络上的两台计算机之间复制某些数据.我要替换一些旧的，丑陋的bash来复制

I'm writing a script to do a copy of some data between two machines on the same network using psycopg2. I'm replacing some old, ugly bash that does the copy with

psql -c -h remote.host "COPY table TO STDOUT" | psql -c "COPY table FROM STDIN"

这似乎既简单又

This seems like both the simplest and most efficient way to do the copy. It's easy to replicate in python with a stringIO or a temp-file, like so:

buf = StringIO()

from_curs   = from_conn.cursor()
to_curs     = to_conn.cursor()

from_curs.copy_expert("COPY table TO STDOUT", buf)
buf.seek(0, os.SEEK_SET)
to_curs.copy_expert("COPY table FROM STDIN", buf)

...但是这涉及将所有数据保存到磁盘/内存中.

...but that involves saving all the data to disk/in memory.

有人能找到一种模仿这样的副本中的Unix管道行为的方法吗?我似乎找不到不涉及POpen的unix-pipe对象-毕竟，最好的解决方案是只使用POpen和子进程.

Has anyone figured out a way to mimic the behavior of a Unix pipe in a copy like this? I can't seem to find a unix-pipe object that doesn't involve POpen - Maybe the best solution is to just use POpen and subprocess, after all.

推荐答案

您将不得不将一个调用放在单独的线程中.我刚刚意识到您可以使用 os.pipe()，剩下的就是很简单:

You will have to put one of your calls in a separate thread. I just realized you can use os.pipe(), which makes the rest quite straightforward:

#!/usr/bin/python
import psycopg2
import os
import threading

fromdb = psycopg2.connect("dbname=from_db")
todb = psycopg2.connect("dbname=to_db")

r_fd, w_fd = os.pipe()

def copy_from():
    cur = todb.cursor()
    cur.copy_from(os.fdopen(r_fd), 'table')
    cur.close()
    todb.commit()

to_thread = threading.Thread(target=copy_from)
to_thread.start()

cur = fromdb.cursor()
write_f = os.fdopen(w_fd, 'w')
cur.copy_to(write_f, 'table')
write_f.close()   # or deadlock...

to_thread.join()

这篇关于用psycopg2在python中管道化postgres COPY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！