封装带SSH跳板机的MYSQL

一、封装带SSH跳板机的MYSQL

二、配置settting

 1 import pymysql
 2 from sshtunnel import SSHTunnelForwarder
 3 
 4 class MyDb(object):
 5     #mysql类方法
 6     def __del__(self):
 7         self.cur.close()
 8         self.coon.close()
 9         self.server.close()
10 
11     def __init__(self,host,ssh_username,ssh_password,ip,db,sql_username,password):
12         try:
13             server = SSHTunnelForwarder(
14                 ssh_address_or_host=(host, 22),
15                 ssh_username=ssh_username,
16                 ssh_password=ssh_password,
17                 remote_bind_address=(ip, 3306)
18             )
19             self.server = server
20             self.server.daemon_forward_servers = True #不加server关闭不了
21             self.server.start()
22             self.connect = pymysql.connect(
23                 host='127.0.0.1',user=sql_username,passwd=password,port=self.server.local_bind_port,charset='utf8',db=db,
24                 autocommit=True,
25             )
26 
27         except Exception as e:
28             print('mysql连接失败，错误信息%s'%e)
29 
30         else:
31             self.cursor = self.connect.cursor(cursor=pymysql.cursors.DictCursor)
32 
33 
34     def ex_sql(self,sql,many=True):
35         try:
36             self.cursor.execute(sql)
37         except Exception as e:
38             print('sql语句有问题，%s'%sql)
39         else:
40             if many:
41                 result = self.cursor.fetchall()
42             else:
43                 result = self.cursor.fetchone()
44             return result
45 
46 
47 #哪里用哪里实例化吧
48 # my_sql = MyDb(**setting.MYSQL_INFO)
49 #直接在这里实例化的话，用的时候，直接导入就ok了

ssh_mysql

 1 import os
 2 import nnlog
 3 BASE_PATH = os.path.dirname(
 4     os.path.dirname(os.path.abspath(__file__))
 5 )
 6 
 7 # MAIL_HOST='smtp.qq.com'
 8 MAIL_HOST='smtp.exmail.qq.com'
 9 MAIL_USER='xxxx.com'
10 MAIL_PASSWRD='xxxxx'
11 
12 
13 TO = ['xxxx.com'
14 ]
15 
16 
17 LEVEL = 'debug' #日志级别
18 
19 LOG_PATH = os.path.join(BASE_PATH,'logs')
20 CASE_PATH = os.path.join(BASE_PATH,'cases')
21 YAML_PATH = os.path.join(BASE_PATH,'case_data')
22 CASE_TEMPLATE = os.path.join(BASE_PATH,'conf','base.txt') #用例模板的路径
23 REPORT_PATH = os.path.join(BASE_PATH,'report')
24 DATA_PATH = os.path.join(BASE_PATH,'data') #存放测试用到数据的目录
25 BASE_URL = 'https://xxx.xxx.xxx.xx' #线上环境
26 #BASE_URL = 'http://xxx.xxx.xxx.xx' #开发环境
27 
28 LOG_NAME='xxx.log' #日志的文件名
29 
30 log  = nnlog.Logger(os.path.join(LOG_PATH,LOG_NAME),'debug') #定义好日志
31 
32 #dev环境redis配置文件
33 REDIS_dev = {
34     'host': 'xxx.xxx.xxx.xxx',
35     'ip' : 'xxx.xxx.xxx.xxx',
36     'ssh_username' : 'xxx',
37     'ssh_password': 'xxx',
38     'db':2,
39     'password' : 'xxx'
40 }
41 
42 #线上环境redis配置文件
43 REDIS_online = {
44     'host': 'xxx.xxx.xxx.xxx',
45     'ip' : 'xx.xxx.xxx.xxx',
46     'ssh_username' : 'xxx',
47     'ssh_password': 'xxxx',
48     'db':2,
49     'password' : 'xxx'
50 }
51 
52 #dev环境mysql配置文件
53 MYSQL_dev  = {
54     'host': 'xxx.xxx.xxx.xxx',
55     'ip' : 'xx.xx.xx.xx',
56     'ssh_username' : 'xxx',
57     'ssh_password': 'xxx',
58     'db':'xxx',
59     'sql_username':'xx',
60     'password' : 'xxx'
61     }
62 
63 #线上环境mysql配置文件
64 MYSQL_online  = {
65     'host': 'xxx.xxx.xxx.xxx',
66     'ip' : '172.xx.xx.xx',
67     'ssh_username' : 'xxx',
68     'ssh_password': 'xxxx',
69     'db':'xxx',
70     'sql_username':'xxx',
71     'password' : 'xxx'
72     }

setting设置