python通过ssh连接mysql数据库

简单介绍

from sshtunnel import SSHTunnelForwarder
import os, pymysql

class MySqlSSH:
    def __init__(self):
        self.server = SSHTunnelForwarder(
            ssh_address_or_host=('192.168.68.129', 22),
            ssh_username='root',
            ssh_password='xxxxxx',
            remote_bind_address=('127.0.0.1', 3306)
        )

        # 启动隧道服务
        self.server.start()

        mysql_config = {
            'user': 'root',
            'passwd': 'xxxxxx',
            'host': '127.0.0.1',
            'port': self.server.local_bind_port,
            'db': 'smartwatch'
        }

        # 连接数据库
        self.mysql = pymysql.connect(**mysql_config)
        self.cursor = self.mysql.cursor()

    def fetch_one(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 查看结果
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 查看结果
        result = self.cursor.fetchall()
        return result

    def execute_sql(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 提交
        self.mysql.commit()
        # 返回影响行数
        result = "受影响的行数：{}".format(self.cursor.rowcount)
        return result

    def close(self):
        self.cursor.close()  # 关闭查询
        self.mysql.close()
        self.server.close()  # 关闭服务

还可以另外封装方法方便调用

# 连接库工具
def conn_mysql(sql):
    try:
        conn = MySqlSSH()
        if sql.split()[0].lower() == "select":
            return conn.fetch_all(sql)
        else:
            return conn.execute_sql(sql)

    except Exception as e:
        GetLog.get_log().error(e)
        raise

    finally:
        # 5、关闭连接
        conn.close()