利用腾讯 CodeBuddy 构建高效数据库自动化运维平台

pip install pymysql

1. __init__ 方法中没有初始化 connection 属性
2. 直接访问 connection 属性可能导致属性未定义错误
解决方案是：
1. 在 __init__ 中初始化 self.connection = None
2. 确保所有方法正确处理 connection 属性
3. 添加更健壮的连接管理

import pymysql
from pymysql.cursors import DictCursor
from flask import current_app
import logging

class MySQLConnector:
    def __init__(self, app):
        self.app = app
        self._connection = None
        self.logger = logging.getLogger(__name__)

    def _get_connection(self, use_config_db=True):
        """获取数据库连接"""
        try:
            if use_config_db:
                return pymysql.connect(
                    host=self.app.config['MYSQL_HOST'],
                    port=self.app.config['MYSQL_PORT'],
                    user=self.app.config['MYSQL_USER'],
                    password=self.app.config['MYSQL_PASSWORD'],
                    database=self.app.config['MYSQL_DATABASE'],
                    charset='utf8mb4',
                    cursorclass=DictCursor
                )
            else:
                # 用于初始化时连接，不指定数据库
                return pymysql.connect(
                    host=self.app.config['MYSQL_HOST'],
                    port=self.app.config['MYSQL_PORT'],
                    user=self.app.config['MYSQL_USER'],
                    password=self.app.config['MYSQL_PASSWORD'],
                    charset='utf8mb4',
                    cursorclass=DictCursor
                )
        except pymysql.Error as e:
            self.logger.error(f"数据库连接失败: {str(e)}")
            raise
        except Exception as e:
            self.logger.error(f"未知连接错误: {str(e)}")
            raise

    def save_config(self, name, config):
        """保存数据库配置"""
        conn = None
        try:
            conn = self._get_connection(use_config_db=True)
            with conn.cursor() as cursor:
                sql = """
                    INSERT INTO db_configs (name, host, port, username, pwd, db_name)
                    VALUES (%s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        host = VALUES(host),
                        port = VALUES(port),
                        username = VALUES(username),
                        pwd = VALUES(pwd),
                        db_name = VALUES(db_name)
                """
                cursor.execute(sql, (
                    name,
                    config['host'],
                    config['port'],
                    config['user'],  # 外部参数名仍为user
                    config['password'],  # 外部参数名仍为password
                    config['database']   # 外部参数名仍为database
                ))
                conn.commit()
                return True
        except Exception as e:
            self.logger.error(f"保存配置失败: {str(e)}")
            raise e
        finally:
            if conn:
                conn.close()

    def get_configs(self):
        """获取所有配置"""
        conn = None
        try:
            conn = self._get_connection(use_config_db=True)
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT 
                        id, 
                        name, 
                        host, 
                        port, 
                        username as user, 
                        pwd as password, 
                        db_name as database 
                    FROM db_configs
                """)
                return cursor.fetchall()
        except Exception as e:
            self.logger.error(f"获取配置失败: {str(e)}")
            return []
        finally:
            if conn:
                conn.close()

    def get_config(self, name):
        """获取指定配置"""
        conn = None
        try:
            conn = self._get_connection(use_config_db=True)
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT 
                        id, 
                        name, 
                        host, 
                        port, 
                        username as user, 
                        pwd as password, 
                        db_name as database 
                    FROM db_configs 
                    WHERE name = %s
                """, (name,))
                return cursor.fetchone()
        except Exception as e:
            self.logger.error(f"获取配置失败: {str(e)}")
            return None
        finally:
            if conn:
                conn.close()

    # ... [保留其他方法不变] ...

from flask import Blueprint, request, jsonify
from datetime import datetime
from ..database import MySQLConnector
import re

bp = Blueprint('sql', __name__, url_prefix='/api/sql')

@bp.route('/execute', methods=['POST'])
def execute_sql():
    """执行SQL语句API接口"""
    data = request.get_json()
    
    # 输入验证
    if not data or 'sql' not in data:
        return jsonify({'error': '缺少SQL参数'}), 400
    
    sql = data['sql'].strip()
    if not sql:
        return jsonify({'error': 'SQL语句不能为空'}), 400
    
    # 简单的SQL注入检查
    if re.search(r'\b(drop|alter|truncate|grant|revoke)\b', sql, re.I):
        return jsonify({'error': '不允许执行该类型SQL语句'}), 403
    
    # 获取数据库连接
    db = MySQLConnector(current_app)
    
    try:
        start_time = datetime.now()
        
        # 执行SQL
        result = db.execute(sql)
        
        # 计算执行时间
        execution_time = (datetime.now() - start_time).total_seconds()
        result['execution_time'] = execution_time
        
        return jsonify(result)
    except Exception as e:
        return jsonify({
            'error': str(e),
            'execution_time': (datetime.now() - start_time).total_seconds()
        }), 500