python3 Operation of Connecting mysql Using ssh Tunnel

I won't talk too much, let's just look at the code ~

import pymysql
from sshtunnel import SSHTunnelForwarder
import pymysql.cursors # With dict Formal output 

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 Server address 
 ssh_port = 22     #SSH Port 
 keyfile = xxxx.key" #SSH Key 
 ssh_user = "root"   #SSH User name 
 db_host = "127.0.0.1"  # Database address 
 db_name = 'DBname'    # Database name 
 sql = 'show tables;'  #SQL
 db_port = 3306     # Database port 
 db_user = 'root'    # Database user name 
 db_passwd = '33333'   # Database password 
 result = dbconnect_ssh(ssh_host,ssh_port,keyfile,ssh_user,db_host,db_name,sql,db_port,db_user,db_passwd)
 print (result)

Additional knowledge: Python uses SSHTunnel to connect to the intranet mysql database

Prepare:

Main modules sshtunnel, pip install sshtunnel

Other modules pymysql, playhouse and configparser

Introduction:

Here is the database connection pool and automatic link disconnection reconnection mechanism, in fact, the most important is the establishment of sshtunner, so you can only look at the establishment of service

Profile:

[mysql]
database=ad_insight
max_connections=10
stale_timeout=1000
host=localhost
user= Database user name 
password= Database password 
port=3306

python code

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 Domain name or address ',
    ssh_port=ssh Port ,
    ssh_password='ssh Password ',
    ssh_username='ssh Name ',
    remote_bind_address=(' Database address ', Database port )
 
   )
   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

In fact, it is mainly in the establishment of server objects and server. start