PYQT6——实现日期选择查询通过tableWidget显示

直接上代码

import sys

import pymysql
from PyQt6 import QtWidgets, QtGui, QtCore
from PyQt6.QtCore import QDate
from PyQt6.QtWidgets import QMainWindow, QMessageBox, QTableWidget, QPushButton, QVBoxLayout, QWidget, QApplication, \
    QTableWidgetItem


class DatabaseManager:
    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 = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database
        )
        self.cursor = self.connection.cursor()

    def close(self):
        if self.cursor:
            self.cursor.close()
            self.cursor = None
        if self.connection:
            self.connection.close()
            self.connection = None

    def according_to_the_time(self, start_time, end_time):
        self.connect()
        sql = ("""  
                SELECT * FROM photo   
                WHERE STR_TO_DATE(date, '%%Y-%%m-%%d') >= STR_TO_DATE(%s, '%%Y-%%m-%%d')   
                AND STR_TO_DATE(date, '%%Y-%%m-%%d') <= STR_TO_DATE(%s, '%%Y-%%m-%%d')
                ORDER BY STR_TO_DATE(date, '%%Y-%%m-%%d_%%H-%%i-%%s') DESC; 
                """)
        try:
            self.cursor.execute(sql, (start_time, end_time))
            results = self.cursor.fetchall()
            if results:
                return results
            else:
                return None
        except pymysql.MySQLError as e:
            print(f"查询出错: {e}")
            return None
        finally:
            self.close()


class MainWindow(QMainWindow):  
    def __init__(self):
        super().__init__()
        # 设置日期
        self.today = QDate.currentDate()

        # 数据库初始化
        # 输入自己的host，用户，密码，数据库
        self.db_manager = DatabaseManager("", "", "", "")

        self.table_widget = QTableWidget()
        self.button = QPushButton("查询")

        self.dateEdit_2 = QtWidgets.QDateEdit()
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Policy.Preferred,
                                           QtWidgets.QSizePolicy.Policy.Preferred)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.dateEdit_2.sizePolicy().hasHeightForWidth())
        self.dateEdit_2.setSizePolicy(sizePolicy)
        self.dateEdit_2.setCursor(QtGui.QCursor(QtCore.Qt.CursorShape.PointingHandCursor))
        self.dateEdit_2.setInputMethodHints(QtCore.Qt.InputMethodHint.ImhPreferNumbers)
        self.dateEdit_2.setCurrentSection(QtWidgets.QDateTimeEdit.Section.YearSection)
        # 是否弹出日历选择器，表示弹出
        self.dateEdit_2.setCalendarPopup(True)
        self.dateEdit_2.setObjectName("dateEdit_2")
        self.dateEdit_2.setDate(self.today)

        self.dateEdit = QtWidgets.QDateEdit()
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Policy.Preferred,
                                           QtWidgets.QSizePolicy.Policy.Preferred)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.dateEdit.sizePolicy().hasHeightForWidth())

        self.dateEdit.setSizePolicy(sizePolicy)
        self.dateEdit.setCursor(QtGui.QCursor(QtCore.Qt.CursorShape.PointingHandCursor))
        self.dateEdit_2.setInputMethodHints(QtCore.Qt.InputMethodHint.ImhPreferNumbers)
        self.dateEdit_2.setCurrentSection(QtWidgets.QDateTimeEdit.Section.YearSection)
        # 是否弹出日历选择器，表示弹出
        self.dateEdit.setCalendarPopup(True)
        self.dateEdit.setObjectName("dateEdit")
        self.dateEdit.setDate(self.today)
        # 设置布局
        layout = QVBoxLayout()
        layout.addWidget(self.table_widget)
        layout.addWidget(self.button)
        layout.addWidget(self.dateEdit_2)
        layout.addWidget(self.dateEdit)

        container = QWidget()
        container.setLayout(layout)
        self.setCentralWidget(container)
        # 添加提示窗口
        self.msg_box = QMessageBox()
        # 事件触发
        self.button.clicked.connect(self.date_query)

    def date_query(self):
        """
        获取日历控件中选择的日期，并调用数据库查询方法，获取该日期范围内的数据，并更新表格控件
        @return: 无返回值
        """
        start_time = self.dateEdit_2.date().toString("yyyy-MM-dd")
        end_time = self.dateEdit.date().toString("yyyy-MM-dd")

        if start_time and end_time and start_time <= end_time:
            data = self.db_manager.according_to_the_time(start_time, end_time)
            if data is None:
                self.msg_box.setText(f"时间段 {start_time, end_time} 内没有照片记录")
                self.msg_box.exec()
            else:
                # 根据查询到的数据条数量设置表格数量
                self.table_widget.setRowCount(len(data))
                # 设置表格列数
                self.table_widget.setColumnCount(3)
                self.table_widget.setHorizontalHeaderLabels(['照片ID', '摄像头ID', '日期'])

                for i, row in enumerate(data):
                    for j, item in enumerate(row):
                        self.table_widget.setItem(i, j, QTableWidgetItem(str(item)))
        else:
            self.msg_box.setText("开始时间不能晚于结束时间")
            self.msg_box.exec()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    main_window = MainWindow()
    main_window.show()
    sys.exit(app.exec())

点击选择日期，进行日期查询，点击查获得信息，效果如下：