pyqt6作品，文件搜索辅助工具

开发诉求，1、输入框内容即变即有结果展现

2、路径合集可自由添加删除，添加时会检测是父/子路径并给出提示

3、能展示数据库上次更新时间

4、双击文件名能使用系统方式打开

下面贴上代码及截图

# -*- coding: utf-8 -*-

################################################################################
## Form generated from reading UI file 'Form - untitled.ui'
##
## Created by: Qt User Interface Compiler version 6.2.3
##
## WARNING! All changes made in this file will be lost when recompiling UI file!
################################################################################
from pathlib import Path
import time,os,sqlite3
from PyQt6.QtCore import (QCoreApplication, QDate, QDateTime, QLocale,
    QMetaObject, QObject, QPoint, QRect,
    QSize, QTime, QUrl, Qt,QStringListModel,)
from PyQt6.QtGui import (QBrush, QColor, QConicalGradient, QCursor,
    QFont, QFontDatabase, QGradient, QIcon,
    QImage, QKeySequence, QLinearGradient, QPainter,
    QPalette, QPixmap, QRadialGradient, QTransform,QStandardItemModel,QStandardItem,)
from PyQt6.QtWidgets import (QApplication, QHBoxLayout, QHeaderView, QLabel,
    QLineEdit, QPushButton, QRadioButton, QSizePolicy,
    QTableView, QVBoxLayout, QWidget,QFileDialog,QAbstractItemView,QMessageBox,)
from PyQt6.QtSql import *

class Ui_Form(object):
    def setupUi(self, Form):
        if not Form.objectName():
            Form.setObjectName(u"Form")
        Form.resize(1118, 822)
        self.base_layout = QHBoxLayout(Form)
        self.base_layout.setObjectName(u"base_layout")
        self.left_verticalLayout = QVBoxLayout()
        self.left_verticalLayout.setSpacing(6)
        self.left_verticalLayout.setObjectName(u"left_verticalLayout")
        self.label = QLabel(Form)
        self.label.setObjectName(u"label")
        self.left_verticalLayout.addWidget(self.label)
        self.path_groud_tableView = QTableView(Form)
        self.path_groud_tableView.setObjectName(u"path_groud_tableView")
        self.left_verticalLayout.addWidget(self.path_groud_tableView)
        self.base_layout.addLayout(self.left_verticalLayout)
        self.right_verticalLayout = QVBoxLayout()
        self.right_verticalLayout.setObjectName(u"right_verticalLayout")
        self.function_button_horizontalLayout = QHBoxLayout()
        self.function_button_horizontalLayout.setObjectName(u"function_button_horizontalLayout")
        self.add_path_pushbutton = QPushButton(Form)
        self.add_path_pushbutton.setObjectName(u"add_path_pushbutton")
        self.function_button_horizontalLayout.addWidget(self.add_path_pushbutton)
        self.del_path_pushbutton = QPushButton(Form)
        self.del_path_pushbutton.setObjectName(u"del_path_pushbutton")
        self.function_button_horizontalLayout.addWidget(self.del_path_pushbutton)
        self.update_index_pushButton = QPushButton(Form)
        self.update_index_pushButton.setObjectName(u"update_index_pushButton")
        self.function_button_horizontalLayout.addWidget(self.update_index_pushButton)
        self.radioButton = QRadioButton(Form)
        self.radioButton.setObjectName(u"radioButton")
        self.function_button_horizontalLayout.addWidget(self.radioButton)
        self.keyword_lineEdit = QLineEdit(Form)
        self.keyword_lineEdit.setObjectName(u"keyword_lineEdit")
        self.function_button_horizontalLayout.addWidget(self.keyword_lineEdit)
        self.right_verticalLayout.addLayout(self.function_button_horizontalLayout)
        self.info_label = QLabel(Form)
        self.info_label.setObjectName(u"info_label")
        self.right_verticalLayout.addWidget(self.info_label)
        self.result_tableView = QTableView(Form)
        self.result_tableView.setObjectName(u"result_tableView")
        self.right_verticalLayout.addWidget(self.result_tableView)
        self.base_layout.addLayout(self.right_verticalLayout)
        self.base_layout.setStretch(0, 1)
        self.base_layout.setStretch(1, 3)
        self.retranslateUi(Form)
        QMetaObject.connectSlotsByName(Form)
        self.path_groud_tableView.setSelectionMode(QAbstractItemView.SelectionMode.SingleSelection) #设置选择行为为单个选择
        self.path_groud_tableView.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers) #设置禁止编辑
        self.path_mode = QStandardItemModel()
        self.search_mode=QSqlQueryModel()
        self.search_mode.fetchMore()


        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName("file_info.db")  # addDatabase处指定名称后面的查询就没结果了
        self.query = QSqlQuery(self.db)
        if self.db.open():
            self.query.exec("select path from path_collection")
            while self.query.next():
                self.path_mode.appendRow(QStandardItem(self.query.value(0)))
            self.query.exec("select last_update_time from basic_info where id=0")
            while self.query.next():
                last_update=time.strftime('%Y-%m-%d %H:%M:%S %A',time.localtime(self.query.value(0))) #对浮点的时间戳先处理为time对象，再用它的规格化函数转为字符串格式
                self.info_label.setText(f"上次数据更新时间:{last_update}") #label处显示上次数据库更新时间

        self.path_groud_tableView.setModel(self.path_mode)
        self.result_tableView.setModel(self.search_mode)

        self.path_groud_tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch) #列充满视图，避免字段少时右边空出一大片不美观
        self.result_tableView.setSelectionMode(QAbstractItemView.SelectionMode.SingleSelection)
        self.result_tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch) #列充满视图，避免字段少时右边空出一大片不美观

        #self.result_tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.ResizeToContents)

        self.path_mode.setHorizontalHeaderLabels(['路径集'])
        self.add_path_pushbutton.clicked.connect(lambda x:self.add_path(Form)) #临函传参，实现添加路径在数据库及视图
        self.del_path_pushbutton.clicked.connect(self.del_path) #实现删除路径在数据库及视图
        self.update_index_pushButton.clicked.connect(self.update_db) #实现数据库重置
        self.keyword_lineEdit.textChanged.connect(self.search_db) #lineEdit文本变化信号槽连结对应函数，以实现即时查询
        self.result_tableView.doubleClicked.connect(lambda x:self.open_file(Form)) #双击信号连接使用系统设置打开相关类型文档
    # setupUi

    def add_path(self,Form):#QMessageBox需传入QWidget对象，connect使用临时函数传入
        str_path=QFileDialog.getExistingDirectory()
        if str_path:
            path_path=Path(str_path)
            win_str_path=str(path_path)
            rows=self.path_mode.rowCount()
            querystr = 'insert into path_collection (path) VALUES (?)'

            if rows==0: #Mode为空时不需判断父子，直接添加Row
                self.path_mode.appendRow(QStandardItem(win_str_path))
                self.query.prepare(querystr)
                self.query.bindValue(0, win_str_path)
                self.query.exec()
            else:
                if any([Path(win_str_path) in Path(self.path_mode.index(a, 0).data()).rglob('') for a in range(rows)]): #判断新加路径是否已有路径父路径，如相同这里也为True
                    QMessageBox.information(Form,'信息',f'”{win_str_path}“是已有路径的子路径，或是相同的路径')
                elif any([Path(self.path_mode.index(a, 0).data()) in Path(win_str_path).rglob('') for a in range(rows)]): #判断新加路径是否为已有路径子路径
                    QMessageBox.information(Form, '信息', f'“{win_str_path}“是已有路径的父路径，请移除多余的子路径再添加')
                else:
                    self.path_mode.appendRow(QStandardItem(win_str_path))
                    self.query.prepare(querystr)
                    self.query.bindValue(0,win_str_path)
                    self.query.exec()
                    #print(self.query.lastError().text())
    def del_path(self): #删除路径集函数
        row=self.path_groud_tableView.currentIndex().row() #取得当前行
        x=self.path_mode.index(row,0).data() #取得对应mode数据
        self.path_mode.removeRow(row) #移除
        del_querystr='delete from path_collection where path=?'
        self.query.prepare(del_querystr)
        self.query.bindValue(0, x)
        self.query.exec()

    def update_db(self): #重置并更新数据库
        if self.db.open():
            self.query.exec("select path from path_collection")
            path_list=[] #建立一空列表以放置数据库指针结果
            while self.query.next():
                path_list.append(Path(self.query.value(0)))
            self.query.exec("delete from file_info") #删除所有表项
            #print(self.query.lastError().text())
            self.query.exec("update sqlite_sequence set seq=0 where name='file_info'") #重置自增键起始
            #print(self.query.lastError().text())
            #print('--------------')
            self.query.exec('BEGIN TRANSACTION') #使用事务批量提交数据
            for z in path_list:
                cut_len=len(z.parts) #获取起始元组分割点位置，以分割部份路径
                for x in z.rglob('*.*'): #使用pathlib递归方法获取所有文件path对象
                    if x.is_file(): #似乎是多余的，前面已过滤，只是感觉再判断会更稳健一点
                        add_querystr = 'insert into file_info (文件名,完整路径,部份路径,创建时间,修改时间,访问时间,文件大小) VALUES (?,?,?,?,?,?,?)' #很多项其实用处不大，只有可能有点强迫症，多线程避介面卡也懒得弄了，毕竟不会经常用
                        self.query.prepare(add_querystr)
                        self.query.bindValue(0, x.name)
                        self.query.bindValue(1, str(x.resolve()))
                        self.query.bindValue(2, '\\'.join(x.parts[cut_len:]))
                        self.query.bindValue(3, x.stat().st_ctime)
                        self.query.bindValue(4, x.stat().st_mtime)
                        self.query.bindValue(5, x.stat().st_atime)
                        self.query.bindValue(6, x.stat().st_size)
                        self.query.exec()
            self.query.exec('COMMIT') #提交变更
            #print(self.query.lastError().text())
            uptime_str="insert or replace into basic_info (id,last_update_time) values (0,?)"
            self.query.prepare(uptime_str)
            last_update_time=time.time()
            self.query.bindValue(0, last_update_time)
            self.query.exec()
            last_update=time.strftime('%Y-%m-%d %H:%M:%S %A',time.localtime(last_update_time))
            self.info_label.setText(f"上次数据更新时间:{last_update}")

    def search_db(self):
        if x:=self.keyword_lineEdit.text():
            search_str=""
            if self.radioButton.isChecked():
                search_str=f'select 文件名,完整路径 from file_info where 部份路径 like "%{x}%" limit 300'
            else:
                search_str=f'select 文件名,完整路径 from file_info where 文件名 like "%{x}%" limit 300'
            self.search_mode.setQuery(search_str)
            #print(self.result_tableView.horizontalHeader().sectionSize(0))
            #print(self.result_tableView.horizontalHeader().sectionSize(1))

    def open_file(self,Form):
        y=self.result_tableView.currentIndex().row() #取得当前所在行
        file=self.search_mode.index(y,1).data() #从对应MODE中取得文件完整路径
        try:
            os.startfile(file)
        except:
            QMessageBox.information(Form,'信息','文件无法打开！')

    def create_db(self): #创建空白数据表
        conn = sqlite3.connect("file_info.db")
        conn.execute("""create table if not exists path_collection(
        path text primary key
        )
        """)
        conn.execute("""create table if not exists basic_info(
        id integer primary key,
        last_update_time timestamp
        )
        """)
        conn.execute("""create table if not exists file_info(
        id integer primary key autoincrement,
        文件名 text,
        完整路径 text,
        部份路径 text,
        创建时间 timestamp,
        修改时间 timestamp,
        访问时间 timestamp,
        文件大小 int
        )
        """)
        conn.commit()
        conn.close()

    def retranslateUi(self, Form):
        Form.setWindowTitle(QCoreApplication.translate("Form", u"Form", None))
        self.label.setText(QCoreApplication.translate("Form", u"\u68c0\u7d22\u8def\u5f84\u96c6", None))
        self.add_path_pushbutton.setText(QCoreApplication.translate("Form", u"\u6dfb\u52a0\u8def\u5f84", None))
        self.del_path_pushbutton.setText(QCoreApplication.translate("Form", u"\u5220\u9664\u8def\u5f84", None))
        self.update_index_pushButton.setText(QCoreApplication.translate("Form", u"\u5efa\u7acb/\u66f4\u65b0\u7d22\u5f15", None))
        self.radioButton.setText(QCoreApplication.translate("Form", u"\u641c\u7d22\u5305\u62ec\u8def\u5f84", None))
        self.info_label.setText("")
    # retranslateUi

if __name__=='__main__':
    import sys
    app=QApplication(sys.argv)
    my_windows=QWidget()
    ui=Ui_Form()
    ui.create_db() #创建空白数据表
    ui.setupUi(my_windows)
    my_windows.show()
    my_windows.setWindowTitle('文件搜索辅助工具')
    sys.exit(app.exec())