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:

Get bookmarks from your Firefox browser database.

This simple example tutorial is about reading the bookmarks from firefox database.
The database is an SQLite database.
You need to create a python file named: firefox_bookmarks.py.
Change your windows account on the bookmarks_path.
The script is simple to understand and comes with two functions: execute_query and get_bookmarks.
Follow the commented source code to understand how it's working the python script:

import os
import sqlite3

# execute a query on sqlite cursor
def execute_query(cursor, query):
    try:
        cursor.execute(query)
    except Exception as error:
        print(str(error) + "\n " + query)
# get bookmarks from firefox sqlite database file and print all
def get_bookmarks(cursor):
    bookmarks_query = """select url, moz_places.title, rev_host, frecency,
    last_visit_date from moz_places  join  \
    moz_bookmarks on moz_bookmarks.fk=moz_places.id where visit_count>0
    and moz_places.url  like 'http%'
    order by dateAdded desc;"""
    execute_query(cursor, bookmarks_query)
    for row in cursor:
        link = row[0]
        title = row[1]
        print(link,title)
# set the path of firefox folder with databases
bookmarks_path = "C:/Users/YOUR_WINDOWS_ACCOUNT/AppData/Roaming/Mozilla/Firefox/Profiles/"
# get firefox profile
profiles = [i for i in os.listdir(bookmarks_path) if i.endswith('.default')]
# get sqlite database of firefox bookmarks
sqlite_path = bookmarks_path+ profiles[0]+'/places.sqlite'
#
if os.path.exists(sqlite_path):
    firefox_connection = sqlite3.connect(sqlite_path)
cursor = firefox_connection.cursor()
get_bookmarks(cursor)
cursor.close()

The result of running the script comes with my bookmarks of Firefox:

C:\Python364>python.exe firefox_bookmarks.py
https://twitter.com/ Twitter. It's what's happening. 

Sqlite and Python

These script explore the Python Sqlite modules available for database administration.
Sometime you need to use extraction, processing, storage and presentation of your data.
This is a short example :

#!/usr/bin/python

import sqlite3

def select_db():
print "Select database"

co=sqlite3.connect("cata2.db")
cursor=co.execute("CREATE TABLE report (nr INT,user VARCHAR(20),descriere VARCHAR(100));")
cursor=co.execute("INSERT INTO report (nr,user,descriere) VALUES (1,'root','root administration');")
cursor=co.execute("SELECT * FROM report;")

print cursor.fetchall()

This python script will be create cata.db file on your folder.
The table of database is 'report' and add next values "1,'root','root administration'".
If you want create new database , use :

co=sqlite3.connect("new_database.db")
cursor=co.execute("CREATE TABLE tabela (some_value_integer INT,some_value_chars VARCHAR(20));")

If you want show it , use this :

cursor=co.execute("SELECT * FROM new_database;")

Thank you !