import pymysql

 from sshtunnel import SSHTunnelForwarder

 class MyDb(object):

     #mysql类方法

     def __del__(self):

         self.cur.close()

         self.coon.close()

         self.server.close()

     def __init__(self,host,ssh_username,ssh_password,ip,db,sql_username,password):

         try:

             server = SSHTunnelForwarder(

                 ssh_address_or_host=(host, 22),

                 ssh_username=ssh_username,

                 ssh_password=ssh_password,

                 remote_bind_address=(ip, 3306)

             )

             self.server = server

             self.server.daemon_forward_servers = True #不加server关闭不了

             self.server.start()

             self.connect = pymysql.connect(

                 host='127.0.0.1',user=sql_username,passwd=password,port=self.server.local_bind_port,charset='utf8',db=db,

                 autocommit=True,

             )

         except Exception as e:

             print('mysql连接失败，错误信息%s'%e)

         else:

             self.cursor = self.connect.cursor(cursor=pymysql.cursors.DictCursor)

     def ex_sql(self,sql,many=True):

         try:

             self.cursor.execute(sql)

         except Exception as e:

             print('sql语句有问题，%s'%sql)

         else:

             if many:

                 result = self.cursor.fetchall()

             else:

                 result = self.cursor.fetchone()

             return result

 #哪里用哪里实例化吧

 # my_sql = MyDb(**setting.MYSQL_INFO)

 #直接在这里实例化的话，用的时候，直接导入就ok了

 import os

 import nnlog

 BASE_PATH = os.path.dirname(

     os.path.dirname(os.path.abspath(__file__))

 )

 # MAIL_HOST='smtp.qq.com'

 MAIL_HOST='smtp.exmail.qq.com'

 MAIL_USER='xxxx.com'

 MAIL_PASSWRD='xxxxx'

 TO = ['xxxx.com'

 ]

 LEVEL = 'debug' #日志级别

 LOG_PATH = os.path.join(BASE_PATH,'logs')

 CASE_PATH = os.path.join(BASE_PATH,'cases')

 YAML_PATH = os.path.join(BASE_PATH,'case_data')

 CASE_TEMPLATE = os.path.join(BASE_PATH,'conf','base.txt') #用例模板的路径

 REPORT_PATH = os.path.join(BASE_PATH,'report')

 DATA_PATH = os.path.join(BASE_PATH,'data') #存放测试用到数据的目录

 BASE_URL = 'https://xxx.xxx.xxx.xx' #线上环境

 #BASE_URL = 'http://xxx.xxx.xxx.xx' #开发环境

 LOG_NAME='xxx.log' #日志的文件名

 log  = nnlog.Logger(os.path.join(LOG_PATH,LOG_NAME),'debug') #定义好日志

 #dev环境redis配置文件

 REDIS_dev = {

     'host': 'xxx.xxx.xxx.xxx',

     'ip' : 'xxx.xxx.xxx.xxx',

     'ssh_username' : 'xxx',

     'ssh_password': 'xxx',

     'db':2,

     'password' : 'xxx'

 }

 #线上环境redis配置文件

 REDIS_online = {

     'host': 'xxx.xxx.xxx.xxx',

     'ip' : 'xx.xxx.xxx.xxx',

     'ssh_username' : 'xxx',

     'ssh_password': 'xxxx',

     'db':2,

     'password' : 'xxx'

 }

 #dev环境mysql配置文件

 MYSQL_dev  = {

     'host': 'xxx.xxx.xxx.xxx',

     'ip' : 'xx.xx.xx.xx',

     'ssh_username' : 'xxx',

     'ssh_password': 'xxx',

     'db':'xxx',

     'sql_username':'xx',

     'password' : 'xxx'

     }

 #线上环境mysql配置文件

 MYSQL_online  = {

     'host': 'xxx.xxx.xxx.xxx',

     'ip' : '172.xx.xx.xx',

     'ssh_username' : 'xxx',

     'ssh_password': 'xxxx',

     'db':'xxx',

     'sql_username':'xxx',

     'password' : 'xxx'

     }