🐍🗄️ Управление данными с помощью Python, SQLite и SQLAlchemy

На одном примере сравниваются три модели управления данными: csv-файлы, SQL-запросы к простой базе данных на SQLite и контроль информации в виде объектов Python с SQLAlchemy. В конце пример веб-приложения на Flask с использованием SQLAlchemy.

Эта публикация – выполненный с небольшими сокращениями перевод статьи Дуга Фаррелла Data Management With Python, SQLite, and SQLAlchemy. Полный программный код пособия доступен в GitHub-репозитории. Все упоминаемые примеры подпрограмм отсылают к соответствующим файлам.

***

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

Аналогичных результатов можно добиться, используя форматы CSV, JSON, XML или даже кастомные решения. Преимуществом таких текстовых хранилищ является легкий для восприятия формат данных – обычно структура понятна с первого взгляда. Ниже мы сравним такой способ хранения и обработки данных с применением реляционных баз данных и языка SQL, а также разберемся, какой метод лучше подходит для вашей программы. В общих чертах мы рассмотрим следующие вопросы:

  • как использовать плоские файлы и SQLite для хранения данных;
  • как SQL позволяет облегчить доступ к данным;
  • как применять SQLAlchemy для работы с данными в форме объектов Python.

В практическом отношении основной акцент в статье делается на SQLAlchemy.

🧾 Плоские базы данных

Под плоской базой данных (плоской таблицей) будем понимать файл, содержащий данные без внутренней иерархии и ссылок на внешние файлы. Это объясняет ряд особенностей: в таких базах данных нет необходимости использовать фиксированную ширину полей, а csv-файлы (англ. comma separated values) представляют собой строки простого текста, в которых элементы данных разделены запятыми. Каждая строка текста представляет собой строку данных, а каждое значение в строке, отделенное от остальных запятой соответствует одному из полей таблицы.

/data/author_book_publisher.csv
first_name,last_name,title,publisher
Isaac,Asimov,Foundation,Random House
Pearl,Buck,The Good Earth,Random House
Pearl,Buck,The Good Earth,Simon & Schuster
Tom,Clancy,The Hunt For Red October,Berkley
Tom,Clancy,Patriot Games,Simon & Schuster
Stephen,King,It,Random House
Stephen,King,It,Penguin Random House
Stephen,King,Dead Zone,Random House
Stephen,King,The Shining,Penguin Random House
John,Le Carre,"Tinker, Tailor, Solider, Spy: A George Smiley Novel",Berkley
Alex,Michaelides,The Silent Patient,Simon & Schuster
Carol,Shaben,Into The Abyss,Simon & Schuster

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

➕ Преимущества плоских баз данных

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

Ещё одно преимущество плоских файлов – они автономны, данными в такой форме легко поделиться. Практически в любом языке программирования есть инструменты и библиотеки для работы с csv-файлами. Python имеет встроенный модуль csv и мощную стороннюю библиотеку pandas.

🐼 Pandas
Библиотека программиста любит Data Science и библиотеку pandas. Пример: 10 трюков библиотеки Python Pandas.

➖ Недостатки плоских баз данных

Преимущества плоских баз данных меркнут по мере увеличения объема информации. Файлы остаются читаемыми, но редактирование и поиск отдельных записей вызывают трудности. И не только для человека – файл разрастается, и его обработка требует вычислительных ресурсов.

Другая трудность, связанная с применением плоских файлов – нужно явно создавать и поддерживать отношения между компонентами данных в рамках одного файла. Чтобы описать новые отношения, приходится писать дополнительный текст, создавать новые поля.

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

📇 Пример работы с плоской базой данных

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

Обратите внимание на особенность набора данных
Для реалистичности авторы Стивен Кинг и Том Клэнси появляются в таблице более одного раза – в данных представлены несколько книг. У авторов Стивена Кинга и Перл Бак есть одна и та же книга, опубликованная более чем одним издателем.

Рассмотрим функцию main() программы, находящейся в упомянутом репозитории по относительному адресу examples/example_1/main.py.

examples/example_1/main.py
def main():
    """Основная точка входа в программу"""
    # Получим ресурсы для программы
    with resources.path(
        "project.data", "author_book_publisher.csv"
    ) as filepath:
        data = get_data(filepath)

    # Узнаем количество книг, напечатанных каждым издателем
    books_by_publisher = get_books_by_publisher(data, ascending=False)
    for publisher, total_books in books_by_publisher.items():
        print(f"Publisher: {publisher}, total books: {total_books}")
    print()

    # Узнаем число авторов у каждого из издателей
    authors_by_publisher = get_authors_by_publisher(data, ascending=False)
    for publisher, total_authors in authors_by_publisher.items():
        print(f"Publisher: {publisher}, total authors: {total_authors}")
    print()

    # Вывод иерархических данных об авторах
    output_author_hierarchy(data)

    # Добавим новую книгу в структуру данных
    data = add_new_book(
        data,
        author_name="Stephen King",
        book_title="The Stand",
        publisher_name="Random House",
    )

    # Выводим обновленные данные в иерархическом виде
    output_author_hierarchy(data)

При запуске программы для обновленного csv-файла выводится следующий результат:

$ python main.py
Publisher: Simon & Schuster, total books: 4
Publisher: Random House, total books: 4
Publisher: Penguin Random House, total books: 2
Publisher: Berkley, total books: 2

Publisher: Simon & Schuster, total authors: 4
Publisher: Random House, total authors: 3
Publisher: Berkley, total authors: 2
Publisher: Penguin Random House, total authors: 1

Authors
├── Alex Michaelides
│   └── The Silent Patient
│       └── Simon & Schuster
├── Carol Shaben
│   └── Into The Abyss
│       └── Simon & Schuster
├── Isaac Asimov
│   └── Foundation
│       └── Random House
├── John Le Carre
│   └── Tinker, Tailor, Solider, Spy: A George Smiley Novel
│       └── Berkley
├── Pearl Buck
│   └── The Good Earth
│       ├── Random House
│       └── Simon & Schuster
├── Stephen King
│   ├── Dead Zone
│   │   └── Random House
│   ├── It
│   │   ├── Penguin Random House
│   │   └── Random House
│   └── The Shining
│       └── Penguin Random House
└── Tom Clancy
    ├── Patriot Games
    │   └── Simon & Schuster
    └── The Hunt For Red October
        └── Berkley

Для выполнения основной части работы main() вызывает другие функции, с которыми можно ознакомиться в файле. Само приложение работает корректно и демонстрирует возможности библиотеки pandas.

Мы показали этот пример, чтобы далее создать программу с идентичной функциональностью, используя базу данных SQLite и библиотеку SQLAlchemy для манипуляции этими данными.

🦋 Использование SQLite для хранения данных

Как мы упомянули выше в примечании, в файле author_book_publisher.csv в некоторых данных есть повторы. Например, роман Перл Бак The Good Earth опубликовали два разных издателя.

Pearl,Buck,The Good Earth,Random House
Pearl,Buck,The Good Earth,Simon & Schuster

Представьте, что было бы, если файл содержал больше связанных данных, например, сведения об авторе, дату публикации, ISBN книги, адрес и телефонный номер издательства. Подобные сведения будут дублироваться для каждого корневого элемента: автора, книги, издательства. Такое построение не только избыточно, но и усложняет процедуры 1) изменения полей, связанных с отдельным объектом и 2) добавления новых свойств.

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

SQLite является системой управления реляционными базами данных, доступной в стандартной библиотеке Python. Для работы с ней не требуется отдельный сервер, формат файла является кросс-платформенным и доступен в других языках программирования, поддерживающих SQLite.

Примечание
О работе с этой базой данных Библиотека программиста также писала в руководстве по SQLite.

🏗️ Создаем структуру базы данных

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

SQL – это декларативный язык, используемый для создания, управления и поиска данных, содержащихся в базе. Декларативный язык описывает, что должно быть выполнено, а не то, как это нужно сделать. Мы увидим примеры операторов SQL позже, когда перейдем к созданию таблиц базы данных.

Примечание
Если вы уже знакомы с SQL и для вас больший интерес представляет работа с SQLAlchemy, вы можете пропустить следующие разделы, дающие общее представление о работе с SQL.

Чтобы воспользоваться преимуществами SQL, нам нужно провести нормализацию базы данных из файла author_book_publisher.csv. Для этого мы перенесем авторов, книги и издателей в отдельные таблицы базы данных.

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

Каждая запись в таблице имеет первичный ключ (англ. primary key), определенный для присвоения записи уникального идентификатора. Первичный ключ похож на ключ в словаре Python. Движок базы данных обычно сам генерирует целочисленный первичный ключ, увеличивая значение на единицу для каждой новой записи. Если данные, хранящиеся в поле, уникальны среди всех других данных в этом поле, это значение также может использоваться, как первичный ключ. Например, в таблице, содержащей данные о книгах, в качестве первичного ключа может использоваться уникальный по своей природе ISBN.

🏢 Создание таблиц базы данных

Ниже представлен пример, как с помощью операторов SQL можно создать три таблицы, представляющие авторов, книги и издательства:

CREATE TABLE author (
    author_id INTEGER NOT NULL PRIMARY KEY,
    first_name VARCHAR,
    last_name VARCHAR
);

CREATE TABLE book (
    book_id INTEGER NOT NULL PRIMARY KEY,
    author_id INTEGER REFERENCES author,
    title VARCHAR
);

CREATE TABLE publisher (
    publisher_id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR
);

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

Примечание
Конечно, можно создавать приложения баз данных на Python, выполняя SQL-запросы непосредственно из кода Python. Использование чистого SQL – вполне приемлемый способ работы с данными. Соответствующие примеры мы привели в руководстве Как подружить Python и базы данных SQL. Однако в этой статье основной акцент сделан на SQLAlchemy, и некоторые сведения об SQL даны лишь затем, чтобы дать первичное представление об SQL-запросах для тех, кто только знакомится с этой областью.

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

SELECT * FROM author;

Вы можете использовать инструмент командной строки sqlite3 для взаимодействия с файлом базы данных author_book_publisher.db:

sqlite3 author_book_publisher.db

Ниже показан результат работы указанной команды SQL и ее вывод, за которой следует команда .q для выхода из программы:

sqlite> SELECT * FROM author;
1|Isaac|Asimov
2|Pearl|Buck
3|Tom|Clancy
4|Stephen|King
5|John|Le Carre
6|Alex|Michaelides
7|Carol|Shaben

sqlite> .q

Обратите внимание, что в отличие от csv-файла каждый автор присутствует в таблице только один раз.

🕹️ Манипуляции данными с помощью SQL

SQL предоставляет различные способы работы с базами данных и таблицами, добавление новых данных, обновление и удаление уже существующих. Пример оператора SQL для вставки нового автора в таблицу author:

INSERT INTO author
    (first_name, last_name)
VALUES ('Paul', 'Mendez');

Оператор INSERT вставляет строковые значения Paul и Mendez в соответствующие столбцы first_name и last_name таблицы author.

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

Обновление записей в таблице базы данных – также несложный процесс. Например, предположим, что Стивен Кинг хотел, чтобы его знали под псевдонимом Ричард Бахман:

UPDATE author
SET first_name = 'Richard', last_name = 'Bachman'
WHERE first_name = 'Stephen' AND last_name = 'King';

Оператор SQL находит запись с помощью условного оператора WHERE, а затем обновляет поля first_name и last_name свежими значениями. Знак равенства (=) в SQL используется и для сравнения, и для присваивания.

Пример оператора SQL для удаления записи из таблицы авторов:

DELETE FROM author
WHERE first_name = 'Paul'
AND last_name = 'Mendez';
Будьте осторожны при удалении записей!
Слишком широкое условие может привести к удалению большего количества записей, чем вы предполагали. Например, если бы условие было основано только на строке first_name = 'Paul', то были бы удалены все авторы с именем Paul.

🤝 Строим отношения: «один ко многим»

Данные в файле author_book_publisher.csv отображают данные и связи путем дублирования данных. База данных SQLite разбивает данные на три таблицы (author, book, and publisher) и устанавливает между ними отношения.

Связь «один ко многим» похоже на связь покупателя с заказываемыми товарами в Интернете. У одного покупателя может быть много заказов, но каждый заказ принадлежит одному покупателю. В базе данных author_book_publisher.db связь «один ко многим» представлена отношением авторов и книг. Каждый автор может написать много книг, но каждая художественная книга написана одним автором (в рамках рассматриваемого примера).

Как реализовать связь «один ко многим» между двумя таблицами? Каждая таблица в базе данных имеет поле первичного ключа, обычно названное по шаблону <имя таблицы>_id.

Кроме того, таблица book содержит поле author_id, которое ссылается на таблицу author (см. выше SQL-запрос для создания таблиц). Таким образом, поле author_id устанавливает связь «один ко многим» между авторами и книгами, которая выглядит следующим образом.

Связь между таблицами author и book

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

  1. Значки ключей обозначают первичный (желтый цвет) и внешний (голубой цвет) ключи.
  2. Стрелка указывает на связь между таблицами на основе внешнего ключа author_id.

Чтобы вывести две таблицы вместе, используем SQL-оператор JOIN:

sqlite> SELECT
   ...> a.first_name || ' ' || a.last_name AS author_name,
   ...> b.title AS book_title
   ...> FROM author a
   ...> JOIN book b ON b.author_id = a.author_id
   ...> ORDER BY a.last_name ASC;
Isaac Asimov|Foundation
Pearl Buck|The Good Earth
Tom Clancy|The Hunt For Red October
Tom Clancy|Patriot Games
Stephen King|It
Stephen King|Dead Zone
Stephen King|The Shining
John Le Carre|Tinker, Tailor, Solider, Spy: A George Smiley Novel
Alex Michaelides|The Silent Patient
Carol Shaben|Into The Abyss

Приведенный SQL-запрос собирает информацию из author и book, используя установленную между ними связь. Конкатенация строк SQL позволяет присвоить author_name полное имя автора. Данные, собранные запросом, сортируются в порядке возрастания по полю last_name.

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

🌉 Добавляем связи: «многие ко многим»

Автор может работать со многими издателями, а издатель – со многими авторами. Книга может быть опубликована в нескольких издательствах, а издатель может опубликовать множество разных книг. То есть в базе данных author_book_publisher.db отношение «многие ко многим» существует между авторами и издателями, а также между издателями и книгами.

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

CREATE TABLE author_publisher (
    author_id INTEGER REFERENCES author,
    publisher_id INTEGER REFERENCES publisher
);

В этом примере создается новая таблица author_publisher, которая ссылается на первичные ключи уже существующих таблиц author и publisher. То есть таблица author_publisher устанавливает отношения между автором и издателем. Аналогично создается таблица book_publisher.

Отношения между всеми необходимыми таблицами

Поскольку связь устанавливается между двумя первичными ключами, нет необходимости создавать первичный ключ для самой таблицы связей. Комбинация двух связанных ключей создает уникальный идентификатор.

Объединить таблицы можно с помощью оператора JOIN, но в случае связи многие-ко-многим требуется использовать оператор дважды:

  1. Соединяя таблицы author и author_publisher
  2. Соединяя таблицы author_publisher и publisher

Пример SQL-запроса, возвращающего список авторов и издателей, публикующих их книги:

sqlite> SELECT
   ...> a.first_name || ' ' || a.last_name AS author_name,
   ...> p.name AS publisher_name
   ...> FROM author a
   ...> JOIN author_publisher ap ON ap.author_id = a.author_id
   ...> JOIN publisher p ON p.publisher_id = ap.publisher_id
   ...> ORDER BY a.last_name ASC;
Isaac Asimov|Random House
Pearl Buck|Random House
Pearl Buck|Simon & Schuster
Tom Clancy|Berkley
Tom Clancy|Simon & Schuster
Stephen King|Random House
Stephen King|Penguin Random House
John Le Carre|Berkley
Alex Michaelides|Simon & Schuster
Carol Shaben|Simon & Schuster

Приведем пример еще одного запроса, отражающего некоторые возможности SQL:

sqlite> SELECT
   ...> a.first_name || ' ' || a.last_name AS author_name,
   ...> COUNT(b.title) AS total_books
   ...> FROM author a
   ...> JOIN book b ON b.author_id = a.author_id
   ...> GROUP BY author_name
   ...> ORDER BY total_books DESC, a.last_name ASC;
Stephen King|3
Tom Clancy|2
Isaac Asimov|1
Pearl Buck|1
John Le Carre|1
Alex Michaelides|1
Carol Shaben|1

Этот запрос возвращает список авторов и количество написанных ими книг. Список сортируется сначала по количеству книг в порядке убывания, а затем по имени автора в алфавитном порядке.

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

🧰 Работа с SQLAlchemy и объектами Python

SQLAlchemy – это мощный набор Python-инструментов для доступа к базам данных. Когда мы программируем на объектно-ориентированном языке, полезно мыслить в категориях объектов. Но между объектно-ориентированной и реляционной моделями существует концептуальный разрыв. Этот зазор покрывает объектно-реляционное отображение (ORM), предоставляемое SQLAlchemy. Между базой данных и программой Python появляется модель, преобразующая информацию из потока базы данных в объекты Python и обратно. Таким образом, SQLAlchemy позволяет мыслить в терминах объектов, при этом сохраняя мощные механизмы базы данных.

Модель. Для подключения SQLAlchemy к базе данных создается модель – класс Python, унаследованный от класса SQLAlchemy Base , определяющий отображение данных между объектами Python, возвращаемыми в результате запроса, и самими таблицами базы данных.

Представленный ниже файл models.py создает модели для представления базы данных author_book_publisher.db:

models.py
# импортируем классы, используемые для определения атрибутов модели
from sqlalchemy import Column, Integer, String, ForeignKey, Table

# импортируем объекты для создания отношения между объектами
from sqlalchemy.orm import relationship, backref

# объект для подключения ядро базы данных
from sqlalchemy.ext.declarative import declarative_base

# создаем класс, от которого будут наследоваться модели
Base = declarative_base()

# создаем модель таблицы связей таблиц author и publisher
author_publisher = Table(
    "author_publisher",
    Base.metadata,
    Column("author_id", Integer, ForeignKey("author.author_id")),
    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
)

# создаем модель таблицы связей таблиц book и publisher
book_publisher = Table(
    "book_publisher",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("book.book_id")),
    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
)

# определяем модельи классов для автора, книги и издательства
class Author(Base):
    __tablename__ = "author"
    author_id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    books = relationship("Book", backref=backref("author"))
    publishers = relationship(
        "Publisher", secondary=author_publisher, back_populates="authors"
    )

class Book(Base):
    __tablename__ = "book"
    book_id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey("author.author_id"))
    title = Column(String)
    publishers = relationship(
        "Publisher", secondary=book_publisher, back_populates="books"
    )

class Publisher(Base):
    __tablename__ = "publisher"
    publisher_id = Column(Integer, primary_key=True)
    name = Column(String)
    authors = relationship(
        "Author", secondary=author_publisher, back_populates="publishers"
    )
    books = relationship(
        "Book", secondary=book_publisher, back_populates="publishers"
    )

В приведенных в коде комментариях модели сопоставлены пяти показанным выше таблицам базы данных author_book_publisher.db.

Класс Table служит для создания связующей таблицы, то есть для создания отношений многие-ко-многим. Первый параметр – имя таблицы, определенное в базе данных, второй (Base.metadata) обеспечивает связь между функциональностью SQLAlchemy и механизмами базы данных. Остальные параметры являются экземплярами класса Column, определяющего поле таблицы по имени, типу, а также соответствие внешнему ключу.

Класс ForeignKey определяет взаимосвязь между двумя полями Column в разных таблицах. Например, следующая строка в определении таблицы author_publisher сообщает SQLAlchemy, что в таблице author_publisher есть поле с именем author_id, тип этого поля – Integer, и он является внешним ключом, связанным с первичным ключом в таблице author.

Column("author_id", Integer, ForeignKey("author.author_id"))

Функция relationship определяет отношение один-ко-многим.

books = relationship("Book", backref=backref("author"))

Первый параметр функции relationship – имя класса Book (не путайте с именем таблицы book) – это класс, к которому относится атрибут books. Это отношение сообщает SQLAlchemy, что существует связь между классами Author и Book. SQLAlchemy найдет связь в определении класса Book:

author_id = Column(Integer, ForeignKey("author.author_id"))

SQLAlchemy распознает, что это точка соединения ForeignKey между двумя классами.

Параметр backref создает атрибут author для каждого экземпляра Book. Этот атрибут ссылается на Author, с которым связан экземпляр Book.

Например, если выполнить следующий код Python, то в результате запроса будет возвращен экземпляр Book, имеющий атрибуты, которые можно использовать для вывода информации о книге:

book = session.query(Book).filter_by(Book.title == "The Stand").one_or_none()
print(f"Authors name: {book.author.first_name} {book.author.last_name}")

Наличие атрибута author в book связано с определением backref. Обратная ссылка очень удобна, когда нам нужно обратиться к родительскому объекту, а все, что у нас есть, – это дочерний экземпляр.

Другая связь у Author с классом Publisher:

publishers = relationship(
    "Publisher", secondary=author_publisher, back_populates="authors"
)

# для сравнения
books = relationship("Book", backref=backref("author"))

Как и книги, атрибут publishers обозначает совокупность издателей, связанных с автором. Первый параметр, "Publisher", сообщает SQLAlchemy, что это за класс. Второй параметр secondary сообщает SQLAlchemy, что связь с классом Publisher осуществляется через «вторичную» таблицу – таблицу author_publisher. Третий параметр back_populate сообщает SQLAlchemy о наличии дополнительной коллекции в классе Publisher, называемой authors.

💬 Запросы к базе данных

Стандартный запрос наподобие SELECT * FROM author в SQLAlchemy можно сделать вот так:

results = session.query(Author).all()

Здесь session – объект SQLAlchemy, используемый для связи с SQLite в программах Python. Здесь мы сообщаем, что хотим выполнить запрос к модели Author и вернуть все записи.

На этом этапе преимущества использования SQLAlchemy вместо простого SQL могут быть неочевидны, особенно с учетом необходимости создания моделей. Однако оказывается, что вместо списка скалярных данных, теперь мы получаем список экземпляров объектов Author с атрибутами, соответствующими заданным именам столбцов.

Коллекции книг и издателей, поддерживаемая SQLAlchemy, создает иерархический список авторов и написанных ими книг, а также издателей, которые эти книги опубликовали. За кулисами SQLAlchemy превращает вызовы объектов и методов в операторы SQL для выполнения в системе управления базами данных SQLite. И наоборот, SQLAlchemy преобразует данные, возвращаемые запросами SQL, в объекты Python.

С помощью SQLAlchemy мы можем выполнить запрос агрегирования, показанный ранее для списка авторов и количества написанных книг, следующим образом:

author_book_totals = (
    session.query(
        Author.first_name,
        Author.last_name,
        func.count(Book.title).label("book_total")
    )
    .join(Book)
    .group_by(Author.last_name)
    .order_by(desc("book_total"))
    .all()
)

Запрос session.query возвращает имя и фамилию автора, а также количество книг, написанных автором. Агрегирующий счетчик в инструкции group_by производит подсчет по фамилии автора. Результаты сортируются в порядке убывания на основе расчетной переменной с псевдонимом book_total.

👨‍💻 Пример программы

Пример программы examples/example_2/main.py содержит те же функции, что и первый программный пример, но использует SQLAlchemy исключительно для взаимодействия с базой данных SQLite author_book_publisher.db. Приведем здесь функцию main():

def main():
    """Main entry point of program"""
    # Подключение к базе данных через SQLAlchemy
    with resources.path(
        "project.data", "author_book_publisher.db"
    ) as sqlite_filepath:
        engine = create_engine(f"sqlite:///{sqlite_filepath}")
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()

    # Определяем число книг, изданных каждым издательством
    books_by_publisher = get_books_by_publishers(session, ascending=False)
    for row in books_by_publisher:
        print(f"Publisher: {row.name}, total books: {row.total_books}")
    print()

    # Определяем число авторов у каждого издательства
    authors_by_publisher = get_authors_by_publishers(session)
    for row in authors_by_publisher:
        print(f"Publisher: {row.name}, total authors: {row.total_authors}")
    print()

    # Иерархический вывод данных
    authors = get_authors(session)
    output_author_hierarchy(authors)

    # Добавляем новую книгу
    add_new_book(
        session,
        author_name="Stephen King",
        book_title="The Stand",
        publisher_name="Random House",
    )

    # Вывод обновленных сведений
    authors = get_authors(session)
    output_author_hierarchy(authors)

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

👥 Предоставление доступа к данным нескольким пользователям

К этому моменту мы узнали, как для доступа к одним и тем же данным могут использоваться pandas, SQLite и SQLAlchemy. Одним из решающих факторов при выборе между использованием плоского файла или базы данных является объем данных и количество связей между различными структурами данных. Еще один фактор, который следует учитывать, – с каким количеством пользователей мы делимся данными и вопрос критичности их рассинхронизации.

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

Последний пример – законченное веб-приложение и пользовательский интерфейс для образца существенно более крупной учебной базы данных SQLite Chinook, содержащей информацию о музыкальных исполнителях, альбомах, треках, жанрах и т. п. (11 связанных таблиц).

🌐 Использование Flask с SQLite и SQLAlchemy

Программа examples/example_3/chinook_server.py создает приложение Flask, с которым можно взаимодействовать с помощью браузера. В приложении используются следующие технологии:

  • Flask Blueprint – часть Flask для делегирования задач отдельным модулям с заранее определенной функциональностью;
  • Flask SQLAlchemy – расширение Flask, добавляющее в веб-приложения поддержку SQLAlchemy;
  • Flask_Bootstrap4 упаковывает набор интерфейсных инструментов Bootstrap, интегрируя его с веб-приложениями Flask;
  • Flask_WTF расширяет Flask с помощью WTForms, предоставляя вашим веб-приложениям удобный способ создания и проверки веб-форм;
  • python_dotenv – модуль Python, используемый приложением для чтения переменных среды из файла и сохранения конфиденциальной информации за пределами программного кода.

Пример приложения довольно большой, и лишь некоторая его часть имеет отношение к руководству. По этой причине изучение кода оставлено в качестве упражнения для читателя. Тем не менее вы можете посмотреть представленный ниже скринкаст, за которой следует HTML-код шаблона домашней страницы и Python-скрипт, который динамически предоставляет данные.

Приложение в действии: перемещение по различным меню и функциям

HTML-шаблон Jinja2, который создает домашнюю страницу приложения:

{% extends "base.html" %}

{% block content %}
<div class="container-fluid">
  <div class="m-4">
    <div class="card" style="width: 18rem;">
      <div class="card-header">Create New Artist</div>
      <div class="card-body">
        <form method="POST" action="{{url_for('artists_bp.artists')}}">
          {{ form.csrf_token }}
          {{ render_field(form.name, placeholder=form.name.label.text) }}
          <button type="submit" class="btn btn-primary">Create</button>
        </form>
      </div>
    </div>
    <table class="table table-striped table-bordered table-hover table-sm">
      <caption>List of Artists</caption>
      <thead>
        <tr>
          <th>Artist Name</th>
        </tr>
      </thead>
      <tbody>
        {% for artist in artists %}
        <tr>
          <td>
            <a href="{{url_for('albums_bp.albums', artist_id=artist.artist_id)}}">
              {{ artist.name }}
            </a>
          </td>
        </tr>
        {% endfor %}
      </tbody>
    </table>
  </div>
</div>
{% endblock %}

Файл Python, ответственный за отображение страницы:

examples/example_3/app/artists/routes.py
from flask import Blueprint, render_template, redirect, url_for
from flask_wtf import FlaskForm
from wtforms import StringField
from wtforms.validators import InputRequired, ValidationError
from app import db
from app.models import Artist

# Настраиваем blueprint
artists_bp = Blueprint(
    "artists_bp", __name__, template_folder="templates", static_folder="static"
)

def does_artist_exist(form, field):
    artist = (
        db.session.query(Artist)
        .filter(Artist.name == field.data)
        .one_or_none()
    )
    if artist is not None:
        raise ValidationError("Artist already exists", field.data)

class CreateArtistForm(FlaskForm):
    name = StringField(
        label="Artist's Name", validators=[InputRequired(), does_artist_exist]
    )

@artists_bp.route("/")
@artists_bp.route("/artists", methods=["GET", "POST"])
def artists():
    form = CreateArtistForm()

    # Проверяем валидность формы
    if form.validate_on_submit():
        # "Создаем" нового артиста
        artist = Artist(name=form.name.data)
        db.session.add(artist)
        db.session.commit()
        return redirect(url_for("artists_bp.artists"))

    artists = db.session.query(Artist).order_by(Artist.name).all()
    return render_template("artists.html", artists=artists, form=form,)
Создание REST API сервера
Такой же подход с применением Flask или Django можно использовать для создания веб-сервера, предоставляющего REST API. Соответствующие подводки даны в статьях Python REST APIs With Flask, Connexion, and SQLAlchemy и Create a Super Basic REST API with Django Tastypie.

Заключение

Разумно задаться вопросом: является ли SQLite правильным выбором в качестве серверной части базы данных для веб-приложения. На веб-сайте SQLite указано, что SQLite – хороший выбор для сайтов, обслуживающих около 100 тыс. обращений в день. Если на вашем сайт посещений больше, в первую очередь вас стоит поздравить 🎉

Если вы реализовали веб-сайт с помощью SQLAlchemy, то можно перенести данные из SQLite в другую базу данных, такую ​​как MySQL или PostgreSQL. Описательные модели данных SQLAlchemy останутся прежними.

Источники

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ

admin
11 декабря 2018

ООП на Python: концепции, принципы и примеры реализации

Программирование на Python допускает различные методологии, но в его основе...
admin
23 февраля 2017

SQL за 20 минут

Предлагаем вашему вниманию статью с кричащим названием "SQL за 20 минут". К...
admin
13 февраля 2017

Программирование на Python: от новичка до профессионала

Пошаговая инструкция для всех, кто хочет изучить программирование на Python...