快捷導航

python3 使用ssh隧道連接mysql的操作

更新時間：2020年12月05日 14:55:55 作者：鶯聲門徑

這篇文章主要介紹了python3 使用ssh隧道連接mysql的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

我就廢話不多說了，大家還是直接看代碼吧~

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服務(wù)器地址
 ssh_port = 22     #SSH端口
 keyfile = xxxx.key" #SSH密鑰
 ssh_user = "root"   #SSH用戶名
 db_host = "127.0.0.1"  #數(shù)據(jù)庫地址
 db_name = 'DBname'    #數(shù)據(jù)庫名
 sql = 'show tables;'  #SQL
 db_port = 3306     #數(shù)據(jù)庫端口
 db_user = 'root'    #數(shù)據(jù)庫用戶名
 db_passwd = '33333'   #數(shù)據(jù)庫密碼
 result = dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd)
 print (result)

補充知識：Python 使用SSHTunnel 連接內(nèi)網(wǎng)mysql數(shù)據(jù)庫

準備：

主要模塊 sshtunnel， pip install sshtunnel

其余模塊 pymysql，playhouse，configparser

簡介：

這里用的是數(shù)據(jù)庫連接池和自動的鏈接斷開重連機制，其實最主要的就是sshtunner的建立，所以可以只看service建立的部分

配置文件：

[mysql]
database=ad_insight
max_connections=10
stale_timeout=1000
host=localhost
user=數(shù)據(jù)庫用戶名
password=數(shù)據(jù)庫密碼
port=3306

python 代碼

from playhouse.pool import PooledMySQLDatabase
from playhouse.shortcuts import ReconnectMixin
from configparser import ConfigParser
from sshtunnel import SSHTunnelForwarder
 
class RetryMySQLDatabase(ReconnectMixin,PooledMySQLDatabase):
 _instance = None
 
 @staticmethod
 def get_db_instance():
  if not RetryMySQLDatabase._instance:
   server = SSHTunnelForwarder(
    ssh_address_or_host='ssh域名或者地址',
    ssh_port=ssh端口,
    ssh_password='ssh密碼',
    ssh_username='ssh名稱',
    remote_bind_address=('數(shù)據(jù)庫地址',數(shù)據(jù)庫端口)
 
   )
   server.start()
   config = ConfigParser()
   config.read("./default.cfg",encoding="utf-8")
   RetryMySQLDatabase._instance = RetryMySQLDatabase(
    str(config['mysql']['database']),
    max_connections=int(config['mysql']['max_connections']),
    stale_timeout=int(config['mysql']['stale_timeout']),
    host=str(config['mysql']['host']),
    user=str(config['mysql']['user']),
    password=str(config['mysql']['password']),
    port=server.local_bind_port
    # port=int(config['mysql']['port'])
   )
  return RetryMySQLDatabase._instance

其實主要是在server對象的建立和server.start

希望能給大家一個參考，本人親測有效。也希望大家多多支持腳本之家。

您可能感興趣的文章: