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

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

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

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

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

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

Работа через адаптер 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 не показал, ибо работают они при данном подходе аналогично. Если хочется попрактиковаться - реализуйте удаление связи, редактирование текста метки.

Также может быть вам интересно:

Важна ли Операционная Система для программиста?

Влияет ли выбор операционной системы на возможности программиста? Какую ОС выбрать конкретному разработчику?

Читать »

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

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

Читать »
Фото Как установить PostgreSQL на Linux и создать базу и пользователя

Как установить PostgreSQL на Linux и создать базу и пользователя

PostgreSQL - система управления базой данных общего назначения. Одна из самых распространённых баз данных, используемая на многих коммерческих и некоммерческих проектах.

Фото Как сделать свою middleware в Django (с примерами)

Как сделать свою middleware в Django (с примерами)

Middleware или "промежуточное программное обеспечение" - элегантный способ установить общие правила обработки запросов и ответов приложения. Давайте напишем парочку middleware, чтобы понять, как они работают.

Фото Как настроить отправку почты из Django

Как настроить отправку почты из Django

Письма об ошибках, отчёты на почту, восстановление паролей - всё это полезно при работе с сайтом. Django предоставляет удобный способ это сделать с минимумом настроек!

Фото Добавляем поддержку медиа-файлов в Django проект

Добавляем поддержку медиа-файлов в Django проект

Современные сайты редко ограничиваются только текстом и вёрсткой. Часто в заметках красуются фотографии, а рядом с описанием товаров - их изображения.

Фото Нет слов, одни... однострочники

Нет слов, одни... однострочники

На днях вышел пост со списком полезных однострочников для JavaScript программистов. Памятуя Perl-овую молодость, заглянул туда.

Фото Добавляем переменные в контекст Django шаблонов (свой контекст-процессор)

Добавляем переменные в контекст Django шаблонов (свой контекст-процессор)

В Django вы можете передавать данные в шаблоны посредством контекстов. Контекст передаётся из контроллера (view в терминах Django), однако, если одни и те же данные нужны в разных местах, лучше сделать свой контекст-процессор.

Фото Пример своей консольной команды в Django проекте

Пример своей консольной команды в Django проекте

Если вы работали с Django проектом, то, скорее всего, запускали команды из консоли (manage.py). В Django есть простой способ писать свои команды для управления проектом.

Фото Разграничение прав доступа на Django сайте

Разграничение прав доступа на Django сайте

Почти на любом веб-сайте необходимо разделять пользователей на группы и предоставлять им разные возможности. В Django есть довольно серьёзная система прав доступа для пользователей - давайте её рассмотрим!