利用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 xlsxwriter.Workbook('.\train\positive.xlsx') as wb1: #事先需建立好train文件夹 sheet=wb1.add_worksheet() #创建工作表对象 bold=wb1.add_format({'bold':True}) #定义加粗的格式对象 for l in range(len(L)): sheet.write(0,l,L[l],bold) #将列标题插入表格 SQL1 = "SELECT title,content,url,user_name \ FROM public.raw_data WHERE sentiment=1 AND created_at < '2018-07-06 12:00:00'" cur.execute(SQL1) numrows=int(cur.rowcount) rows=cur.fetchall() #返回全部的查询结果 for i in range(numrows): g=rows[i] sheet.write(i+1,0,g[0]) sheet.write(i+1,1,g[1]) sheet.write(i+1,2,g[2]) sheet.write(i+1,3,g[3]) #sheet.write(i+1,4,g[4])
