分享一个封装MySQL操作的半成品

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   test.py
@Time    :   2020/05/18 20:57:15
@Author  :   xuchenliang 
@Version :   1.0
@Desc    :   None
'''


# here put the import lib
import pymysql




class RdsApi(object):
    def __init__(self, host=None, port=None, user=None, password=None, db=None):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db


    def _local_cnx(self):
        cnx = pymysql.connect(host=self.host,
                              port=self.port,
                              user=self.user,
                              passwd=self.password,
                              db=self.db,
                              max_allowed_packet=1024 * 1024 * 1024,
                              charset='utf8',
                              cursorclass=pymysql.cursors.DictCursor)
        # 设置最大查询时间60s
        cnx._read_timeout = 60
        return cnx


    def query(self, sql_content):
        conn = self._local_cnx()
        thread_id = conn.thread_id()
        cursor = conn.cursor()


        try:
            cursor.execute(sql_content)
            result = cursor.fetchall()
            # 获取到thread_id，如果点击页面上的中止，那么连接对应的实例，kill相应的thread_id
            print(thread_id)
            print(result)
        except Exception as err:
            print(err)
        finally:
            conn.close()


    def kill(self, sql_pattern):
        conn = self._local_cnx()
        cursor = conn.cursor()


        cursor.execute("SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE '{}%'".format(sql_pattern))
        thread_dict = cursor.fetchall()


        if thread_dict:
            for thread in thread_dict:
                try:
                    cursor.execute('kill {}'.format(int(thread['id'])))
                    print('kill {} success'.format(thread['id']))
                except Exception as err:
                    print(err)
                    continue
        else:
            print('未匹配到会话')
        conn.close()




if __name__ == "__main__":
    rds = RdsApi(host='127.0.0.1', port=3306, user='xucl', password='xuclxucl123', db='xucl')
    rds.kill('select SLEEP')