python连接mysql、ssh隧道连接数据库

python连接mysql数据库，普通连接数据库、ssh隧道连接数据库、redis连接

一、文件目录

二、单个文件内容

2.1 config.py文件

import os
import sys
import configparser


currDir = os.path.dirname(os.path.realpath(__file__))
# os.path.abspath(path)获取绝对路径
rootDir = os.path.abspath(os.path.join(currDir, "."))
if rootDir not in sys.path:
    sys.path.append(rootDir)

cf = configparser.ConfigParser()
sec = cf.sections()
cf.read(os.path.join(rootDir, "config.conf"))


class Config:
    # ---------------- 项目根目录 --------------------
    BASE_PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

    # ---------------- 数据库配置 --------------------
    IP = "替换ip"
    PORT = 3306
    USER = "替换名"
    PASSWORD = "替换密码"
    NAME = "替换数据库名"
    CHARSET = "utf8（替换，一般是utf8）"

    # ---------------- ssh配置 --------------------
    SSH_USER = "替换对应名"
    SSH_PASSWORD = "替换对应密码"   
    SSH_PORT = 22
    remote_IP = "127.0.0.1"
    # ---------------- redis配置 --------------------
    REDIS_PORT = 6379

2.2 pack_mysql.py文件

import pymysql
import logging
import redis
from sshtunnel import SSHTunnelForwarder

from config.config import Config


class DB:
    # ssh连接/通过ssh隧道连接数据库
    def ssh_connect(self):
        server = SSHTunnelForwarder(
            ssh_address_or_host=(Config.IP, Config.SSH_PORT),
            ssh_username=Config.SSH_USER,
            ssh_password=Config.SSH_PASSWORD,
            remote_bind_address=(Config.remote_IP, Config.PORT),
        )
        # 启动隧道服务
        server.start()
        try:
            mysql_config = {
                "user": Config.USER,
                "passwd": Config.PASSWORD,
                "host": Config.remote_IP,
                "port": server.local_bind_port,
                "db": Config.NAME,
            }
            # 连接数据库
            mysql = pymysql.connect(**mysql_config)
            print("数据库连上了")
            return mysql
        except pymysql.connector.Error as error:
            logging.error("连接数据库时出错: {}".format(error))
            return None
        finally:
            mysql.close()

    # redis连接
    def redis_connect(self):
        server = SSHTunnelForwarder(
            ssh_address_or_host=(eval(Config.IP), Config.SSH_PORT),
            ssh_username=Config.SSH_USER,
            ssh_password=Config.SSH_PASSWORD,
            remote_bind_address=(Config.remote_IP, Config.REDIS_PORT),
        )
        # 启动隧道服务
        server.start()
        r = redis.Redis(
            host=Config.remote_IP, port=server.local_bind_port, decode_responses=True
        )
        response = r.ping()
        print(response)
        if response:
            print("Redis connection successful.")
        else:
            print("Failed to connect to Redis.")
        return r

    # 普通连接
    def db_connect(self):
        """获取数据库中的数据"""
        DB_HOST = Config.IP
        # DB_HOST = Config.master_ip
        DB_PORT = Config.PORT
        DB_USER = Config.USER
        DB_PASSWORD = Config.PASSWORD
        DB_NAME = Config.NAME
        DB_CHARSET = Config.CHARSET
        try:
            mysql_client = pymysql.connect(
                host=DB_HOST,
                user=DB_USER,
                passwd=DB_PASSWORD,
                db=DB_NAME,
                port=DB_PORT,
                charset=DB_CHARSET,
            )
            return mysql_client
        except pymysql.connector.Error as error:
            logging.error("连接数据库时出错: {}".format(error))
            return None

2.3 test.py文件

from packs.pack_mysql import DB

base = DB()
base.ssh_connect()

三、执行结果