JasonPhdWork/server/flaskProject/data.py

import mysql.connector


class MySQLTool:
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = mysql.connector.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database
        )
        self.cursor = self.connection.cursor()

    def disconnect(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def select(self, table, columns='*', conditions=None):
        query = f"SELECT {columns} FROM {table}"
        if conditions:
            query += " WHERE " + " AND ".join([f"{key} = %s" for key in conditions.keys()])
            values = tuple(conditions.values())
            self.cursor.execute(query, values)
        else:
            self.cursor.execute(query)
        result = self.cursor.fetchall()
        return result

    def insert(self, table, columns, values):
        query = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['%s' for _ in columns])})"
        self.cursor.execute(query, values)
        self.connection.commit()
        affected_rows = self.cursor.rowcount
        return affected_rows

    def update(self, table, set_values, conditions=None):
        query = f"UPDATE {table} SET " + ", ".join([f"{key} = %s" for key in set_values.keys()])
        values = tuple(set_values.values())
        if conditions:
            query += " WHERE " + " AND ".join([f"{key} = %s" for key in conditions.keys()])
            values += tuple(conditions.values())
        self.cursor.execute(query, values)
        self.connection.commit()
        affected_rows = self.cursor.rowcount
        return affected_rows

    def delete(self, table, conditions=None):
        query = f"DELETE FROM {table}"
        if conditions:
            query += " WHERE " + " AND ".join([f"{key} = %s" for key in conditions.keys()])
            values = tuple(conditions.values())
            self.cursor.execute(query, values)
        else:
            self.cursor.execute(query)
        self.connection.commit()
        affected_rows = self.cursor.rowcount
        return affected_rows