python通过SSH链接数据库

通过SSH链接数据库

mysql 的SSH数据库链接如下图


python3的SSH数据库链接代码如下

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import traceback
import pymysql
from sshtunnel import SSHTunnelForwarder


def main():
    with SSHTunnelForwarder(
            ssh_address_or_host=("118.123.123.123", 22),  # ssh跳转机的地址
            ssh_username="xiaoming",  # ssh的用户名
            ssh_pkey="C:/Users/Administrator/.ssh/id_rsa",  # ssh私钥地址
            ssh_private_key_password="",  # ssh私钥密码
            local_bind_address=("0.0.0.0", 10022),
            remote_bind_address=("192.168.0.1", 3306)) as server:  # 数据库地址
        server.start()
        myConfig = pymysql.connect(
            user="userName",  # 数据库登录名
            passwd="password",  # 数据库密码
            host="127.0.0.1",  # 写死
            db="db_name",  # 数据库名称
            port=server.local_bind_port,
            cursorclass=pymysql.cursors.DictCursor)  # sql查询结果返回类型：DictCursor 为字典类型， 没有指定为 数组
        cursor = myConfig.cursor()
        try:
            sql = " select customer_name,customer_avatar from customer_details limit 10 ;"
            cursor.execute(sql)
            customers = cursor.fetchall()
            for customer in customers:
                print(customer["customer_name"], customer["customer_avatar"])


        except Exception as e:  # 捕捉异常，并打印
            traceback.print_exc()
        # 关闭数据库连接
        cursor.close()
        myConfig.close()
		return

if __name__ == "__main__":
    main()

优化代码：把SSH数据库连接操作优化成模块，方便使用

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import traceback

import pymysql
from sshtunnel import SSHTunnelForwarder


# 通过SSH链接数据库
class DBSSHHelper:
    # 构造函数,初始化数据库连接
    def __init__(self):
        self.server = None
        self.myConfig = None
        self.cursor = None

    # 连接数据库
    def connection_database(self):
        try:
            self.server = SSHTunnelForwarder(
                ssh_address_or_host=("118.123.123.123", 22),  # ssh跳转机的地址
                ssh_username="xiaoming",  # ssh的用户名
                ssh_pkey="C:/Users/Administrator/.ssh/id_rsa",  # ssh私钥地址
                ssh_private_key_password="",  # ssh私钥密码
                local_bind_address=("0.0.0.0", 10022),
                remote_bind_address=("192.168.0.1", 3306))  # 数据库地址
            self.server.__enter__()
            self.myConfig = pymysql.connect(
                user="userName",  # 数据库登录名
                passwd="password",  # 数据库密码
                host="127.0.0.1",  # 写死
                db="db_name",  # 数据库名称
                port=self.server.local_bind_port,
                cursorclass=pymysql.cursors.DictCursor)  # sql查询结果返回类型：DictCursor 为字典类型， 没有指定为 数组
            self.cursor = self.myConfig.cursor()
        except Exception as e:  # 捕捉异常，并打印
            traceback.print_exc()
            return False
        return True

    # 关闭数据库
    def close_database(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.cursor:
            self.cursor.close()
        if self.myConfig:
            self.myConfig.close()
        if self.server:
            self.server.__exit__()
        return True

    # 查询数据,返回集合
    def select_all(self, sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        return result

    # 查询数据，返回单个
    def select_one(self, sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result


if __name__ == "__main__":
    db_helper = DBSSHHelper()
    db_helper.connection_database()

    result = db_helper.select_all("select * from client_customers limit 10")
    print(result)

    result = db_helper.select_one("select * from client_customers where id=131")
    print(result)
    db_helper.close_database()

扩展：通过Ip直连数据库

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import traceback

import pymysql


def main():
    # 打开数据库连接
    db = pymysql.connect(
        user="userName",  # 数据库登录名
        passwd='password',  # 数据库密码
        host="192.168.1.111",  # 数据库ip
        db='db_name',  # 熟即可名称
        charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    try:
        sql = " select customer_name,customer_avatar from customer_details ;"
        cursor.execute(sql)
        customers = cursor.fetchall()
        for customer in customers:
            print(customer["customer_name"], customer["customer_avatar"])

    except Exception as e:  # 捕捉异常，并打印
        traceback.print_exc()
    db.commit()
    db.close()
    return


if __name__ == "__main__":
    main()