import pymysql
from sshtunnel import SSHTunnelForwarder
import pymysql.cursors  #以dict形式输出

def dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd):
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            #ssh_password="sshpasswd",
            ssh_pkey=keyfile,
            ssh_username=ssh_user,
            remote_bind_address=(db_host, db_port)
    ) as server:

        db = pymysql.connect(
            host='127.0.0.1',
            port=server.local_bind_port,
            user=db_user,
            passwd=db_passwd,
            db=db_name,
            charset="utf8",
            cursorclass=pymysql.cursors.DictCursor)

        cursor = db.cursor()

        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            db.commit()
        except:
            db.rollback()

        collect = []
        for result in data:
            collect.append(result)

        db.close()
        cursor.close()

        return collect

if __name__ == "__main__":
    ssh_host = "10.10.2.13"           #SSH服务器地址
    ssh_port = 22                  #SSH端口
    keyfile = xxxx.key"  #SSH密钥
    ssh_user = "root"           #SSH用户名
    db_host = "127.0.0.1"        #数据库地址
    db_name = 'DBname'              #数据库名
    sql = 'show tables;'        #SQL
    db_port = 3306                 #数据库端口
    db_user = 'root'               #数据库用户名
    db_passwd = '33333'           #数据库密码
    result = dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd)
    print (result)