记录一下源码
from sshtunnel import SSHTunnelForwarder
import psycopg2
def getdate(sql):
a = []
with SSHTunnelForwarder(
("跳板机ip",22),
ssh_username="跳板机的账户名",
ssh_pkey="私钥地址",
ssh_private_key_password="跳板机密码",
remote_bind_address=("远程机ip",5432),
)as server:
conn = psycopg2.connect(host='127.0.0.1',# 此处必须是是127.0.0.1
port = server.local_bind_port,
user ='数据库账户名',
password ='数据库密码',
database ='数据库表名')
cursor = conn.cursor()
#cursor.execute("select * from databse limit 1;")
try :
cursor.execute(sql)
raws = cursor.fetchall()
except :
raws = []
a =list(raws)
cursor.close()
conn.close()
return a
if __name__ =="__main__":
print(getdata(""))
或者使用另外一种方法，不用with .... as ....
import psycopg2
from sshtunnel import SSHTunnelForwarder
import pandas as pd
server = SSHTunnelForwarder(
# 指定ssh登录的跳转机的
address ssh_address_or_host=('跳板机ip', 端口),
ssh_username='用户名',
#设置密钥
ssh_pkey = '私钥文件全路径',
ssh_private_key_password='私钥密码',
# 设置数据库服务地址及端口
remote_bind_address=('数据库ip', 端口))
server.start()
conn = psycopg2.connect(
database = '库名',
user = '用户名',
password = '数据库密码',
host = '127.0.0.1',#host、port 固定
port = server.local_bind_port)
sql = 'select ...;'
df = pd.read_sql(sql, conn)
sever.close( ）
