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

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

ПрограммыDataBaseКодPythonMariaDB / MySQLPostgreSQL

В данной заметке будут предложены 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)

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

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