pyqt 操作mysql数据库

 

 

#

import mysql.connector
from datetime import datetime, timezone
from PyQt5.QtCore import pyqtSignal, QObject
import time
import threading

class VC_DB_Tool(QObject):
    signalMsg = pyqtSignal(str)
    
    def __init__(self, host="localhost", port=3306, database="", username="", password=""):
        """
        初始化MySQL工具类
        :param host: 数据库主机地址
        :param port: 数据库端口
        :param database: 数据库名
        :param username: 用户名
        :param password: 密码
        """
        super().__init__()
        self.host = host
        self.port = port
        self.database = database
        self.username = username
        self.password = password
        self.db = None
        self.cursor = None
    #
    def set_DBIP(self,db_ip):
        self.host = db_ip
    #
    def set_DBPort(self,port):
        self.port = port
    #
    #
    def set_DBDatabase(self,database):
        self.database = database
    #
    #
    def set_DBUsername(self,username):
        self.username = username
    #
    def set_DBPassword(self,password):
        self.password = password
    #
    def connect(self):
        """
        连接数据库
        :return: 连接是否成功
        """
        try:
            self.db = mysql.connector.connect(
                host=self.host,
                port=self.port,
                database=self.database,
                user=self.username,
                password=self.password
            )
            self.cursor = self.db.cursor(dictionary=True)
            self.signalMsg.emit("数据库连接成功")
            return True
        except Exception as e:
            self.signalMsg.emit(f"数据库连接失败: {str(e)}")
            return False

    def disconnect(self):
        """
        断开数据库连接
        """
        if self.db and self.db.is_connected():
            self.cursor.close()
            self.db.close()
            self.signalMsg.emit("数据库连接已断开")

    def read_table_data(self, table_name):
        """
        读取数据表所有数据
        :param table_name: 表名
        :return: 查询结果列表
        """
        if not self.db or not self.db.is_connected():
            self.signalMsg.emit("数据库未连接")
            return None

        try:
            query = f"SELECT * FROM {table_name}"
            self.cursor.execute(query)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            self.signalMsg.emit(f"读取表数据失败: {str(e)}")
            return None

    def query_data(self, table_name, conditions=None):
        """
        根据条件查询数据表数据
        :param table_name: 表名
        :param conditions: 查询条件字典，如 {"name": "张三", "age": 25}
        :return: 查询结果列表
        """
        if not self.db or not self.db.is_connected():
            self.signalMsg.emit("数据库未连接")
            return None

        try:
            # 构建SQL语句   
            sql = f"SELECT * FROM {table_name}"
            if conditions:
                where_clause = " AND ".join([f"{key}={conditions[key]}" for key in conditions.keys()])
                sql += f" WHERE {where_clause};"
            self.signalMsg.emit(f"{str(sql)}")
            self.cursor.execute(sql, conditions or {})
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            self.signalMsg.emit(f"条件查询失败: {str(e)}")
            return None

    def get_table_count(self, database_name):
        """
        获取某个数据库的数据表数量
        :param database_name: 数据库名
        :return: 数据表数量
        """
        if not self.db or not self.db.is_connected():
            self.signalMsg.emit("数据库未连接")
            return None

        try:
            query = """
            SELECT COUNT(*) as table_count 
            FROM information_schema.tables 
            WHERE table_schema = %s
            """
            self.cursor.execute(query, (database_name,))
            result = self.cursor.fetchone()
            return result['table_count'] if result else None
        except Exception as e:
            self.signalMsg.emit(f"获取表数量失败: {str(e)}")
            return None

    def check_table_exists(self, table_name):
        """
        检查表是否存在
        :param table_name: 表名
        :return: 是否存在
        """
        if not self.db or not self.db.is_connected():
            self.signalMsg.emit("数据库未连接")
            return False

        try:
            self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
            return self.cursor.fetchone() is not None
        except Exception as e:
            self.signalMsg.emit(f"检查表存在失败: {str(e)}")
            return False
    #
    def extract_date_from_table_name(self,table_name):
        """从表名中提取日期字符串"""
        parts = table_name.split('_')
        if len(parts) >= 4:
            return parts[-1]
        return ""

    def sort_table_names_by_date(self,table_names):
        """根据表名中的日期部分对表名进行排序（从最远到最近）"""
        sorted_tables = sorted(table_names, key=self.extract_date_from_table_name, reverse=False)
        return sorted_tables

    #
    def get_all_table_names(self, database_name=None):
        """
        获取数据库中所有表的名称
        :param database_name: 数据库名，如果为None则使用当前连接的数据库
        :return: 表名列表
        """
        if not self.db or not self.db.is_connected():
            self.signalMsg.emit("数据库未连接")
            return None

        try:
            if database_name is None:
                database_name = self.database
            
            query = """
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = %s
            ORDER BY table_name
            """
            self.cursor.execute(query, (database_name,))
            result = self.cursor.fetchall()
            #
            #print(result)
            # 提取表名列表
            table_names = [row['TABLE_NAME'] for row in result]
            # 按日期排序
            sorted_tables = self.sort_table_names_by_date(table_names)
            return sorted_tables
        except Exception as e:
            self.signalMsg.emit(f"获取表名列表失败: {str(e)}")
            return None
    #
#

 

 

 

#