Статья Создаем приложение для хранения данных в базе sqlite с помощью Python и Qt. Часть 01

В данной статье я попытаюсь создать приложение, которое позволяет сохранять в базу данных файлы в формате HTML. За все время, проведенное в интернете и поиске самой разнообразной информации натыкаешься на большое количество самых разнообразных страниц и статей, которые в данный момент прочитать не получается. В этом случае можно поступить по разному. Кто-то просто сохраняет ссылку в браузере, кто-то добавляет страницы в агрегаторы ссылок. Кто-то сохраняет страницы на жесткий диск, особенно, если это информация, которая может потребоваться в дальнейшем. Я отношусь скорее к последним. И за долгое время накапливается большое количество сохраненных страниц, в которых становиться довольно сложно ориентироваться. Поэтому я решил сделать небольшое приложение на Python, которое частично помогло решить мне эту задачу.

Для начала, почему формат HTML. Ну, во-первых, это просто удобно. В случае, если вам нужно что-то скопировать или на странице представлен код во фрейме, выходящий за границы страницы. Если сохранить такую страницу в PDF, часть кода будет попросту недоступна. Во-вторых, есть хорошие инструменты, которые позволяют делать это достаточно легко удобно. Конечно же, речь не о них, но для понимания общей концепции данный вопрос нужно описать.

Когда-то, года до 2019, почти все браузеры поддерживали формат MHTML, архивный формат веб-страниц. С помощью данного формата можно было сохранить документы в один файл, что предоставляло удобства для хранения сохраненного документа, а также позволяло не зависеть от любых действий произведенных администраторами сайтов. В случае, если будет сохранена ссылка, сайт может стать просто недоступным, его могут удалить или закончиться срок действия домена. Ну или картинки, тем более, если они тянуться со сторонних ресурсов могут быть просто удалены. Вот потому, во избежание данных неудобств, сохранение страницы локально, это достаточно удобный вариант. Тем более, в данном случае, когда файл только один, отсутствует папка с изображениями, шрифтами, скриптами, которые неизбежно создаются при сохранении веб-страницы как простого HTML-файла. Вот только теперь формат MHTML больше не поддерживается. Ни его сохранение, ни его отображение. Все это можно реализовать плагинами или просмотровщиками, но зачем воскрешать то, что уже мертво. Поэтому я поискал более подходящие для сегодняшнего дня инструменты. Наткнулся на очень хорошее расширение для браузеров — SingleFile. Более подробно о поддержке браузеров да и по работе с этим инструментом можно почитать на его странице на GiHub (GitHub - gildas-lormeau/SingleFile: Web Extension and CLI tool for saving a faithful copy of an entire web page in a single HTML file). Данное расширение не только сохраняет веб-страницы в один HTML-файл, но также позволяет их редактировать перед сохранением. Достаточно выбрать в меню пункт «Аннотировать и сохранить страницу».

Аннотировать и сохранить страницу

Вот, для примера страница со статьей с Codeby до аннотирования и после.

Страница до аннотирования

Страница после аннотирования

Вот с помощью данного инструмента я и сохраняю страницы локально на компьютер.
Эти страницы, я и буду сохранять в базе данных. Что же, давайте приступим к созданию приложения.


Что потребуется?

Для того, чтобы создать приложение, нам потребуется установить Qt Designer. Так как его интерфейс создан именно в нем. Установка данного инструмента различается в Linux и Windows. В Windows установить его можно с помощью команды:

pip install pyqt5-tools --pre

В этом случае Дизайнер можно найти в папке установки инструментов примерно вот по этому пути: ...Lib\site-packages\pyqt5_tools. Или можно скачать инсталлятор с сайта:

Ссылка скрыта от гостей

.

К сожалению, установщика данного инструмента для Linux, в чистом виде не существует. Поэтому, в Linux установка Дизайнера происходит с помощью нескольких команд. Последовательно вводим в терминале команды:

sudo apt-get install python3-pyqt5
sudo apt-get install pyqt5-dev-tools
sudo apt-get install qttools5-dev-tools

В этом случае, ярлык для запуска Дизайнера должен создастся автоматически. Вот, к примеру, как это выглядит в Linux Mint:

Дизайнер в меню приложений Linux Mint

Теперь, для того, чтобы мы имели возможность работать с данным инструментом из Python, следует установить библиотеку PyQt5. Для ее установки пишем в терминале:

pip install PyQt5

Если у вас операционная система Windows, вам потребуется установить еще одно библиотеку, для того, чтобы иметь возможность показывать системные сообщения win10toast. Для ее установки пишем следующую команду:

pip install win10toast

Иконки для данного приложения, которые представлены в виде картинок формата PNG я добавлю во вложении к данной статье. Скорее всего, ко второй ее части. По итогу нашей работы, должно получиться что-то, подобное этому:

Интерфейс будущего приложения

Теперь, когда мы немного определились с целями, давайте приступим к написанию кода. Для начала напишем часть кода, которая будет работать с базой данных sqlite.


Работа с базой данных

Создадим отдельный файл питона. Я назвал его doc.py. В него импортируем библиотеки, которые нужны будут в процессе работы:

import base64
import sqlite3
from pathlib import Path

Удаление из базы пустых записей

Создадим функцию vacuum_base(path: str). На вход она принимает путь к базе данных. Далее, создаем соединение с базой. И запускаем sql команду «VACUUM». После чего закрываем соединение.

def vacuum_base(path: str):
    """
    Очистка пустых значений из базы.
    :param path: Путь к базе данных.
    """
    conn = sqlite3.connect(path)
    conn.execute("VACUUM")
    conn.close()

Для чего нужна эта команда? Для примера, у меня создана база с 85 записями, общий объем которых составляет 119,1 Мб.

Объем базы данных

Теперь, я удалю все записи из базы и добавлю в нее всего одну, объемом 369,1 Кб. Посмотрим, что станет в этом случае с объемом базы. Объем базы не изменился. Он как был 119,1 Мб, так и остался.

Объем после удаления всех записей

Теперь применим к базе данных команду «VACUUM». Как видим, ее объем стал 499,7 Кб. Существенная разница.

Объем после применения команды

А дело в том, что данная команда перестраивает всю базу данных. При удалении большого количества данных в базе остается пустое пространство, так называемые «свободные» страницы. Если активно работать с базой, вставлять, удалять или редактировать записи, это приводит к ее фрагментации. То есть, информации хранящейся в ней немного, а вот объем она занимает большой. VACUUM копирует все содержимое базы во временный файл, а затем перезаписывает оригинал его содержимым. Имейте в виду, что эта особенность требует наличия на диске свободного места в два раза больше, чем оригинальная база данных. Надеюсь, смог прояснить этот вопрос.

Изначально я хотел повесить данную команду на закрытие приложения. Но, при большом объеме приложение может достаточно долго закрываться. Потому, сделал уплотнение базы отдельной кнопкой.


Создание базы данных

Для создания базы данных создадим функцию create_base(path: str). На вход она принимает путь к директории с именем и расширением создаваемой базы. Создаем соединение с базой. Если базы не существует, она будет создана автоматически. Создаем курсор, некий объект, который делает запросы и получает их результаты. Выполняем sql-запрос на создание таблицы в созданной базе, если данная таблица не существует. Таблица называется documents. В ней мы создаем три столбца: name — текстовый формат, здесь будет храниться название документа; tag — текстовый формат, здесь будут храниться теги документа; base64 — формат BLOB, для хранения данных в виде байтов. Сохраняем изменения и закрываем соединение с курсором и с базой.

def create_base(path: str):
    """
    Создаем базу данных sqlite.
    :param path: Путь к базе данных.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS documents(
                   name TEXT,
                   tag TEXT,
                   base64 BLOB);
                """)
    conn.commit()
    cur.close()
    conn.close()

Открытие базы данных

Создадим функцию open_base(path: str) -> list. На вход она получает путь к базе данных, а возвращает, после выполнения запроса список с кортежами, в которых будут содержаться имя документа и его теги.

Здесь почти все также, как и в предыдущем случае. Устанавливаем соединение с базой данных, создаем курсор. Выполняем запрос на выборку из столбцов name и tag, возвращаем все результаты с помощью команды fetchall. Закрываем соединения и возвращаем полученные результаты.

def open_base(path: str) -> list:
    """
    Открытие базы данных.
    :param path: Путь к базе данных.
    :return: Список с выборкой названий всех документов в базе.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute("SELECT name, tag FROM documents")
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data

Обновление записей в базе

Для обновления записей создадим функцию update_data(path: str, name: str, tag: str, data: bytes). На вход она принимает путь к базе данных, название документа, теги, содержимое в формате base64. Данная функция нужна для редактирования тегов. В процессе добавления документов в базу, вы вряд ли захотите останавливаться на каждом документе и редактировать теги. Поэтому документы по умолчанию имеют тег: no tag. Вот для обновления данного содержимого после редактирования и нужна данная функция. Здесь все то же самое, что и в предыдущих за исключением выполняемого запроса. Таким образом, с помощью запроса мы находим запись с нужным названием, перезаписываем в ней теги и сам документ. После чего закрываем соединение.

def update_data(path: str, name: str, tag: str, data: bytes):
    """
    Обновление записи в БД. В данном случае обновление тегов.
    :param path: Путь к базе данных.
    :param name: Название документа.
    :param tag: Теги.
    :param data: Документ в формате base64.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    update_query = """Update documents set tag = ?, base64 = ? where name = ?"""
    column_values = (tag, data, name)
    cur.execute(update_query, column_values)
    conn.commit()
    cur.close()
    conn.close()

Удаление записей из базы данных

Для удаления записей создадим функцию delete_from_base(path: str, name: str). На вход она принимает путь к базе данных, название документа для удаления записи. Подключаемся к базе. Выполняем запрос на удаление записи по имени документа. Закрываем соединение.

def delete_from_base(path: str, name: str):
    """
    Удаление документа из базы данных.
    :param path: Путь к базе данных.
    :param name: Название документа для удаления.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    update_query = """DELETE from documents where name = ?"""
    cur.execute(update_query, (name,))
    conn.commit()
    cur.close()
    conn.close()

Добавление записей в базу данных

Для добавления записей создадим функцию save_to_base(path: str, data_list: list). На вход она принимает путь к базе данных, список со списками, в которых содержится название документа, теги и содержимое в формате base64. Выполняем запрос на начало транзакции. В теории, это должно немного ускорить добавление данных в базу. В цикле итерируемся по полученному списку и записываем полученные значения в базу с помощью соответствующего запроса. После добавления всех записей закрываем соединения.

def save_to_base(path: str, data_list: list):
    """
    Сохраняем данные в базе.
    :param path: Путь к базе данных.
    :param data_list: Список со списками из данных: название статьи (документа), данные в формате base64.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('BEGIN TRANSACTION')
    for data in data_list:
        cur.executemany("INSERT INTO documents VALUES(?, ?, ?);", (data, ))
    conn.commit()
    cur.close()
    conn.close()

Выборка тегов из базы данных

Для того, чтобы иметь возможность фильтровать записи по тегам, которые содержатся в столбце tag, создадим функцию select_tag(path: str, tag: str) -> list. На вход она принимает путь к базе данных, тег, который нужно найти.

Подключаемся к базе и выполняем запрос на выборку. Здесь мы будем использовать оператор LIKE, который ищет все записи по заданному шаблону. В нашем случае шаблоном является переданный тег со знаками процента в начале и конце, что означает найти все записи в столбце в любой позиции с указанным значением. Получаем список тегов, которые возвращаем из функции. Закрываем соединение.

def select_tag(path: str, tag: str) -> list:
    """
    Выборка данных из базы по определенному тегу.
    :param path: Путь к базе данных.
    :param tag: Тег, по которому нужно делать выборку.
    :return: Список из кортежей.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    select_query = """SELECT name, tag FROM documents where tag LIKE ?"""
    cur.execute(select_query, [f'%{tag}%'])
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data

Сохранение данных из базы

Несмотря на то, что функция называется сохранением, на деле, она просто делает выборку с указанным параметром. После чего эта выборка возвращается из функции в виде кортежа. Создадим функцию save_doc_from_base(path: str, name: str) -> tuple. На вход она получает путь к базе данных, имя документа. Возвращает кортеж из имени документа, тегов и байтового содержимого в формате base64. И снова подключаемся к базе, составляем запрос, делаем выборку, закрываем соединение и возвращаем кортеж с данными из функии. Здесь очень важно не ошибиться и поставить запятую в кортеже с условием для выборки: cur.execute(select_query, (name,)). В данном случае, после name. В противном случае, если запятая поставлена не будет, возникнет исключение: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied. Циферки могут различаться, но суть останется прежней. Если не поставить запятую, кортеж не считается кортежем, а просто сгруппированным выражением. Соответственно, python воспринимает его как некое число, равное длине выражения, отдельных значений привязки, каждое из которых равно длине в один символ.

def save_doc_from_base(path: str, name: str) -> tuple:
    """
    Выборка из базы документа для сохранения.
    :param path: Путь к базе данных.
    :param name: Название сохраняемого документа.
    :return: Кортеж из названия документа и base64-документ.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    select_query = """SELECT * FROM documents where name = ?"""
    cur.execute(select_query, (name,))
    data = cur.fetchone()
    cur.close()
    conn.close()
    return data

Кодирование документа в base64

Статью о кодировании документов в данный формат я писал ранее. Вот ссылка на нее: (Кодирование/декодирование изображений и документов в формат base64 с помощью Python). Создадим функцию encode_b64(path: str) -> tuple. На вход она получает путь к кодируемому документу, а возвращает кортеж из его названия, тега и байтовой строки.

Открываем файл на чтение в байтовом режиме. Забираем из пути к документу его название с помощью Path. Кодируем с помощью функции b64encode документ в base64. Возвращаем кортеж со значениями.

def encode_b64(path: str) -> tuple:
    """
    Кодирование файла в base64.
    :param path: Путь к кодируемому файлу.
    :return: Кортеж из имени кодированного документа и кодированный в base64 документ.
    """
    with open(path, "rb") as file:
        return Path(path).name.split(Path(path).suffix)[0], "no tag", base64.b64encode(file.read())

Декодирование документа из base64

Создадим функцию decode_b64(path: str, data: bytes). На вход она принимает путь для сохранения документа. По большому счету, сохранением занимается данная функция. Документ в формате base64 для декодирования. Затем декодируем полученные данные. И записываем на диск по переданному пути в байтовом режиме.

def decode_b64(path: str, data: bytes):
    """
    Декодирование файла из base64.
    :param path: Путь к базе данных.
    :param data: Строка в формате base64 для декодирования.
    """
    dec = base64.decodebytes(data)
    with open(path, 'wb') as file:
        file.write(dec)

На этом, думаю, что первую часть нужно завершать. В данной части мы создали модуль, с помощью которого будем совершать операции с базой данных из нашего приложения. В следующей части мы создадим его интерфейс, а также напишем код взаимодействия с его компонентами и базой.

Полный код модуля doc.py

import base64
import sqlite3
from pathlib import Path


def vacuum_base(path: str):
    """
    Очистка пустых значений из базы.
    :param path: Путь к базе данных.
    """
    conn = sqlite3.connect(path)
    conn.execute("VACUUM")
    conn.close()


def create_base(path: str):
    """
    Создаем базу данных sqlite.
    :param path: Путь к базе данных.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS documents(
                   name TEXT,
                   tag TEXT,
                   base64 BLOB);
                """)
    conn.commit()
    cur.close()
    conn.close()


def open_base(path: str) -> list:
    """
    Открытие базы данных.
    :param path: Путь к базе данных.
    :return: Список с выборкой названий всех документов в базе.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute("SELECT name, tag FROM documents")
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data


def update_data(path: str, name: str, tag: str, data: bytes):
    """
    Обновление записи в БД. В данном случае обновление тегов.
    :param path: Путь к базе данных.
    :param name: Название документа.
    :param tag: Теги.
    :param data: Документ в формате base64.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    update_query = """Update documents set tag = ?, base64 = ? where name = ?"""
    column_values = (tag, data, name)
    cur.execute(update_query, column_values)
    conn.commit()
    cur.close()
    conn.close()


def delete_from_base(path: str, name: str):
    """
    Удаление документа из базы данных.
    :param path: Путь к базе данных.
    :param name: Название документа для удаления.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    update_query = """DELETE from documents where name = ?"""
    cur.execute(update_query, (name,))
    conn.commit()
    cur.close()
    conn.close()


def save_to_base(path: str, data_list: list):
    """
    Сохраняем данные в базе.
    :param path: Путь к базе данных.
    :param data_list: Список со списками из данных: название статьи (документа), данные в формате base64.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('BEGIN TRANSACTION')
    for data in data_list:
        cur.executemany("INSERT INTO documents VALUES(?, ?, ?);", (data, ))
    conn.commit()
    cur.close()
    conn.close()


def encode_b64(path: str) -> tuple:
    """
    Кодирование файла в base64.
    :param path: Путь к кодируемому файлу.
    :return: Кортеж из имени кодированного документа и кодированный в base64 документ.
    """
    with open(path, "rb") as file:
        return Path(path).name.split(Path(path).suffix)[0], "no tag", base64.b64encode(file.read())


def decode_b64(path: str, data: bytes):
    """
    Декодирование файла из base64.
    :param path: Путь к базе данных.
    :param data: Строка в формате base64 для декодирования.
    """
    dec = base64.decodebytes(data)
    with open(path, 'wb') as file:
        file.write(dec)


def select_tag(path: str, tag: str) -> list:
    """
    Выборка данных из базы по определенному тегу.
    :param path: Путь к базе данных.
    :param tag: Тег, по которому нужно делать выборку.
    :return: Список из кортежей.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    select_query = """SELECT name, tag FROM documents where tag LIKE ?"""
    cur.execute(select_query, [f'%{tag}%'])
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data


def save_doc_from_base(path: str, name: str) -> tuple:
    """
    Выборка из базы документа для сохранения.
    :param path: Путь к базе данных.
    :param name: Название сохраняемого документа.
    :return: Кортеж из названия документа и base64-документ.
    """
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    select_query = """SELECT * FROM documents where name = ?"""
    cur.execute(select_query, (name,))
    data = cur.fetchone()
    cur.close()
    conn.close()
    return data

А на этом, пожалуй, все.

Спасибо за внимание. Надеюсь, данная информация будет вам полезна