【Python】Python封装一个操作MySQL的工具类

代码实现

import pymysql

class MySQLTool:
    def __init__(self, host, user, password, database):
        self.connection = pymysql.connect(host=host,
                                          user=user,
                                          password=password,
                                          database=database)
        self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)

    def query(self, sql, params=None):
        self.cursor.execute(sql, params or ())
        return self.cursor.fetchall()

    def insert(self, sql, params=None):
        self.cursor.execute(sql, params or ())
        self.connection.commit()
        return self.cursor.lastrowid

    def update(self, sql, params=None):
        self.cursor.execute(sql, params or ())
        self.connection.commit()
        return self.cursor.rowcount

    def delete(self, sql, params=None):
        self.cursor.execute(sql, params or ())
        self.connection.commit()
        return self.cursor.rowcount

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.connection.close()
        
        
if __name__ == '__main__':
    # 初始化工具类实例
    db_tool = MySQLTool('localhost', 'root', 'password', 'my_database')

    # 查询操作
    result = db_tool.query("SELECT * FROM my_table WHERE id = %s", (1,))
    print(result)

    # 插入操作
    new_id = db_tool.insert("INSERT INTO my_table (name, value) VALUES (%s, %s)", ('name', 'value'))
    print(f"Inserted row ID: {new_id}")

    # 更新操作
    updated_rows = db_tool.update("UPDATE my_table SET value = %s WHERE id = %s", ('new_value', 1))
    print(f"Updated rows: {updated_rows}")

    # 删除操作
    deleted_rows = db_tool.delete("DELETE FROM my_table WHERE id = %s", (1,))
    print(f"Deleted rows: {deleted_rows}")
    
    # 推荐使用以下这种操作方式不用手动去关闭连接
    with MySQLTool('localhost', 'root', 'password', 'my_database') as db_tool:
    	# 这里执行数据库操作
	    result = db_tool.query("SELECT * FROM my_table")
	    print(result)