記錄一下源碼
from sshtunnel import SSHTunnelForwarder
import psycopg2
def getdate(sql):
a = []
with SSHTunnelForwarder(
("跳板機(jī)ip",22),
ssh_username="跳板機(jī)的賬戶名",
ssh_pkey="私鑰地址",
ssh_private_key_password="跳板機(jī)密碼",
remote_bind_address=("遠(yuǎn)程機(jī)ip",5432),
)as server:
conn = psycopg2.connect(host='127.0.0.1',# 此處必須是是127.0.0.1
port = server.local_bind_port,
user ='數(shù)據(jù)庫賬戶名',
password ='數(shù)據(jù)庫密碼',
database ='數(shù)據(jù)庫表名')
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登錄的跳轉(zhuǎn)機(jī)的
address ssh_address_or_host=('跳板機(jī)ip', 端口),
ssh_username='用戶名',
#設(shè)置密鑰
ssh_pkey = '私鑰文件全路徑',
ssh_private_key_password='私鑰密碼',
# 設(shè)置數(shù)據(jù)庫服務(wù)地址及端口
remote_bind_address=('數(shù)據(jù)庫ip', 端口))
server.start()
conn = psycopg2.connect(
database = '庫名',
user = '用戶名',
password = '數(shù)據(jù)庫密碼',
host = '127.0.0.1',#host讼呢、port 固定
port = server.local_bind_port)
sql = 'select ...;'
df = pd.read_sql(sql, conn)
sever.close( ）
