如何使用PYTHON进行PSQL COPY

Question

尝试使用python运行PSQL COPY命令。我能够连接到给定kube集群中的正确DB。

炮弹在前进途中失去了踪迹。当我退出时，我得到以下错误。我在os.system调用的PSQL命令中使用的引号有什么错误吗。

import os
import subprocess
conn = 'gcloud container clusters get-credentials clusterA --zone us-west --project projectX'
pg = 'kubectl -n DB exec -it postgres -- bash'

if __name__ == '__main__':
subprocess.call(back_clus_conn, shell=True)
subprocess.call(pg, shell=True)
os.system('psql -U postgres -c "COPY (SELECT * FROM "public"."tableName") TO tableName.csv DELIMITER ',' CSV"')

错误：

kubeconfig entry generated for clusterA.
Defaulted container "postgres" out of: postgres, postgres-exporter
bash-5.0#
bash-5.0# exit
exit
Traceback (most recent call last):
File "test.py", line 19, in <module>
os.system('psql -U postgres -c "COPY (SELECT * FROM "public"."tableName") TO tableName.csv DELIMITER ',' CSV"')
TypeError: system() takes exactly 1 argument (2 given)

Answer 1

您只需在postgres容器exec子流程调用中运行命令，如下所示：

import subprocess
conn = 'gcloud container clusters get-credentials clusterA --zone us-west --project projectX'
copy_cmd = 'psql -U postgres -c "COPY (SELECT * FROM "public"."tableName") TO tableName.csv DELIMITER ',' CSV"'
pg = f'kubectl -n DB exec -it postgres -- bash -c {copy_cmd}'
if __name__ == '__main__':
subprocess.call(back_clus_conn, shell=True)
subprocess.call(pg, shell=True)

Answer 2

基于粘性位，它告诉您给它两个参数，而不是一个。

目前您的代码是：

os.system('psql -U postgres -c "COPY (SELECT * FROM "public"."tableName") TO tableName.csv DELIMITER ',' CSV"')

在DELIMITER和CSV之间的逗号','中添加转义字符，以便Python识别：

os.system('psql -U postgres -c "COPY (SELECT * FROM "public"."tableName") TO tableName.csv DELIMITER ',' CSV"')