🐍🗄️ Управление данными с помощью 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) представляют собой строки простого текста, в которых элементы данных разделены запятыми. Каждая строка текста представляет собой строку данных, а каждое значение в строке, отделенное от остальных запятой соответствует одному из полей таблицы.

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.

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

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

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

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

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

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

Рассмотрим функцию main() программы, находящейся в упомянутом репозитории по относительному адресу 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.

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

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

SQL – это декларативный язык, используемый для создания, управления и поиска данных, содержащихся в базе. Декларативный язык описывает, что должно быть выполнено, а не то, как это нужно сделать. Мы увидим примеры операторов 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
);

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

Представим, что далее мы сделали 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';

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

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

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

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

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

Приведенная выше диаграмма представляет собой простую диаграмму отношений сущностей (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:

# импортируем классы, используемые для определения атрибутов модели
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, ответственный за отображение страницы:

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,)

Заключение

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

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

Источники

• https://realpython.com/python-sqlite-sqlalchemy/