python操作mysql的工具类

#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : new_work 
@file : MysqlHelper.py
@Author : Administrator
@time : 2022/8/1 20:25
@func :
"""

import pymysql

# conn_config = {
#     "host":'localhost',
#     "port":3306,
#     "user":'root',
#     "password":'123456',
#     "database":'world',
#     "charset":'utf8',
#     "unix_socket":None
# }

class MysqlHelper(object):

    def __init__(self,database,host="localhost",port=3306,user="root",password="123456",charset="utf8"):
        """
        初始化Helper对象
        :param database:数据库
        :param host:连接主机
        :param port:端口号
        :param user:用户名
        :param password:密码
        :param charset:字符集
        """
        self.__host = host
        self.__port = port
        self.__user = user
        self.__password = password
        self.__database = database
        self.__charset = charset

    def __get_conn_cursor(self):
        """
        创建连接，获取游标
        :return:
        """
        conn = pymysql.connect(
            host = self.__host,
            port = self.__port,
            user = self.__user,
            passwd = self.__password,
            db = self.__database,
            charset = self.__charset
        )
        # 创建游标对象
        cursor = conn.cursor()
        return (conn,cursor)

    def __close(self,conn,cursor):
        """
        关闭游标，关闭连接
        :param conn:
        :param cursor:
        :return:
        """
        cursor.close()
        conn.close()

    def fetchall(self, sql,args = None):
        """
        查询所有数据
        :param sql:
        :param args:
        :return:
        """
        try:
            (conn, cursor) = self.__get_conn_cursor()
            cursor.execute(sql,args)
            results = cursor.fetchall()
            return results
        finally:
            self.__close(conn,cursor)

    def fetchone(self, sql,args = None):
        """
        查询单个值
        :param sql:
        :return:
        """
        try:
            (conn, cursor) = self.__get_conn_cursor()
            cursor.execute(sql,args)
            results = cursor.fetchone()
            return results
        finally:
            self.__close(conn,cursor)

    def execute(self,sql,args):
        """
        执行命令
        :param sql:
        :param args:
        :return:
        """
        if not sql:
            return -1
        try:
            effectRow = 0
            (conn, cursor) = self.__get_conn_cursor()
            cursor.execute(sql,args)
            effectRow += cursor.rowcount
            conn.commit()
            return effectRow
        except Exception as ex:
            conn.rollback()
            raise ex
        finally:
            self.__close(conn, cursor)

    def executemany(self,sql,args):
        """
        传入sql语句，以及多组 对应值
        :param sql:
        :return:受影响行数
        """
        if not sql:
            return -1
        try:
            effectRow = 0
            (conn, cursor) = self.__get_conn_cursor()
            cursor.executemany(sql,args)
            effectRow += cursor.rowcount
            conn.commit()
            return effectRow
        except Exception as ex:
            conn.rollback()
            raise ex
        finally:
            self.__close(conn, cursor)

    def __exec_insert(self,sql,args):
        """
        插入数据，返回 lastrowid
        :param sql:
        :param args:
        :return:
        """
        if not sql:
            return -1
        try:
            (conn, cursor) = self.__get_conn_cursor()
            cursor.execute(sql,args)
            conn.commit()
            return cursor.lastrowid
        except Exception as ex:
            conn.rollback()
            raise ex
        finally:
            self.__close(conn, cursor)

    def insert(self,table_name,**kwargs):
            """
            根据表名，字典 插入数据
            :param table_name:数据库表名
            :param kwargs:字典
            :return:受影响行数
            """
            insert_sql = "insert into {table_name}({column_names})values({column_values})"
        # if type(kwargs).__name__ == "dict":
            insert_sql = insert_sql.format(table_name=table_name, column_names=",".join(list(kwargs.keys())),
                                       column_values=("%s," * len(kwargs)).strip(","))
            return self.__exec_insert(insert_sql,list(kwargs.values()))
        # else:
        #     raise ValueError(f"未传入字典类型对象。{kwargs}")

    def insert_dict(self, table_name, dict):
        return self.insert(table_name,**dict)

    def insert_obj(self,obj):
        """
        插入对象
        :param obj:对象值，对象名称必须与数据库表名称相同
        :return:受影响行数
        """
        obj_dict = obj.__dict__
        table_name = obj.__class__.__name__
        return self.insert_dict(table_name,obj_dict)

    def is_exist_table(self,table_name):
        """
        查询数据库中是否存在某张表
        :param table_name:表名称
        :return:返回 Boolean 类型
        """
        sel_sql = f"select table_name from INFORMATION_SCHEMA.TABLES where lower(table_type)='base table' and  lower(table_schema) = '{self.__database.lower()}' and lower(table_name) ='{str(table_name).lower()}'"
        res = self.fetchone(sel_sql)
        return res != None

    def is_exist_column(self,table_name,column_name):
        sel_sql = f"select column_name from information_schema.COLUMNS where lower(table_schema) = '{self.__database.lower()}' and lower(table_name) = '{table_name.lower()}' and lower(column_name)='{str(column_name).lower()}'"
        res = self.fetchone(sel_sql)
        return res != None
        
        

if __name__ == "__main__":

    helper = MysqlHelper(database="ground_boot",host="192.168.43.131",port=3306,user="root",password="123456")

    # kwargs 参数插入
    # res = helper.insert("test",name="zhangsan",age=21, job="医生")
    # print("kwargsgs 参数插入,执行成功：",res)

    #字典 参数插入
    # dc1 = {"name":"lisi","age":22,"job":"教师"}
    dc1 = { "user_code":23,
            "user_name":"lisi",
            "password":"lisi",
            "create_by":"lisi",
            "create_date":"2022-07-27 21:54:31.0",
            "update_by":"lisi",
            "update_date":"2022-07-27 21:54:31.0"
           }
    res1 = helper.insert_dict("users_test",dc1)
    print("字典 参数插入,执行成功：",res1)

    #对象 参数插入
    # dc2 = test('wangwu', 23, '软件工程师')
    # res2 = helper.insert_obj(dc2)
    # print("对象 参数插入,执行成功：",res2)

    print("-------------------------------------------------------------")

    is_exist = helper.is_exist_table("tt")
    print(is_exist)

    is_exist_col = helper.is_exist_column("tt","job")
    print(is_exist_col)

    print("-------------------------------------------------------------")