Python Qt6 : use sqlite - part 002.

In this article tutorial I will show you how to read from the sqlite file the content of the table: files.

In the last article tutorial, I create a interface with PyQt6 that search files by regular expresion and result is add to sqlite file named: file_paths.db.

I used same steps with a default python class and I used QSqlTableModel to show the content received.

The script will create a window with this QSqlTableModel, then reads the file and add the result.

Let's see the source code:

import sys
from PyQt6.QtWidgets import QApplication, QMainWindow, QTableView
from PyQt6.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        # Initialize the database
        self.init_db()

        # Set up the GUI
        self.table_view = QTableView(self)
        self.setCentralWidget(self.table_view)

        # Set up the model and connect it to the database
        self.model = QSqlTableModel(self)
        self.model.setTable('files')
        self.model.select()
        self.table_view.setModel(self.model)

    def init_db(self):
        # Connect to the database
        db = QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName('file_paths.db')
        if not db.open():
            print('Could not open database')
            sys.exit(1)

    def create_table(self):
        # Create the 'files' table if it doesn't exist
        query = QSqlQuery()
        query.exec('CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY, path TEXT)')

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

I add this source code into a python script named view.py and I run it.

This is the result of the running script:

Python Qt6 : use sqlite - part 001.

This will default update for any python project:

python.exe -m pip install --upgrade pip --user
...
Successfully installed pip-23.1

The sqlite3 is already on my python instalation because I use version 3.11.0, you can see the official webpage.

Install the PyQt6 with the pip tool, I have this python package:

pip install PyQt6 --user
Requirement already satisfied: PyQt6 in c:\python311\lib\site-packages (6.4.1)
...

The next source of code will create a windows with two buttons and one edit area.

The PyQt6 graphics user interface use these elements: QPushButton, QLineEdit and QMessageBox from QWidget.

The python class will create a window with these elements and dor each of these is need to have methods.

First you need to select the folder, then use an regular expresion for search.

I used this : .*\.blend1$ this means *.blend1.

The last step is to use FindFiles button to search all blend files, in this case and add path of each of these to the sqlite database into a table named: files .

If you select the root C: then will take some time to search the files.

Let's see the source code:

import sys
import os
import re
from PyQt6.QtWidgets import *
from PyQt6.QtCore import *
import sqlite3

class FindFiles(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Find Files")
        self.setGeometry(100, 100, 500, 300)

        self.folder_button = QPushButton("Choose Folder")
        self.folder_button.clicked.connect(self.choose_folder)
        self.pattern_edit = QLineEdit()
        self.pattern_edit.setPlaceholderText("Enter regular expression pattern")
        self.pattern_edit.setFixedWidth(250)
        self.find_button = QPushButton("Find Files")
        self.find_button.clicked.connect(self.find_files)

        layout = QVBoxLayout()
        layout.addWidget(self.folder_button)
        layout.addWidget(self.pattern_edit)
        layout.addWidget(self.find_button)
        self.setLayout(layout)

        self.folder_path = ""

        self.conn = sqlite3.connect("file_paths.db")
        self.cursor = self.conn.cursor()
        self.cursor.execute("CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY, path TEXT)")

    def choose_folder(self):
        self.folder_path = QFileDialog.getExistingDirectory(self, "Choose Folder")
        if self.folder_path:
            self.folder_button.setText(self.folder_path)

    def find_files(self):
        if not self.folder_path:
            QMessageBox.warning(self, "Warning", "Please choose a folder first!")
            return

        pattern = self.pattern_edit.text()

        if not pattern:
            QMessageBox.warning(self, "Warning", "Please enter a regular expression pattern!")
            return

        file_paths = []
        for root, dirs, files in os.walk(self.folder_path):
            for file in files:
                if re.match(pattern, file):
                    file_path = os.path.join(root, file)
                    file_paths.append(file_path)
                    self.cursor.execute("INSERT INTO files (path) VALUES (?)", (file_path,))
        self.conn.commit()

        QMessageBox.information(self, "Information", f"Found {len(file_paths)} files that match the pattern!")

if __name__ == "__main__":
    app = QApplication(sys.argv)
    find_files = FindFiles()
    find_files.show()
    sys.exit(app.exec())

I put this source code into a file named:main.py and I run it.

python main.py

The result is this: