ssh mysql 导出excel_python通过SSH连接Oracle/Mysql数据库并导出到Excel

----------------------------------------------------------------------

|

-------------+ | +----------+ +---------

LOCAL | | | REMOTE | | PRIVATE

CLIENT | <== SSH ========> | SERVER | <== local ==> | SERVER

-------------+ | +----------+ +---------

| 中间跳板机 数据库服务器

FIREWALL (only port 10022 is open)

----------------------------------------------------------------------

#!/usr/bin/env python

#coding:utf-8

import cx_Oracle

import xlsxwriter

import time

from sshtunnel import SSHTunnelForwarder

userInfo = '数据库用户名/密码'

dbStr = '@PRIVATE SERVER IP:1521/数据库服务名'

connstr = userInfo + dbStr

LOCAL_PORT = 38399

DSN = "数据库用户名/密码@localhost:%d/dbcenter" %LOCAL_PORT

########不通过ssh来连接Oracle数据库直接用以下语句即可

#con = cx_Oracle.connect(connstr)

#数据库语句

sql ='''

select * from TMP

'''

#######通过ssh中间跳板机来连接Oracle数据库

with SSHTunnelForwarder(

('REMOTE SERVER IP', 10022), #远程中间跳板机器的配置

ssh_password="root",

ssh_username="root",

remote_bind_address=("PRIVATE SERVER IP", 1521), #远程数据库所在机器配置

local_bind_address=("127.0.0.1", LOCAL_PORT) #本地转发接口配置,与DSN的配置相关

) as server:

#如果是Mysql数据库则用以下连接

# conn = MySQLdb.connect(host='127.0.0.1', #此处必须是是127.0.0.1

# port=server.local_bind_port,

# user='user',

# passwd='password',

# db='dbname')

print("sshConnectSuccess")

con = cx_Oracle.connect(DSN)

cursor = con.cursor()

query1 = cursor.execute(sql)

title = [i[0] for i in query1.description]

date_now = time.strftime("%Y%m%d", time.localtime())

#name and path

report_name = "数据" + date_now + '.xlsx'

#生成xlsx格式oracle查询统计报表

workbook = xlsxwriter.Workbook(report_name, {'default_date_format': 'yyyy-mm-dd'})

worksheet = workbook.add_worksheet()

data = cursor.fetchall()

worksheet.write_row(0, 0, title)

for row, row_data in enumerate(data):

worksheet.write_row(row+1, 0, row_data)

cursor.close()

con.close()

workbook.close()

参考文章

【1】https://pypi.org/project/sshtunnel/#description

【2】https://www.cnblogs.com/luyingfeng/p/6386093.html

【3】https://github.com/oracle/python-cx_Oracle/issues/158