python 连接操作MySQL数据库

安装依赖

pip install mysql-connector-python

自定义公共管理类

import mysql.connector
from mysql.connector import Error


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

    def connect(self):
        """建立到MySQL数据库的连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password
            )
            if self.connection.is_connected():
                print("成功连接到数据库")
        except Error as e:
            print(f"连接失败: {e}")

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")

    def execute_query(self, sql, params=None):
        """执行不需要返回结果的查询（如插入、更新、删除）"""
        cursor = None
        try:
            self.connect()
            cursor = self.connection.cursor()
            cursor.execute(sql, params)
            self.connection.commit()
            print("查询成功执行")
        except Error as e:
            print(f"查询执行失败: {e}")
        finally:
            if cursor:
                cursor.close()
            self.disconnect()

    def execute_many(self, sql, params=None):
        """执行不需要返回结果的查询（如插入、更新、删除）"""
        cursor = None
        try:
            self.connect()
            cursor = self.connection.cursor()
            cursor.executemany(sql, params)
            self.connection.commit()
            print("查询成功执行")
        except Error as e:
            print(f"查询执行失败: {e}")
        finally:
            if cursor:
                cursor.close()
            self.disconnect()

    def fetch_data(self, query_sql, params=None):
        """执行查询并获取所有数据"""
        cursor = None
        results = None
        try:
            self.connect()
            cursor = self.connection.cursor(dictionary=True)
            print("SQL:", query_sql)
            print("PARAMS:", params)
            cursor.execute(query_sql, params)
            results = cursor.fetchall()
        except Error as e:
            print(f"数据获取失败: {e}")
        finally:
            if cursor:
                cursor.close()
            self.disconnect()
        return results



# 使用示例
if __name__ == "__main__":
    db_config = {
        'host': '127.0.0.1',
        'database': 'test_database',
        'user': 'your_username',
        'password': 'your_password'
    }

    # 创建数据库对象
    db = MySQLDatabase(**db_config)