Программы
Три примера работы с SQL базой данных в Python (адаптор sqlite3)

Три примера работы с SQL базой данных в Python (адаптор sqlite3)

Многие приложения на Python используют базу данных для хранения и эфективного использования их в своей работе.

В данной заметке будут предложены 3 примера работы с БД из Python:

Хочется сконцентрироваться на различиях и преимуществах, которые дают те или иные подходы.

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

Изображение Python 3.11. Что нового?

Работа через адаптер sqlite3

Для начала нам потребуются некоторые классы и функции из sqlite3 и typing (для type-hinting-а):

    import sys
    from sqlite3 import Error as SQLError, Connection, connect as connect_db
    from typing import List, Tuple

Подключение к базе данных вынесем в отдельную функцию. Само подключение, очевидно, делается с помощью функции connect:

    def create_connection(file_path: str) -> Connection:
        try:
            return connect_db(file_path)
        except SQLError as e:
            print(e, file=sys.stderr)
            raise e

Пытаемся подключиться к БД, расположенной в файле file_path, если получилось - возвращаем, если нет - выводим сообщение исключения на стандартный поток вывода ошибок и пробрасываем дальше исключение.

Работая с базой данных через адаптер sqlite3, мы выполняем SQL-запросы, описывая их в текстовом ("сыром") виде. Поэтому функция создания таблиц в нашем случае будет выглядеть следующим образом:

    def create_tables(conn: Connection):
        cur = conn.cursor()
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS photo (
                id INTEGER PRIMARY KEY,
                url TEXT NOT NULL
            );
            """
        )
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS tag (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL
            );
            """
        )
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS photo_tag (
                photo_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
                FOREIGN KEY(photo_id) REFERENCES photo(id),
                FOREIGN KEY(tag_id) REFERENCES tag(id)
            );
            """
        )

Как видите, много SQL-кода. Из нового в Python коде - создание курсора cur, через который мы и взаимодействуем с БД, выполняя запросы через метод execute.

Сама же схема довольно простая: есть таблица для хранения url-ов, есть таблица для хранения тегов (меток), а также таблица для связи многие-ко-многим для отмечания (тегирования) фотографий.

Теперь нам нужны функции для добавления фотографий и тегов:

    def add_photo(conn: Connection, url: str) -> int:
        cur = conn.cursor()
        cur.execute("INSERT INTO photo(url) VALUES(?)", (url,))
        return cur.lastrowid


    def add_tag(conn: Connection, name: str) -> int:
        cur = conn.cursor()
        cur.execute("INSERT INTO tag(name) VALUES(?)", (name,))
        return cur.lastrowid

На вход получаем объект соединения с базой данных, а также полезные данные: url, имя тега. В результате отдаём идентификатор новосозданной строки таблицы.

Всё также работаем через курсор. Однако, на этот раз execute принимает два параметра: параметризованный запрос и параметры. То есть сначала идёт строка запроса, где на месте вставки данных стоит знак вопроса, а потом кортеж или список с параметрами, которые будут подставлены на места этих вопросов. Так, к примеру, мы можем избежать sql-инъекций, которые могли бы возникнуть, напиши мы cur.execute(f"INSERT INTO tag(name) VALUES({name})").

Также используем cur.lastrowid для получения идентификатора сохранённой записи.

Аналогично создаём функцию для добавления связи тега и фотографии:

    def create_photo_tag_link(conn: Connection, photo_id: int, tag_id: int):
        cur = conn.cursor()
        cur.execute("INSERT INTO photo_tag(photo_id, tag_id) VALUES(?, ?)", (photo_id, tag_id))

Параметра уже 2, но суть та же. Ну и id мы не возвращаем. Хотя могли бы, если бы в схеме таблицы описали.

Теперь в качестве логического завершения - функция для получения идентификаторов и урлов фотографий по тегу:

    def find_urls_by_tag(conn: Connection, tag_name: str) -> List[Tuple[int, str]]:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT p.id, p.url
            FROM photo as p
            JOIN photo_tag as pt ON pt.photo_id = p.id
            JOIN tag as t ON t.id = pt.tag_id
            WHERE t.name = ?
            """,
            (tag_name,)
        )
        return cur.fetchall()

Довольно стандартный SQL-запрос при работе с many-to-many (m2m) схемами - через join-ы. Из нового - получение данных из SELECT - используем метод fetchall курсора для получения всего результата запроса в виде списка кортежей колонок. Также есть метод fetchone - если нужно получить только первый результат - кортеж из колонок полученной записи.

И в завершении напишем функцию, которая будет использовать данные функции:

    def main():
        with create_connection('example.db') as conn:
            create_tables(conn)
            photo_id = add_photo(conn, 'https://900913.ru/media/cache/11/33/11338c534c4ad1388edc981ab6fb1994.jpg')
            tag_id = add_tag(conn, 'python')
            create_photo_tag_link(conn, photo_id, tag_id)
            print(find_urls_by_tag(conn, 'python'))

Здесь мы используем контекстный менеджер create_connection, чтобы в начале блока создалось подключение, а по завершению - закрылось.

Создаём таблицы, добавляем урл фотографии, добавляем метку, создаём между ними связь и ищем+печатаем все фотографии, которые были помечены тегом "python".

Чтобы запустить и проверить всю эту программу, скопируйте последовательно весь код в файл, добавьте в конце:

    if __name__ == '__main__':
        main()

Теперь это готовая к проверке программа. UPDATE, DELETE не показал, ибо работают они при данном подходе аналогично. Если хочется попрактиковаться - реализуйте удаление связи, редактирование текста метки.