单连接上带有事务 block 的 Python Postgres 查询

Question

目前我有两个单独的语句传递给 Postgres (Greenplum)。1. 截表2. 使用\copy

加载数据

myStr="export PGPASSWORD=" + dbPass + "; psql -h " + dbHost + " -p " + dbPort + " -d " + dbName + " -U " + dbUser + " -c " + "\"" + "truncate table " + dbTable + ";\""
print(myStr)
subprocess.call(myStr,shell=True)
myStr="export PGPASSWORD=" + dbPass + "; psql -h " + dbHost + " -p " + dbPort + " -d " + dbName + " -U " + dbUser + " -c " + "\"" + "\\" + "copy " + dbTable + " from " + "'" + csvfile + "' with " + copyOpts + ";" + "select count(*) from " + dbTable + ";\""
print(myStr)
subprocess.call(myStr,shell=True)

有时加载有错误但截断已经发生，所以我试图在一个连接中运行这两个语句，这样我就可以在数据加载失败时放置一个事务 block (BEGIN ... COMMIT;)将回滚到截断发生之前。

我试过下面的方法:

myStr="export PGPASSWORD=" + dbPass + "; psql -h " + dbHost + " -p " + dbPort + " -d " + dbName + " -U " + dbUser + " -c " + "\"" + "truncate table " + dbTable + ";" + " \\" + "copy " + dbTable + " from " + "'" + csvfile + "' with " + copyOpts + ";" + "select count(*) from " + dbTable + ";\""
print(myStr)

解析为命令:

export PGPASSWORD=abcde; 
psql -h abcde.testserver.corp 
-p 5432 -d namem -U username -c 
"truncate table schema.example; 
\copy schema.example from 
'/home/testing/schema/schema.example_export.csv' 
with header null as '' escape 'off' delimiter E',' ;
select count(*) from schema.example;"

但是我得到了错误:

ERROR: syntax error at or near "\"

我认为这是因为 \ 命令必须在单独的一行上。

有没有办法将命令拆分成单独的行，以便我可以在单个连接中执行所有命令？

Answer 1

问题是，如果您使用 -c 选项，则无法将反斜杠命令与其他命令分开。您可以使用 echo 通过 STDIN 将命令发送到 psql:

export PGPASSWORD=abcde;
echo "truncate table schema.example; 
\copy schema.example from '/home/testing/schema/schema.example_export.csv' with header null as '' escape 'off' delimiter E',' ;
select count(*) from schema.example;" | psql -h abcde.testserver.corp -p 5432 -d namem -U username

这有点笨拙。最好使用 subprocess.Popen

theCommand = """truncate table schema.example; 
\copy schema.example from 
'/home/testing/schema/schema.example_export.csv' 
with header null as '' escape 'off' delimiter E',' ;
select count(*) from schema.example;"""
theProcess = subprocess.Popen("psql -h abcde.testserver.corp -p 5432 -d namem -U username", 
    stdin = subprocess.PIPE, stdout = subprocess.PIPE, stderr = subprocess.PIPE)
theOutput, theErrors = theProcess.communicate(input = theCommand)

但最好的方法应该是避免使用 shell 命令并使用像 PyGreSQL 这样的数据库适配器。