利用SSH连接服务器并从服务器上的PG数据库提取数据,保存为excel文件。
Python代码如下:
## connectted to postgresql DB in the local PC
import psycopg2
import paramiko
from sshtunnel import SSHTunnelForwarder
import xlsxwriter
#获取密钥
private_key=paramiko.RSAKey.from_private_key_file('privacy_key_filepath\\id_rsa') #私钥路径
with SSHTunnelForwarder(
ssh_address_or_host=('192.168.2.9',22), #ssh主机IP+端口,本例局域网
ssh_pkey=private_key,
ssh_username='hrm', #服务器用户名
remote_bind_address=('127.0.0.1',5432)) as server: #数据库服务器地址及端口
# server.start() #start ssh sever
conn=psycopg2.connect(database="test_db",
user="tester",
password="test_user",
host="127.0.0.1",
port=server.local_bind_port)
print('connected to the DB successfully')
cur=conn.cursor()
L=['title','content','url','user_name'] #列标题
with xlsxwrit