通过Python脚本中的expect命令捕获在远程PostGreSQL服务器中执行的查询结果

Question

我想抓住后格雷斯通过在远程服务器（无密码-ssh）中执行的查询结果预计用于为给定 PostGres 用户提供密码的命令。实际上，我们的应用程序有两个版本，旧版本不需要 Postgres 密码，而新版本需要密码。所以测试用例必须处理这个问题

在旧版本（不需要 psql 密码）中，执行查询的 cmd 字符串非常简单：

cmd = 'ssh ' + db_ip + ' "psql -h localhost -p 5434 -U perfmon -d network -c \\\"select count(*) from crm_customer_device_info;\\\""'

但是对于新版本（由于psql启用了密码），我尝试了以下方法，但出现超时

方法一：出现提示时执行查询

#! /usr/bin/python

import subprocess

def execute(cmd):
    proc = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    _output, _error = proc.communicate()
    _code = proc.returncode
    return _output, _error, _code

def executeQuery(db_ip, db_user, db_name, db_pass, sql_query):
    cmd = '''/usr/bin/expect -c 'spawn -noecho ssh -q {0} "psql -h localhost -p 5434 -U {1} -d {2}"
           expect {{
             -nocase "Password*" {{
                send "{3}\r"
                exp_continue
             }}
             "=# " {{
                send "{4}"
                set var $expect_out(buffer);
                send "\q\n"
              }}
           }}
           '
       '''.format(db_ip, db_user, db_name, db_pass, sql_query)
    return execute(cmd)

db_ip = "192.168.0.1"
db_user = "perfmon"
db_name = "network"
db_pass = "xxx"
sql_query = "select count(*) from crm_customer_device_info;"

_output, _error, return_code = executeQuery(db_ip, db_user, db_name, db_pass, sql_query)

方法2：使用执行查询 postgres-c 选项和处理超时场景

def executeQuery(db_ip, db_user, db_name, db_pass, sql_query):
    cmd = '''/usr/bin/expect -c 'spawn ssh {o} "psql -h localhost -p 5434 -U {1} -d {2} -c \\\'{4}\\\' "
           expect {{
             -nocase "Password*" {{
                send -- "{3}\r"
                exp_continue
             }}
             -nocase "permission denied" {{
               exit 4
             }}
             -nocase "timed out" {{
               exit 2
             }}
             timeout {{
               exit 1
             }}
           }}
           '
       '''.format(db_ip, db_user, db_name, db_pass, sql_query)
    return execute(cmd)

当我执行以下命令时预计直接在安装 psql 的服务器上而不是远程 ssh 上编写脚本，然后我设法获得输出：

spawn -noecho psql -h localhost -p 5434 -U user -d db;
               expect {
                 "^Password*" {
                   send -- "passwd\r"
                   exp_continue
                }
                "*=# " {
                send -- "select now();\r\n"
                set var $expect_out(buffer);

                send "\\q\r\n"
                exp_continue
               }
               }; #puts $var; #interact;