pymysql通过ssh方式进行数据库操作

由于阿里云服务器安全组规则没有开放3306端口，我们不能直接通过3306端口进行访问，但是可以采用ssh的方式登录mysql数据库，代码如下

# _*_ coding:utf-8 _*_
import pymysql
from sshtunnel import SSHTunnelForwarder

# 数据库配置信息
host = '192.168.1.1'
user = 'username'
dbpassword = 'password'
dbname = 'testdb'


class ExecuteSQL(object):

    # 传入待执行的sql语句sql
    def __init__(self, sql):
        self.sql = sql
	# 查询语句
    def query_sql(self):
        server = SSHTunnelForwarder(
            ssh_address_or_host=(host, 22),  # 指定ssh登录的跳转机的address
            ssh_username='root',  # 跳转机的用户
            ssh_password='123456',  # 跳转机的密码
            remote_bind_address=('127.0.0.1', 3306))
        server.start()
        conn = pymysql.connect(
            user=user,
            passwd=dbpassword,
            host="127.0.0.1", # 此处必须是是127.0.0.1
            db=dbname,
            port=server.local_bind_port,
            cursorclass=pymysql.cursors.DictCursor)
        # 游标
        cursor = conn.cursor()
        try:
            # 执行SQL语句
            cursor.execute(self.sql)
            # 获取所有记录列表
            results = cursor.fetchall()
        except Exception as data:
            print('Error: 执行查询失败，%s' % data)
        # 关闭游标
        cursor.close()
        # 关闭数据库
        conn.close()
        server.close()
        return results
	
	# 新增和修改语句
    def update_sql(self):
        server = SSHTunnelForwarder(
            ssh_address_or_host=(host, 22),  # 指定ssh登录的跳转机的address
            ssh_username='root',  # 跳转机的用户
            ssh_password='123456',  # 跳转机的密码
            remote_bind_address=('127.0.0.1', 3306))
        server.start()
        conn = pymysql.connect(
            user=user,
            passwd=dbpassword,
            host="127.0.0.1", # 此处必须是是127.0.0.1
            db=dbname,
            port=server.local_bind_port,
            cursorclass=pymysql.cursors.DictCursor)
        # 游标
        cursor = conn.cursor()
        try:
            # 执行SQL语句
            cursor.execute(self.sql)
            # 提交
            conn.commit()
        except Exception as data:
            conn.rollback()
            print('Error: 插入或更新失败，%s' % data)
        # 关闭游标
        cursor.close()
        # 关闭数据库
        conn.close()
        server.close()