python_pymysql

import pymysql
from pymysql import cursors

class SqlUtil:
    conn = None
    cursor = None

    env = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'anjuke'
    }

    @classmethod
    def open(cls):
        try:
            cls.conn = pymysql.connect(host=cls.env['host'], user=cls.env['user'], password=cls.env['password'],
                                       database=cls.env['database'])
            cls.cursor = cls.conn.cursor(cursor=pymysql.cursors.DictCursor)
        except Exception as e:
            print(f"open method connect exception:{e}")

    @classmethod
    def close(cls):
        cls.cursor.close()
        cls.conn.close()

    @classmethod
    def select(cls, sql=None, condition=None):
        cls.open()
        if cls.conn and cls.cursor:
            try:
                cls.cursor.execute(sql, condition)
                result = cls.cursor.fetchall()
                return result
            except Exception as e:
                print(f"select method execute exception:{e}")
                return None
            finally:
                cls.close()
        else:
            return None

    @classmethod
    def update(cls, sql=None, params=None):
        cls.open()
        if cls.conn and cls.cursor:
            try:
                cls.cursor.execute(sql, params)
                cls.conn.commit()
                return cls.cursor.rowcount
            except Exception as e:
                print(f"update method execute exception, sql{sql}, params{params}, e:{e}")
                cls.conn.rollback()
                return 0
            finally:
                cls.close()
        else:
            print(f"连接数据库异常，更新数据时未获取到conn,cursor，sql：{sql}，params_list：{params}")
            return 0

    @classmethod
    def insert(cls, sql=None, params=None):
        cls.open()
        if cls.conn and cls.cursor:
            try:
                cls.cursor.execute(sql, params)
                cls.conn.commit()
                return cls.cursor.rowcount
            except Exception as e:
                print(f"insert method execute exception, sql:{sql}, params:{params}, e:{e}")
                cls.conn.rollback()
                return 0
            finally:
                cls.close()
        else:
            print(f"insert method not get \"conn,cursor\", sql:{sql}, params:{params}")
            return 0

    @classmethod
    def insertMany(cls, sql=None, params_list=None):
        cls.open()
        if cls.conn and cls.cursor:
            try:
                cls.cursor.executemany(sql, params_list)
                cls.conn.commit()
                return cls.cursor.rowcount
            except Exception as e:
                print(f"insetMany method execute exception, sql：{sql}, params:{params_list}, e:{e}")
                cls.conn.rollback()
                return 0
            finally:
                cls.close()
        else:
            print(f"insert method not get \"conn,cursor\", sql:{sql}, params_list:{params_list}")
            return 0