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

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

• Работа через адаптер sqlite3.
• Взаимодействие через sqlalchemy.Table.
• Использование sqlalchemy.orm для тех же задач.
• Бонус - PonyORM!

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

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

Взаимодействие через sqlalchemy.Table

Итак, мы уже имеем реализацию через sqlite3. Она имела ряд преимуществ:

• полный контроль над тем, какой же SQL код в итоге выполнится,
• явное написание SQL кода упростит использование тем, кто больше работал с SQL базами данных, чем с Python.

Однако, эти же преимущества могут быть и проблемами:

• при написании кода приходится постоянно перестраиваться с одного языка программирования на другой,
• среды разработки (IDE) обычно подсвечивают и автодополняют только код на одном языке.

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

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

    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, create_engine, insert

    metadata = MetaData()
    engine = create_engine('sqlite:///example.db', echo=True)


    PhotoTable = Table(
        'photo', metadata,
        Column('id', Integer, primary_key=True),
        Column('url', String),
    )

    TagTable = Table(
        'tag', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String, unique=True),
    )

    PhotoTagTable = Table(
        'photo_tag', metadata,
        Column('photo_id', Integer, ForeignKey("photo.id"), nullable=False),
        Column('tag_id', Integer, ForeignKey("tag.id"), nullable=False),
    )

    metadata.create_all(engine)

Создаём metadata для хранения информации о схеме базы данных. А также engine - как адаптер к базе данных.

Далее - описание таблиц. Очень похоже на описание SQL-таблиц, но на Python. Сравните с описанием таблиц из предыдущего примера.

И в итоге - metadata.create_all(engine) - создаём описанные таблицы в базе данных.

Для добавления записей в таблицы (INSERT-ов) заведём функцию и создадим в ней нужные строки БД для демонстрации запросов:

    def insert_data(conn):
        # Create photo
        stmt = insert(PhotoTable).values(url='https://900913.ru/static/img/logo-192x192.png')
        result = conn.execute(stmt)
        photo_id = result.lastrowid

        # Create tag
        stmt = insert(TagTable).values(name='python')
        tag_id = conn.execute(stmt).lastrowid

        # Create photo-tag link
        stmt = insert(PhotoTagTable).values(photo_id=photo_id, tag_id=tag_id)
        conn.execute(stmt)

        # Create one more tag and photo-tag link
        stmt = insert(TagTable).values(name='website')
        tag_id = conn.execute(stmt).lastrowid
        stmt = insert(PhotoTagTable).values(photo_id=photo_id, tag_id=tag_id)
        conn.execute(stmt)

В insert(PhotoTable).values(url=...) создаётся запрос. Вывести его мы можем, добавив print(stmt) строчкой ниже. В частности, данная конструкция преобразуется в запрос: INSERT INTO photo (url) VALUES (:url).

Далее выполняем запрос conn.execute(stmt) и получаем объект результата запроса. Как это было с курсором из примера запросов на sqlite3, result имеет атрибут lastrowid - из него мы можем получить идентификатор добавленной строки. Также в SELECT запросах можно применять fetchone() и fetchall() для получения результатов запроса.

Теперь перейдём к непосредственно запросу:

    def main():
        conn = engine.connect()
        insert_data(conn)

        stmt = (
            PhotoTable.select()
            .join(PhotoTagTable, PhotoTagTable.c.photo_id == PhotoTable.c.id)
            .join(TagTable, TagTable.c.id == PhotoTagTable.c.tag_id)
            .where(TagTable.c.name == 'website')
        )

        result = conn.execute(stmt)

        for id, url in result:
            print(url)

Ровно такое же запрос, как и был в примере sqlite3: получаем фотографии, отмеченные указанным тегом. Заметим, что вместо result.fetchall() мы используем for для итеративного прохода по данным.

Отмечу, что для указания полей в запросах используется атрибут .c класса таблицы.

Также для примера - запросы обновления и удаления:

    stmt = TagTable.update().where(TagTable.c.name == 'website').values(name='web')
    conn.execute(stmt)
    stmt = TagTable.delete().where(TagTable.c.name == 'web')
    conn.execute(stmt)

В итоге мы получили возможность обращаться к базе данных на Python, пусть запросы и очень похожи на SQL.

• Работа через адаптер sqlite3.
• Взаимодействие через sqlalchemy.Table.
• Использование sqlalchemy.orm для тех же задач.
• Бонус - PonyORM!