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)

事务级处理文本编辑器

db_config = {
        'host': '127.0.0.1',
        'database': 'test_database',
        'user': 'your_username',
        'password': 'your_password'
    }

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


class MySQLDatabase(object):

    def __init__(self, log_time=True):
        self._log_time = log_time

    def __enter__(self):
        # 如果需要记录时间
        if self._log_time:
            self._start = time.time()

        # 在进入的时候自动获取连接和cursor
        conn = connect(**db_config)
        conn.autocommit = False
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        try:
            if exc_info[0] is None:
                self._conn.commit()
        except Exception as e:
            self._conn.rollback()
            print(f"Commit failed with error: {e}")
        finally:
            self._cursor.close()
            self._conn.close()
            if self._log_time:
                diff = time.time() - self._start
                # print(f'-- 用时: {diff:.6f} 秒')

    @property
    def cursor(self):
        return self._cursor

    def exec(self, sql_list):
        try:
            for sql, param in sql_list:
                ans = self._cursor.execute(sql, param)
            last_id = self._cursor.lastrowid
            return True, last_id
        except Exception as e:
            exception_info = {
                'type': type(e).__name__,
                'message': str(e),
            }
            print(f"Commit failed with error: {exception_info}")
            return False, exception_info

    def exec_many(self, sql, params):
        try:
            ans = self._cursor.executemany(sql, params)
            last_id = self._cursor.lastrowid
            return True, last_id
        except Exception as e:
            exception_info = {
                'type': type(e).__name__,
                'message': str(e),
            }
            print(f"Commit failed with error: {exception_info}")
            return False, exception_info

    def exec_manylist(self, sqls, params):
        try:
            for sql, param in zip(sqls, params):
                ans = self._cursor.executemany(sql, param)
                last_id = self._cursor.lastrowid
            return True, last_id
        except Exception as e:
            exception_info = {
                'type': type(e).__name__, 
                'message': str(e),
            }
            print(f"Commit failed with error: {exception_info}")
            return False, exception_info

    def fetch_one(self, sql, params=None):
        try:
            self._cursor.execute(sql, params)
            res = self._cursor.fetchone()
            return True, res
        except Exception as e:
            print("Exception:", e)
            return False, None

    def fetch_all(self, sql, params=None):
        try:
            self._cursor.execute(sql, params)
            res = self._cursor.fetchall()
            return True, res
        except Exception as e:
            print(e)
            return False, None