import pymysql.cursors


class MySQLHelper:
    myVersion = 0.1

    def __init__(self, host, port, db, user, password, charset="utf8"):
        self.host = host
        self.user = user
        self.port = port
        self.password = password
        self.charset = charset
        self.db = db

        try:
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password,
                                        db=self.db, charset=self.charset, cursorclass=pymysql.cursors.DictCursor)
            self.cursor = self.conn.cursor()

            # 设置执行时间为8小时
            sql = "set session wait_timeout=288000"
            self.cursor.execute(sql)
            sql = "set session interactive_timeout=288000"
            self.cursor.execute(sql)

        except Exception as e:
            print('MySql Error : %d %s' % (e.args[0], e.args[1]))

    def query(self, sql: object) -> object:
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            print('MySql Error: %s SQL: %s' % (e, sql))

    def execute(self, sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('MySql Error: %s SQL: %s' % (e, sql))

    def executeWithPara(self, sql, params):
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
        except Exception as e:
            print('MySql Error: %s SQL: %s' % (e, sql))

    def executemany(self, sql, data):
        try:
            self.cursor.executemany(sql, data)
            self.conn.commit()
        except Exception as e:
            print('MySql Error: %s SQL: %s' % (e, sql))

    def close(self):
        self.cursor.close()
        self.conn.close()