Управление данными за счет “Плоских файлов”, SQLite и SQLAlchemy
Представьте, сколь кардинально изменилась бы наша жизнь, если бы вдруг не стало Internet, мобильной связи и уже привычных нам супермаркетов. Когда взамен на получение нужной справки пришлось бы предоставлять десятки других бумажек, а для оплаты коммуналки или сдачи налоговых отчетов следовало бы выстаивать многочасовые очереди в тесной толпе несчастных ожидающих. Все это безусловно стало бы реальностью, если бы человечество не придумало, как можно сохранять и обслуживать большие объемы информации в виде баз данных.
Нам уже редко когда сегодня посчастливится наткнуться на одиноко затерявшиеся в сети раритеты в виде статических сайтов или прикладных программ, не требующих поддержки каких-либо баз данных. Напротив, сейчас уже любое, действительно функционально полезное и востребованное, программное обеспечение (ПО) просто обязано взаимодействовать если не с базами данных, то хотя бы со специальными (плоскими) файлами, хранящими вводную и/или выводную информацию для этого ПО.
В Python для сохранения и извлечения данных в ходе выполнения наших программ, предусмотрено достаточное количество различных средств. Но, мы в этой статье остановимся лишь на описании некоторых функций программных библиотек по работе с плоскими файлами и с классами SQLAlchemy на основе использования компактной SQL системы управления данными (СУБД) SQLite. В частности, здесь будут рассмотрены основные возможности SQLite и SQLAlchemy, предоставляющие вполне достаточный арсенал функций для хранения и обслуживания небольших объемов данных в одном файле. Это, в свою очередь, позволит нам в ряде случаев обходится без использования дорогостоящих серверов, работающих с крупными коммерческими базами данных.
Кроме применения СУБД SQLite, для случаев с совсем уж небольшими объемами данных, в данной статье будет рассмотрено использование так называемых плоских файлов, позволяющих хранить наши данные в строго заданном формате. Эти плоские файлы характерны тем, что имея текстовые (или, в меньшей степени, двоичные) данные, также обладают способностью с помощью средств стандартных библиотек Python легко считываться, сохранятся и дополняться, как в стандартных форматах типа CSV, JSON, XML и т.д., так и в индивидуальных (пользовательских форматах).
Таким образом в рамках этой внушительной по размерам статьи мы с вами рассмотрим, как использовать:
- Плоские файлы для хранения данных
- SQL для расширений функций доступа к данным
- SQLite для хранения данных в виде реляционной базы данных с доступом к ней через SQL
- SQLAlchemy для синхронизации объектов Python и записями в SQLite
Примечание. С целью закрепления полученных навыков, вам потребуется скачать соответствующий архив с образцами кода и данных, используемыми в практических примерах к данной статье. О том, как это сделать и, как затем запустить скаченные вами примеры кода с данными для них, вы сможете узнать открыв соответствующий PDF файл с инструкцией по формированию надлежащей виртуальной среды для выполнения практических примеров из данной статьи.
Следует отметить, что впоследствии, изучение образцов кода из упомянутого выше архива может оказаться для вас весьма полезным не только в плане более глубокого понимания изложенного в статье материала, но и с точки зрения своеобразного фундамента на начальных этапах разработки будущих приложений в сфере баз данных.
Использование плоских файлов для работы с данными
Плоский файл представляет собой строго последовательные данные, у которых нет ни внутренней иерархии, ни каких-либо ссылок на внутренние элементы или внешние файлы. Особенность этих файлов заключается в том, что с одной стороны, они легко обрабатываются средствами стандартных библиотек Python, а с другой стороны, являются довольно читабельными для неискушенных пользователей даже при их открытии в сыром (неформатированном) виде. К тому же, плоские файлы предусматривают еще и возможность работы с полями неограниченной (нефиксированной) ширины. Это, в частности, достигается, как за счет использования соответствующих форматов плоских файлов, так и за счет использования дополнительных функций и классов, позволяющих нашим программам анализировать содержание этих файлов.
На базовом уровне Python располагает исчерпывающим спектром функций для поддержки взаимодействия наших программ с плоским файлами. Так, например, предоставляемая Python возможность чтения данных из файлов позволяет восстанавливать наши приложения до нужного нам состояния при их повторных последующих запусках. Возможность же сохранения данных в файлы позволяет обмениваться информацией между пользователями и сайтами, где запускаются наши приложение. Но, прежде чем наши программы смогут работать с необходимыми им плоским файлами, они должны знать структуру, согласно которой в соответствующих файлах размещаются данные. Только так, создаваемые нами программы смогут не только понять и проанализировать нужные им плоские файлы, но и записать в них положенную информацию по результатам работы программ. Обычно, структура (формат) плоских файлов предопределяется расширением их имени. Следовательно, мы на этапе создания наших программ уже должны знать с форматами каких плоских файлов эти программы будут иметь дело и, соответственно исходя из этого осуществлять дальнейшее их программирование.
Давайте, для примера, более подробно рассмотрим формат файла со значениями, разделенными запятыми (CSV). Файл этого формата представляет собой своеобразную таблицу, состоящую из строк обычного текста, в которых запятыми формируются столбцы данной таблицы. Таким образом, каждое отдельное значение файла CSV, характеризуемое номером строки и количеством запятых перед ним, будет ничем иным, как соответствующей ячейкой (полем) для нашей таблицы. Как уже было сказано выше, каждая запятая в CSV файле фактически является разделителем между столбцами в строках нашей воображаемой таблицы, поэтому, для корректного построения такой таблицы, количество запятых в вышеназванном файле от строки к строке должно быть одинаковым.
Ниже для примера структуры CSV приведен файл author_book_publisher.csv, который также будет нами использован в качестве базы данных (плоского файла) для первого примера программы в этой статье:
Имя,Фамилия,Название_книги,Издатель
Айзек,Азимов,Фонд,Рэндом Хаус
Перл,Бак,Хорошая Земля,Рэндом Хаус
Перл,Бак,Хорошая Земля,Саймон и Шустер
Том,Клэнси,«Охота за красным октябрем»,Беркли
Том,Клэнси,Игры патриотов,Саймон и Шустер
Стивен,Кинг,Оно,Рэндом Хаус
Стивен,Кинг,Оно,Пингвин Рэндом Хаус
Стивен,Кинг,Мертвая зона,Рэндом Хаус
Стивен,Кинг,Сияние,Пингвин Рэндом Хаус
Джон,Ле Карре,«Тинкер; портной; солдат; шпион: роман Джорджа Смайли»,Беркли
Алекс,Майклидес,Молчаливый пациент,Саймон и Шустер
Кэрол,Шабен,В бездну,Саймон и Шустер
Как видно из содержимого нашего CSV файла, первая строка в нем представляет собой список имен столбцов для данных, размещаемых в последующих его строках. Таким образом, начиная со второй своей строки, этот файл содержит непосредственные данные, каждая строка которых в терминах таблицы соответствует одной ее записи.
Примечание. Хотя все авторы, их книги и фирмы издателей в приведенном выше CSV файле являются реальными, взаимосвязь между соответствующими названиями книг и опубликовавшими их издателями здесь вымышлена и смоделирована лишь для целей данной статьи.
Далее на примере вышеприведенного CSV файла мы с вами рассмотрим некоторые преимущества и недостатки использования плоских файлов при работе с используемыми в наших программах данными.
Преимущества плоских файлов
Пожалуй, наиболее очевидным преимуществом плоских файлов является возможность их создания и обработки с помощью любого текстового редактора. Это предполагает беспроблемное восприятие, содержащейся в вышеназванных файлах информации, позволяющее, не только легко их редактировать, но и выявлять в них возможные несоответствия или проблемы.
Массовое же распространение плоских файлов и стандартизация их форматов приводят к тому, что многие популярные приложения предусматривают возможность конвертирования данных из этих файлов в файлы собственных форматов. Так, например, Excel позволяет осуществлять импорт и экспорт файлов CSV в рамках их взаимодействия со своими электронными таблицами (файлами с расширением XLSX). Таким образом, плоские файлы с относительно простой (двумерной) информационной моделью не требуют специального программного обеспечения для своей обработки и, следовательно, будучи всегда доступными для любых пользователей, могут быть легко переданы им через E-mail или всевозможные чаты.
И, наконец, почти в каждом языке программирования есть свои инструменты и библиотеки, облегчающие работу с наиболее распространенными форматами плоских файлов. Так, например, имея свой встроенный csv модуль и мощнейший внешний модуль pandas для обработки и анализа данных, Python обеспечивает нам исчерпывающий арсенал функций для управления данными, хранящимися в плоских файлах CSV формата.
Недостатки плоских файлов
По мере разрастания содержимого плоских файлов, преимущества их применения все более начинают нейтрализоваться предопределенными в таких обстоятельствах проблемами. Очевидным проявлением этого является то, что с увеличением объема данных в вышеназванных файлах, все более проблематичным становится программное осуществление поиска и редактирования данных, не говоря уже о модификации их структуры. Так, для того чтобы просто изменить данные в большом (гигабайтом) файле, наше приложение зачастую должно будет сначала считать данные из него в память, затем внести в эти данные соответствующие изменения и, только потом сохранить их в тот же или в другой файл. Все это, помимо непомерной траты вычислительных ресурсов, будет занимать безумно долгое время.
Еще одной проблемой использования плоских файлов со сложными (переплетающимися) данными является то, что между отдельными их частями в рамках предопределённой этими данными информационной модели всегда должна формироваться соответствующая система явных отношений (связей). Причем, под такие системы отношений в создаваемых нами программах еще и нужно будет разрабатывать отдельный код, который бы управляя этими отношениями данных в файле, всегда поддерживал бы их в актуальном состоянии.
Кроме того, плоскими файлами со сложными (переплетающимися) данными крайне проблематично поделиться с другими людьми, которые для того, чтобы воспользоваться ими должны быть в курсе разработанной нами системы отношений между отдельными частями данных в соответствующих файлах. Но, даже если мы этим людям (пользователям) и предоставим соответствующую систему расшифровки отношений между данными, то таким пользователям все-равно еще нужно будет разъяснить, как работать со специфическими приложениями или же даже, как их программировать для доступа к интересующим пользователей данным.
Пример плоского файла
В этом подразделе нашей статьи мы с вами в качестве примера рассмотрим работу Python программы main.py, размещаемой в каталоге …\project\examples\example_1\. Для получения данных и их информационной модели взаимосвязей, эта программа использует, приводимый раннее файл author_book_publisher.csv, содержащий список авторов с написанными ими книгами и издателями, которые эти книги опубликовали.
Примечание. Файлы данных с расширениями CSV и DB, использованные в примерах для данной статьи, доступны в каталоге
…\project\data. В этой связи дополнительно нужно также обратить внимание на каталог…\project\build_data, где присутствует программный файл build_author_book_publisher_sqlite.py, позволяющий сгенерировать базу данных SQLite, размещенную в файле author_book_publisher.db. Такая генерация с помощью build_author_book_publisher_sqlite.py осуществляется из данных, уже описанного прежде CSV файла author_book_publisher.csv. Следовательно, соответствующий программный файл будет для вас особенно полезен после внесенных в author_book_publisher.db изменений, в случае если вы захотите вернуть базу данных в первоначальное состояние, инициализировав ее значениями из одноименного CSV файла.С целью получения доступа к материалам практических примеров для данной статьи вам следует предварительно скачать соответствующий архив с кодом и данными для этих примеров, а затем создать среду для их выполнения. О том, как это сделать вы сможете узнать открыв соответствующий PDF файл с инструкцией.
Упомянутый прежде в этой статье CSV файл представляет собой сравнительно небольшой массив данных, состоящий всего из нескольких строк, в каждой из которых указывается тот или иной автор, написанная им книга и издатель, который эту книгу опубликовал. Кроме того, можно заметить, что данные из этого файла имеют ряд следующих особенностей:
- Авторы Стивен Кинг и Том Клэнси представлены здесь несколькими своими книгами и, потому указываются в файле более одного раза.
- Для таких авторов, как Стивен Кинг и Перл Бак одни и те же их книги были изданы несколькими издателями, что также приводит к тому, что данные авторы повторяются в нашем файле несколько раз.
Такие дублированные данные как раз и создают отношения между различными иными частями наших данных. Например, один автор может написать много книг, а одно издательство может работать с несколькими авторами. Таким образом, авторы и издатели в нашем примере разделяют отношения с отдельными книгами.
Свидетельством наличия таких отношений между частями данных бесспорно являются повторяющиеся поля, которые неоднократно возникают в разных строках нашего author_book_publisher.csv файла в виде дублируемых авторов, названий книг и фирм издателей. Таким образом, вследствие таких повторяющихся данных, двумерная таблица в нашем CSV файле фактически превращается в некий многомерный массив.
На особенностях отношений между данными внутри их информационных моделей мы еще более подробно остановимся ниже, когда из нашего CSV файла будем создавать соответствующий файл базы данных SQLite.
Следует отметить, что упоминавшаяся прежде программа main.py из каталога …\project\examples\example_1\ в своем коде изначально уже предусматривает все оговоренные выше особенности отношений между данными в файле author_book_publisher.csv. Базируясь именно на этих отношениях, данная программа формирует два разных перечня издателей, один из которых содержит количество опубликованных ими книг, а второй – количество авторов, книги которых эти издатели опубликовали.
После вывода двух вышеназванных перечней издателей, наша программа main.py использует внешний Python модуль treelib, позволяющий отразить информацию по авторам, их книгам и издателям, их опубликовавшим, в виде древовидной иерархической структуры.
Далее, наша программа добавляет к уже имеющимся данным новую книгу и повторно, уже с учетом этой книги, отображает все данные в виде древовидной иерархической структуры. Ниже приведен листинг main() функции (точки входа) для этой программы:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | |
Построчно, вышеприведенный код можно разбить на ряд групп, предполагающих следующие действия:
- Строки с 4 по 7 считывают данные из файла author_book_publisher.csv и сохраняют их в виде двухмерной маркированной pandas структуры (DataFrame) в переменную data.
- Строки с 10 по 13 выводят на консоль количество книг, изданных каждым издателем.
- Строки с 16 по 19 выводят на консоль количество авторов, книги которых были опубликованы каждым издателем.
- Строка 22 выводит на консоль, отсортированную по авторам, древовидную иерархическую структуру с данными о книгах и опубликовавшим их издателям.
- Строки с 25 по 30 добавляют к нашей DataFrame структуре, находящейся в переменной data, новую книгу с указанием ее автора и издателя.
- Строка 33 выводит на консоль, отсортированную по авторам, обновленную древовидную иерархическую структуру, включающую книгу, которая была только что добавлена в строках 25 – 30.
Таким образом, после запуска нашей программы main.py на консоль будет выведен следующий ее результат:
(venv) PS > python main.py
Издатель: Рэндом Хаус, всего книг: 4
Издатель: Саймон и Шустер, всего книг: 4
Издатель: Беркли, всего книг: 2
Издатель: Пингвин Рэндом Хаус, всего книг: 2
Издатель: Саймон и Шустер, всего авторов: 4
Издатель: Рэндом Хаус, всего авторов: 3
Издатель: Беркли, всего авторов: 2
Издатель: Пингвин Рэндом Хаус, всего авторов: 1
Авторы
├── Айзек Азимов
│ └── Фонд
│ └── Рэндом Хаус
├── Алекс Майклидес
│ └── Молчаливый пациент
│ └── Саймон и Шустер
├── Джон Ле Карре
│ └── «Тинкер; портной; солдат; шпион: роман Джорджа Смайли»
│ └── Беркли
├── Кэрол Шабен
│ └── В бездну
│ └── Саймон и Шустер
├── Перл Бак
│ └── Хорошая Земля
│ ├── Рэндом Хаус
│ └── Саймон и Шустер
├── Стивен Кинг
│ ├── Мертвая зона
│ │ └── Рэндом Хаус
│ ├── Оно
│ │ ├── Пингвин Рэндом Хаус
│ │ └── Рэндом Хаус
│ └── Сияние
│ └── Пингвин Рэндом Хаус
└── Том Клэнси
├── «Охота за красным октябрем»
│ └── Беркли
└── Игры патриотов
└── Саймон и Шустер
Авторы
├── Стивен Кинг
│ ├── Мертвая зона
│ │ └── Рэндом Хаус
│ ├── Оно
│ │ ├── Пингвин Рэндом Хаус
│ │ └── Рэндом Хаус
│ ├── Противостояние
│ │ └── Рэндом Хаус
│ └── Сияние
│ └── Пингвин Рэндом Хаус
Для того, чтобы несколько ужать объем результирующего вывода нашей программы, вторая древовидная иерархическая структура здесь приведена в усеченном виде и показывает только ветку книг, автором которых является Стивен Кинг. Эта ветка наглядно показывает, что по сравнению с первым вариантом, тут, как и следовало ожидать, добавилась новая книга Стивена Кинга – «Противостояние», опубликованная издателем «Рэндом Хаус».
Как мы уже увидели из приведенного раннее листинга, функция main() предусматривает внутри своего кода вызов еще целого ряда других функций, которые фактически выполняют основную часть работы нашей программы. Рассмотрим эти функции по порядку их вызова в main() и, первой функцией, с которой мы начнем будет функция get_data():
def get_data(filepath):
"""Получение данных об издателях из CSV-файла"""
return pd.read_csv(filepath)
Эта функция в качестве аргумента принимает путь к файлу CSV, данные из которого затем переносятся в так называемый DataFrame (двухмерную маркированную pandas структуру). В итоге, сформировавшейся здесь DataFrame возвращается обратно в функцию main(), инициализируя при этом переменную data, которая в последствии передается другим функциям, составляющим программу.
Функция get_books_by_publisher() группирует, принимаемую в переменной data структуру DataFrame по колонке “Издатель” и сортирует полученные таким образом значения по ascending флагу. В итоге, данная функция передает в main() массив одномерных маркированных pandas структур (Series) со сведениями о количестве опубликованных каждым издателем книг:
def get_books_by_publisher(data, ascending=True):
"""Возвращение книг, отобранных по каждому издателю в виде
массива одномерных маркированных pandas структур (Series)
"""
return data.groupby("Издатель").size().sort_values(ascending=ascending)
Функция get_authors_by_publisher() делает, в сущности, то же самое, что и предыдущая функция, но только в отношении авторов, книги которых были опубликованы издателями:
def get_authors_by_publisher(data, ascending=True):
"""Возвращение авторов, работающих с каждым издателем в виде
массива pandas структур Series
"""
return (
data.assign(name=data.Имя.str.cat(data.Фамилия, sep=" "))
.groupby("Издатель")
.nunique()
.loc[:, "name"]
.sort_values(ascending=ascending)
)
В рамках функции add_new_book() к нашей DataFrame структуре в переменной date добавляется новая книга, сведения о которой передаются в данную функцию в виде ее дополнительных аргументов. Предварительно, код этой функции также проверяет, существует ли уже в нашем DataFrame для новой книги идентичные записи об авторе, книге или издателе. Если таких записей нет, то данная функция добавляет в DataFrame новую книгу и возвращает его обновленный вариант в главную main() функцию:
def add_new_book(data, author_name, book_title, publisher_name):
"""Добавление в подборку сведений об издателях новой книги"""
# Проверяем, есть ли уже в имеющихся сведениях добавляемая книга?
first_name, _, last_name = author_name.partition(" ")
if any(
(data.Имя == first_name)
& (data.Фамилия == last_name)
& (data.Название_книги == book_title)
& (data.Издатель == publisher_name)
):
return data
# Добавляем новою книгу к подборке уже имеющихся сведений об издателях
line = {
'Имя': [first_name],
'Фамилия': [last_name],
'Название_книги': [book_title],
'Издатель': [publisher_name]
}
new_line = pd.DataFrame(line)
return pd.concat((data, new_line), axis=0)
С целью вывода сведений из CSV файла в виде иерархического списка применяется функция output_author_hierarchy(), которая использует для этого вложенные for циклы, перебирающие соответствующие структуры данных для каждого из трех предопределенных уровней этого списка. Также при построении данного списка используется внешний Python модуль treelib, благодаря которому все, предварительно сформированные соответствующим образом, сведения об авторах, их книгах и опубликовавших эти книги издателях оформляются в виде трехуровневого древовидного иерархического списка.
def output_author_hierarchy(data):
"""Вывод данных об авторах книг в виде иерархического списка"""
authors = data.assign(
name=data.Имя.str.cat(data.Фамилия, sep=" ")
)
authors_tree = Tree()
authors_tree.create_node("Авторы", "authors")
for author, books in authors.groupby("name"):
authors_tree.create_node(author, author, parent="authors")
for book, publishers in books.groupby("Название_книги")["Издатель"]:
book_id = f"{author}:{book}"
authors_tree.create_node(book, book_id, parent=author)
for publisher in publishers:
authors_tree.create_node(publisher, parent=book_id)
# Вывод иерархического списка с данными об авторах
authors_tree.show()
Рассмотренная нами здесь программа на примере соответствующего CSV файла безусловно довольно наглядно демонстрирует функциональную мощь внешнего Python модуля pandas в сочетании с такими источниками данных, как плоские файлы для эффективного чтения и обработки данных.
Но, это далеко не предел в возможностях управления данными и, теперь настало время приступить ко второму этапу освоения этих возможностей на основе изучения баз данных. Начнем же мы этот второй этап с создания идентично функционирующей программы для тех же, уже знакомых нам данных, но только уже сохраненных не в CSV файле, а в базе данных SQLite и, обрабатываемых не с помощью pandas, а с применением модуля SQLAlchemy.
Работа с данными с помощью SQLite
Как мы убедились раннее на примере файла author_book_publisher.csv, в плоских файлах всегда присутствует существенная доля избыточных данных. Например, все данные по книге Перл Бака “Хорошая Земля” указываются в нашем CSV файле дважды, лишь только из-за того, что она была опубликована двумя разными издателями.
Но, представьте, как колоссально разросся бы объем информации в нашем CSV файле, если бы в нем дополнительно указывались бы такие связанные с авторами книг данные, как их адреса, номера телефонов, даты публикаций и ISBN написанных ими книг, а также номера телефонов и, возможно, годовой доход издателей, опубликовавших книги этих авторов. Ведь, вся эта информация должна были бы дублироваться для каждого автора, книги или издателя, являющихся для наших данных (нашей информационной модели) корневыми элементами.
Помимо непомерного разрастания объемов нашего файла, возникают еще громадные проблемы в отношении поддержания данных этого файла в актуальном состоянии. Так, если Стивен Кинг захотел бы сменить свое имя на тот или иной псевдоним, нам пришлось бы в нашем файле не только обновить все записи, содержащие его имя, но и убедиться в том, что все его вновь введенные псевдонимы не содержат опечаток.
Еще более худшим, чем дублирование данных может быть добавление связей к новым данным. Представьте себе ситуацию, когда мы решили добавить телефоны для авторов, но оказалось, что эти телефоны еще могут иметь ряд таких статусов, как: домашний, рабочий, мобильный, соседский и т.д. В такой ситуации, каждый новый статус телефонов должен предопределять отдельную связь с автором. Это, в свою очередь, будет вынуждать для телефона того или иного автора добавлять в наш файл столько строк (записей), сколько номеров телефонов с разными статусами этот автор нам предоставил.
Вышеописанная проблема в системах управления базами данных (СУБД) предопределила необходимость осуществления для каждой отдельной базы данных так называемого процесса нормализации, предполагающего разбиения данных на части с последующим установлением четких взаимосвязей между ними. Проведение нормализации в рамках проектирования каждой базы данных, в частности, позволяет уменьшить избыточность и повысить целостность тех данных, под которые эта база данных проектируется. К тому же, когда структура этой базы данных в будущем будет расширяться новыми типами данных, осуществление ее предварительной нормализации позволит все нужные при этом изменения ее структуры свести, практически, к минимуму.
Все основные функции баз данных далее мы рассмотрим на примере доступной в Python СУБД SQLite, которая, согласно официальному сайту этой базы данных, используется чаще, чем все другие СУБД вместе взятые. Программный пакет SQLite представляет собой полнофункциональную систему управления реляционными базами данных (RDBMS), работающую с одним файлом и поддерживающую все основные функции серверов коммерческих баз данных.
Преимущество данного программного пакета заключается еще и в том, что для своей работы он не требует отдельного сервера, а формат его файлов базы данных является кроссплатформенным и доступен для любого языка программирования, поддерживающего SQLite.
Вся вышеприведенная теория безусловно интересна отнюдь не только для повышения уровня общей эрудиции. Поэтому, далее мы с вами более подробно “окунемся” в практические вопросы работы с базами данных.
Создание структуры базы данных
Безусловно, для переноса содержимого файла author_book_publisher.csv в базу данных SQLite можно было бы просто продублировать в нее структуру этого CSV файла в виде одной таблицы. Но, тогда мы потеряли бы большую часть тех преимуществ, которые может дать нам эта база данных по сравнению с плоскими файлами.
В частности, для такой реляционной СУБД, как SQLite, вследствие создания лишь одной таблицы на базе вышеназванного CSV файла мы утеряли бы всю мощь, используемого в этом СУБД, структурированного языка запросов (SQL) для осуществления различных выборок данных, их фильтрации и сортировки. Вообще, следует отметить, что предопределенная в реляционных СУБД способность хранения данных в виде структур с многочисленными и связанными между собой таблицами, порождает неограниченные возможности по взаимодействию с хранимыми в этих СУБД данными на основе использования языка SQL.
Система управления базами данных SQLite точно также, как и другие реляционные СУБД обеспечивает взаимодействие со своими таблицами в базе данных с помощью языка SQL, который фактически встроен в каждый файл (отдельную базу данных) вышеназванной СУБД. Это, фактически означает, что любой язык программирования, который может работать с файлом SQLite, как с базой данных, также может использовать SQL для взаимодействия с таблицами этой базы данных.
Взаимодействие с базой данных с помощью языка SQL
SQL – это декларативный язык программирования, который применяется для создания, модификации и управления данными в реляционной базе данных, управляемой какой-либо СУБД, к примеру, SQLite. В отличии от большинства языков программирования, особенность декларативных языков, подобных SQL заключается в том, что они декларируют то, что должно быть выполнено, а не то, как это должно быть выполнено. Поэтому для получения большей практики, далее в этой статье мы с вам подробно рассмотрим примеры операторов SQL и наглядно увидим всю его мощь при взаимодействии с конкретными данными из файла author_book_publisher.csv.
Структурирование базы данных с помощью SQL
Как уже упоминалось раннее, при проектировании любой реляционной базы данных, неминуем процесс, так называемой нормализации, обеспечивающей не только использование всех преимуществ SQL, но также позволяющий уменьшить избыточность и повысить целостность тех данных, под которые соответствующая база данных проектируется. Такая же нормализация предполагается и при переносе данных из нашего файла author_book_publisher.csv в базу данных на SQLite, когда мы должны будем разделить имя и фамилию авторов, их книги и фирмы издателей в отдельные таблицы. Но, прежде, давайте разберёмся в особенностях построения таблиц и механизмов их взаимосвязей внутри реляционных баз данных.
Концептуально, данные в любой реляционной СУБД хранятся в виде двумерных табличных структур (таблиц), каждая из которых состоит из строк (записей), разбитых на столбцы (поля), содержащих максимально не делимую информацию.
Каждое поле в таблицах должно принимать только те данные, под тип которых оно было изначально инициализировано при создании этих таблиц. Таким образом, поля в таблицах должны иметь тот или иной конкретно установленный тип данных, совпадающий с одним из таких, предопределенных в СУБД типов, как: текст, целые числа, числа с плавающей запятой и т.д. Файлы CSV, в отличии от вышеописанной типизации полей в СУБД, имеют лишь текстовые поля, которые для назначения им соответствующего типа данных должны всегда предварительно анализироваться, создаваемым нами программным обеспечением.
Еще одной особенностью построения таблиц и обеспечения их взаимосвязей в реляционных СУБД является наличие для каждой записи этих таблиц так называемого первичного ключа, обеспечивающего ее уникальность. Таким образом первичный ключ представляет собой уникальный идентификатор записи в таблицы, который схож с ключами Python словарей. Эти первичные ключи, как правило, генерируются средствами большинства современных СУБД автоматически в виде возрастающих целочисленных значений для каждой записи, вставленной в те или иные таблицы баз данных.
В то же время, процесс автоматической генерации первичных ключей средствами СУБД отнюдь не является обязательным. Так, если данные, хранящиеся в поле той или иной таблицы, сами по себе являются уникальными среди всех других ее данных, то данное поле в этой таблице может выступать первичным ключом. К примеру, в таблице, содержащей данные о книгах, первичным ключом может выступать поле с международным стандартным номером (ISBN) этих книг.
Создание таблиц с помощью SQL
Для создания по данным вышеназванного CSV файла трех таблиц, каждая из которых соответственно представляет, авторов, их книги и издателей этих книг, можно воспользоваться следующими операторами SQL:
CREATE TABLE author (
author_id INTEGER NOT NULL PRIMARY KEY,
Имя VARCHAR,
Фамилия VARCHAR
);
CREATE TABLE book (
book_id INTEGER NOT NULL PRIMARY KEY,
author_id INTEGER REFERENCES author,
Название_книги VARCHAR
);
CREATE TABLE publisher (
publisher_id INTEGER NOT NULL PRIMARY KEY,
Издатель VARCHAR
);
Из вышеприведенного SQL кода мы можем заметить, что в нем отсутствуют какие-либо файловые операции, переменные и структуры для их хранения. Операторы в этом коде описывают лишь то, что мы хотим создать три таблицы с определенными, присущими только для них, атрибутами. Механизм же базы данных исходя из задаваемых нами атрибутов таблиц уже сам потом определяет алгоритм, переменные и команды, благодаря которым создаются эти самые, необходимые нам таблицы.
После создания вышеназванных таблиц и заполнения их данными из файла author_book_publisher.csv мы легко можем получить к ним доступ и запросить необходимые данные с помощью соответствующего SQL оператора SELECT. Так, например, благодаря нижеприведенной команде (также называемой запросом) с использованием оператора SELECT и подстановочного знака *, из таблицы author можно получить все, содержащиеся в ней данные с последующим их выводом на консоль:
SELECT * FROM author;
Примечание. Для выполнения нижеследующих практических примеров, использующих файл базы данных …\project\data\author_book_publisher.db вам потребуется доступ к СУБД SQLite через соответствующую командную консоль, открытие которой обеспечивается с помощью программной утилиты sqlite3. Архив с этой утилитой, например, для операционной системы Windows можно скачать здесь. Затем, предварительно распаковав этот архив, нужно переписать из него к себе в подкаталог
…\project\data\лишь файл sqlite3.exe. Таким образом, используя подкаталог, где записана ваша база данных author_book_publisher.db в качестве текущего, вы сможете легко запустить все примеры этой статьи, касающиеся применения SQL.
Давайте попробуем войти в командную консоль SQLite, одновременно открыв при этом наш файл базы данных author_book_publisher.db. Для этого находясь в подкаталоге *…\project\data* через терминал (командную оболочку) нашего компьютера просто выполним следующую команду:
PS > sqlite3 author_book_publisher.db
После запуска вышеприведенной команды мы с вами войдем в командную консоль SQLite, где все последующие команды SQL должны будут уже непосредственно относится к соответствующей базе данных, находящейся в файле author_book_publisher.db. Так, например, далее для вывода всего содержимого из таблицы author мы можем воспользоваться уже знакомой нам командой с оператором SELECT и подстановочного знака *. Для завершения же работы с командной консолью нам достаточно всего лишь ввести в ней команду .q:
sqlite> SELECT * FROM author;
1|Айзек|Азимов
2|Перл|Бак
3|Том|Клэнси
4|Стивен|Кинг
5|Джон|Ле Карре
6|Алекс|Майклидес
7|Кэрол|Шабен
sqlite> .q
PS >
Обратите внимание, что, в отличии от имеющихся повторений в нашем CSV файле, в выведенном содержимом таблицы author каждый автор присутствует лишь один раз. Это результат той самой нормализации, о которой мы говорили раннее.
Актуализация таблиц баз данных с помощью SQL
Кроме уже знакомого нам оператора запроса SELECT, SQL предусматривает еще целый ряд операторов по актуализации содержимого таблиц формирующих те или иные базы данных. Наиболее популярными (часто используемыми) среди этих операторов являются те, которые позволяют добавлять к таблицам новые данные, а также совершать обновление или удаление уже имеющихся данных. Например, для добавления к таблице author нового автора мы можем воспользоваться командой со следующим оператором:
INSERT INTO author
(Имя, Фамилия)
VALUES ('Пол', 'Мендес');
Приведенный выше SQL оператор INSERT INTO вставляет значения Пол и Мендес в соответствующие столбцы Имя и Фамилия таблицы author.
Как мы видим, оператор INSERT INTO в своих атрибутах для таблицы author не требует указания первичного ключа в поле (столбце) author_id. Это вызвано тем, что наша база данных автоматически генерирует значение этого поля и вставляет его в новую запись параллельно с выполнением вышеназванного оператора.
Обновление уже имеющихся данных в таблицах баз данных, также является достаточно несложным процессом. Например, давайте реализуем уже описываемый нами раннее пример и, изменим фактическое имя автора Стивен Кинг на псевдоним Ричард Бахман, под которым этот автор, скажем, пожелал значиться в наших записях. Вот как будет выглядеть SQL оператор для реализации данной задачи:
UPDATE author
SET Имя = 'Ричард', Фамилия = 'Бахман'
WHERE Имя = 'Стивен' AND Фамилия = 'Кинг';
В этом коде оператор UPDATE за счет использования в своей структуре условного оператора WHERE Имя = 'Стивен' AND Фамилия = 'Кинг' сначала находит в таблице единственную запись для Стивен Кинг, а затем изменяет ее на Ричард Бахман путем изменения соответствующих значений в полях Имя и Фамилия при помощи оператора SET. При этом SQL использует знак равенства (=) как в качестве оператора сравнения, так и в качестве оператора присваивания.
Язык SQL также может удалять записи из базы данных. Вот пример того, как это можно сделать с целью удаления записи Пол Мендес в таблице author:
DELETE FROM author
WHERE Имя = 'Пол'
AND Фамилия = 'Мендес';
В данном коде оператор DELETE с помощью использования в своей структуре оператора WHERE Имя = 'Пол' AND Фамилия = 'Мендес' сначала находить в таблице author единственную запись с такими же значениями, а затем удаляет ее.
Следует иметь в виду, что условия, которые задаются для поиска удаляемых записей должны быть максимально конкретными. Иначе, указание слишком широкого (расплывчатого) условия в операторе DELETE может привести к уничтожению в том числе и тех записей, удалять которые изначально мы совершенно не собирались. Так, например, если бы наше условие из предыдущего SQL кода ограничивалось бы лишь проверкой Имя = 'Павел', то все авторы с именем Павел, которые могли бы быть в нашей таблице author, были бы удалены из базы данных.
Примечание. Для того, чтобы избежать случайного удаления записей, многие приложения, реализованные на базе реляционных СУБД вообще не разрешают удаление. Вместо этого в соответствующие таблицы баз данных добавляется еще одно поле (столбец), указывающее может ли использоваться та или иная запись при выборке данных либо нет. Обычно такой столбец в таблицах называется active и содержит логические значения типа True или False, указывающие на то, следует ли включать запись в запрос на наличие соответствующих данных в этих таблицах.
К примеру, приведенный ниже SQL-запрос должен вывести в консоль содержимое всех столбцов для всех активных записей в таблице some_table:
SELECT * FROM some_table WHERE active = 1;К сожалению, СУБД SQLite не имеет логического типа данных, поэтому поля active в таблицах ее баз данных представляются целым числом со значением 1 или 0, указывающим является ли соответствующая запись активной, либо нет. В отличии от SQLite, большинство других СУБД, как правило, все-таки предусматривают возможность установления для полей своих таблиц логических типов, что не только довольно удобно, но и позволяет незначительно сэкономить место на диске для этих самых таблиц.
В Python реализованы довольно неплохие возможности по созданию баз данных и непосредственному включению в его код SQL операторов. Но, при этом данные из баз данных в Python приложения обычно возвращаются в виде списка списков или списка словарей, что достаточно усложняет дальнейшую работу с получаемой информации.
Поэтому, хотя использование чистого SQL и является вполне приемлемым способом работы Python с базами данных, мы в этой статье сразу же будем использовать классы из SQLAlchemy, поскольку они являются все же более мощным, удобным и гибким средством для работы с базами данных, нежели просто SQL. Но, прежде, давайте все же более подробно рассмотрим отношения между таблицами, как основу для проведения нормализации в реляционных базах данных.
Построение отношений между таблицами в реляционных базах данных
Наряду с удобным механизмом сохранения и извлечения данных, пожалуй, наиболее мощной и полезной особенностью реляционных баз данных является возможность установления отношений между их таблицами. Ведь, именно эта возможность обеспечивает нормализацию баз данных путем разделения информации в них на ряд таблиц с последующей установкой взаимосвязей между ними.
Раннее в этой статье мы уже сталкивались с формированием отношений между таблицами при их создании из данных файла author_book_publisher.csv на примере соответствующего SQL кода. Тогда для того, чтобы избавиться от дублирования данных в этом файле мы разбивали его на три такие, взаимосвязанные между собой, таблицы, как author, book, и publisher.
На первый взгляд, вышеупомянутое разбиение одного CSV файла на несколько таблиц в то время могло бы показаться нам явно нецелесообразным. Зачем же разбивать то, что уже собрано в одном месте? В какой-то степени, данные соображения вполне оправданы, но преимущества разбиения данных и их последующего объединения с помощью SQL все же скоро возобладают и гарантированно покорять нас своим потенциалом самых разнообразных полезностей.
Отношения «один ко многим»
Отношения «один ко многим» между таблицами в базах данных очень схожи на отношения между клиентами и их заказами Internet магазинах. У одного клиента может быть много заказов, но каждый заказ должен принадлежат лишь одному клиенту. В нашем файле базы данных author_book_publisher.db отношениями «один ко многим» характеризуется связь между авторами и написанными ими книгами. Каждый автор может написать много книг, но каждая книга, как правило, пишется лишь одним автором.
Как мы знаем, при нормализации мы, прежде всего, должны избавиться от дублирования данных, помещая в разные таблицы лишь только неповторяющиеся сущности. Также мы должны поступить и в нашем случае с таблицей для авторов и таблицей для их книг. Но, как же тогда избавиться от повторений авторов для их различных книг, как реализовать отношение «один ко многим» между этими двумя вышеназванными таблицами?
Здесь на помощь нам приходит поле с первичном ключом, о котором уже упоминалось раннее в этой статье. Имена таких полей в таблицах обычно формируются на основе использования шаблона <table name>_id. Поля со схожими именами есть и в наших таблицах author для авторов и book для их книг.
Так, аналогично полю author_id, содержащемуся в таблице author и являющемуся первичным ключом, таблица book содержит такое же одноименное поле, которое для данной таблицы является уже не первичным, а так называемым внешним ключом. Таким образом, поле author_id с первичным ключом в таблице author устанавливает отношение «один ко многим» с одноименным полем, представляющим собой внешний ключ в таблице book. Графически такая связь между вышеназванными таблицами выглядит следующим образом:
На вышеприведенном рисунке представлена простая диаграмма ER модели данных сущность – связь, созданная с помощью приложения JetBrains DataGrip. На этой диаграмме в виде своеобразных блоков показана таблица author и таблица book с соответствующими полями, а также первичным и внешним ключами, которые обеспечивают межтабличную взаимосвязь. Также на этой диаграмме представлены:
- Маленькие желтые и синие значки ключей, соответственно обозначающие первичный и внешний ключи, присутствующие в наших таблицах.
- Стрелка, соединяющая таблицу book с таблицей author и, указывающая на связь между ними, которая обеспечивается внешним ключом author_id из таблицы book.
Такая взаимосвязь первичного и внешнего ключей в нашем примере обеспечивает то, что параллельно с добавлением новой книги в таблицу book, в эту запись автоматически включается значение из поля author_id для существующего автора этой книги из таблицы author. Таким образом, все книги, написанные теми или иными авторами, могут быть отфильтрованы (выбраны) в таблице book именно благодаря, имеющемуся в ней внешнему ключу author_id с уникальными кодами для всех тех авторов, книги которых были сохранены в этой таблицы.
Теперь, когда у нас имеются отдельные, но взаимосвязанные между собой таблицы для авторов и их книг, мы благодаря SQL свободно можем объединить их данные с помощью так называемых JOIN операций, позволяющих в рамках заданных условий соединять две или более таблиц в наших базах данных.
Вот, к примеру, так с помощью SQL-запроса могут быть объединены таблицы author и book вместе за счет предварительно запущенной нами командной консоли sqlite3.exe:
sqlite> SELECT
...> a.Имя || ' ' || a.Фамилия AS author_name,
...> b.Название_книги AS book_title
...> FROM author a
...> JOIN book b ON b.author_id = a.author_id
...> ORDER BY a.Фамилия ASC;
Айзек Азимов|Фонд
Перл Бак|Хорошая Земля
Стивен Кинг|Оно
Стивен Кинг|Мертвая зона
Стивен Кинг|Сияние
Том Клэнси|<Охота за красным октябрем>
Том Клэнси|Игры патриотов
Джон Ле Карре|<Тинкер; портной; солдат; шпион: роман Джорджа Смайли>
Алекс Майклидес|Молчаливый пациент
Кэрол Шабен|В бездну
Приведенный выше SQL-запрос выводит на консоль совокупность данных из таблиц author и book, объединяя их по полю author_id с помощью выражения JOIN book b ON b.author_id = a.author_id, основанного на операторе JOIN. При этом, предварительно в таблице author производится конкатенация строк в ее полях Имя и Фамилия с тем, чтобы объединить эти поля в дополнительное виртуальное поле с полными именами авторов author_name. В конце нашего запроса объединенные из вышеназванных таблиц данные еще и сортируются оператором ORDER BY в порядке возрастания по полю Фамилия.
Помимо того, что в выводе по результатам предшествующего запроса присутствуют не две, а одна колонка с полными именами авторов, отсортированными по их фамилии, мы видим, что тут наши данные снова начали дублироваться, также как это было в файле author_book_publisher.csv. Это произошло как раз таки из-за того, что при объединении этих таблиц в операторе JOIN мы применили отношение «один ко многим», что позволило дублировать имена авторов для каждой, написанной ими книги.
Вместе с тем, создав отдельные таблицы для авторов и их книг, а также установив связь между ними, мы не только сократили избыточность этих данных, но и обеспечили возможность автоматического распространения, единожды внесённых нами изменений во все возможные сочетания данных, выводимые при соответствующих SQL запросах.
Отношения «многие ко многим»
Отношение «многие ко многим», наряду с отношением, рассматриваемым в предыдущем разделе нашей статьи, также является весьма распространенным видом взаимосвязей между таблицами реляционных баз данных. К примеру, в файле нашей базы данных author_book_publisher.db такое отношение присутствует между таблицей с авторами и таблицей с издателями, а также между таблицей с книгами и все той же таблицей с издателями. Ведь, один автор может работать со многими издателями, а один издатель может работать со многими авторами. Точно так же одна книга может быть опубликована многими издателями, а один издатель может издать много книг.
Вместе с тем, из-за того, что в отношениях «многие ко многим» предполагается сугубо обоюдное взаимодействие между таблицами, их обработка в базах данных представляет собой гораздо более сложную процедуру, нежели обработка связей «один ко многим». Как правило, в результате обработки таких отношений создаются ассоциативные таблицы, выступающие в роли своеобразного моста между двумя взаимодействующими таблицами.
Обязательной составляющей ассоциативных таблиц должны являться как минимум два поля с внешними ключами, создающиеся на основе полей с первичных ключами во взаимодействующих таблицах, которые как раз таки эти ассоциативные таблицы и связывают. Давайте посмотрим, как может быть создана ассоциативная таблица, связывающая таблицы author и publisher с помощью следующего оператора SQL:
CREATE TABLE author_publisher (
author_id INTEGER REFERENCES author,
publisher_id INTEGER REFERENCES publisher
);
В этом коде стандартный оператор SQL CREATE TABLE создает новую таблицу author_publisher дублируя в нее поля первичных ключей author_id и publisher_id из таблиц author и publisher соответственно. В результате мы получаем ассоциативную таблицу author_publisher содержащую два внешних ключа, за счет которых в последующем можно установить отношения между таблицей с авторами и таблицей с издателями.
Примечание. Изначально, в файле нашей базы данных author_book_publisher.db уже существует ассоциативная таблица author_publisher, поэтому заново нам ее создавать не следует. Если же мы захотим это сделать с помощью вышеприведенного кода, то SQLite нам выдаст сообщение о том, что такая таблица у нас уже существует.
Как мы видим, при наличии двух полей дублирующих первичные ключи связываемых таблиц, в нашей ассоциативной таблице отсутствует свой собственный первичный ключ. Такая вот особенность этой ассоциативной таблицы предопределяется тем, что комбинация существующих в ней полей с ключами из внешних таблиц сама по себе является уникальной и достаточной для однозначной идентификации записей в ней.
Давайте теперь попробуем, применив уже имеющуюся у нас ассоциативную таблицу author_publisher, объединить информацию из таблицы author и таблицы publisher в единое целое. Для такого объединения мы, как и прежде используем ключевое слово JOIN, но при этом, сам данный процесс проводим в два следующих этапа:
- Объединяем таблицу author с ассоциативной таблицей author_publisher.
- Затем объединяем таблицу author_publisher с таблицей publisher.
Таким образом, наша ассоциативная таблица author_publisher обеспечивает своеобразный мост, через который благодаря ключевому слову JOIN объединяются две вышеназванные таблицы author и publisher. Вот как выглядит пример SQL-запроса, возвращающий список авторов и издателей, которые опубликовали их книги:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | |
В рамках вышеприведенного кода выполняются следующие действия:
- Строка 1 запускает SQL оператор SELECT, обеспечивающий вывод информации на консоль из баз данных по заданным критериям.
- Строка 2 выбирает соответствующие поля Имя и Фамилия из таблицы author, которые затем объединяет через пробел и записывает под псевдонимом author_name.
- Строка 3 присваивает полю с названием фирмы издателя в таблице publisher псевдоним publisher_name.
- Строка 4 назначает таблице author псевдоним a и указывает ее в качестве первого источника данных для оператора SELECT.
- Строка 5 является первым этапом описанного выше процесса объединения таблицы author с таблицей publisher. В рамках этого этапа на основе нашей ассоциативной таблицы author_publisher, использующей псевдоним ap выполняется JOIN операция, подключающая ее внешний ключ ap.author_id к первичному ключу a.author_id из таблицы author.
- Строка 6 является вторым этапом объединения двух вышеназванных таблиц, в ходе которого используется таблица publisher под псевдонимом p. Так, в рамках данного этапа за счет выполнения JOIN операции происходит связывание внешнего ключ ap.publisher_id из нашей ассоциативной таблицы с первичным ключом p.publisher_id из вышеупомянутой таблицы publisher.
- Строка 7 сортирует последующий вывод информации из нашей базы данных исходя из алфавитного порядка фамилий присутствующих там авторов.
- Строки с 8 по 17 являются результирующими данными нашего SQL-запроса.
Как видно из кода, приведенного выше SQL-запроса, наши, нормализованные данные, которые в таблицах author и publisher не содержат повторений, по итогам этого запроса все же дублируются там, где это требуется. Это как раз и показывает всю мощь нормализации данных в сочетании с корректными SQL-запросами.
В сущности, наш предыдущий SQL-запрос всего лишь демонстрирует, как с помощью отношений между таблицами совместно с применением ключевого слова JOIN воссоздать через SQL то, что было сохранено в нашем CSV файле author_book_publisher.csv. И, снова, этот факт в очередной раз заставляет нас задуматься - в чем же тогда выигрыш от того, что мы проделали столь обширную работу для объединения того, что и так когда-то уже было сохранено в таком же объединенном состоянии?
Для того, чтобы ответить на этот вопрос и окончательно убедиться во всей мощи SQL для реляционных СУБД, давайте попробуем выполнить еще один SQL-запрос, демонстрирующий те возможности баз данных, о которых мы еще не говорили:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
Приведенный выше SQL-запрос возвращает список авторов и количество написанных ими книг. Этот список сначала отсортирован по количеству книг в порядке убывания, а затем в алфавитном порядке по фамилиям авторов:
- Строка 1 начинает SQL-запрос с запуска оператора SELECT.
- Строка 2 выбирает поля Имя и Фамилия из таблицы author, которые затем, объединяя через пробел, записывает под псевдонимом author_name для последующего использования в операторе GROUP BY.
- Строка 3 подсчитывает с помощью COUNT() количество книг, написанных каждым автором, сохраняя этот результат в дополнительном поле, которое в последующем используется под псевдонимом total_books в операторе ORDER BYдля соответствующей сортировки.
- Строка 4 назначает таблице author псевдоним a и указывает ее в качестве первого источника данных для оператора SELECT.
- Строка 5 объединяет таблицу author с таблицей book, использующей псевдоним b, путем подключения ее внешнего ключа b.author_id к первичному ключу a.author_id из таблицы author.
- Строка 6 формирует агрегированные данные об авторах и общем количестве написанных ими книгах, используя при этом оператор GROUP BY. Данный оператор, в частности, группирует строки для каждого автора в поле под псевдонимом author_name, присоединяя к нему поле под псевдонимом total_books, где с помощью COUNT() подсчитывается количество написанных этими авторами книг.
- Строка 7 сортирует вывод SQL-запроса сначала по количеству книг в порядке убывания, а затем по фамилиям авторов в порядке возрастания согласно алфавиту.
- Строки с 8 по 14 являются результирующими данными вышеприведенного SQL-запроса.
Приведенный выше SQL код является наглядным примером выполнения статистических вычислений и сортировки результатов в удобном для нас порядке. Все подобные вычисления и сортировки в большинстве реляционных СУБД, как правило, выполняются быстрее, нежели это происходит с необработанными наборами данных в Python. Это объясняется рядом встроенных в реляционные СУБД возможностей по организации и вычислению данных. Так, например, выполнение любых SQL-запросов, как правило, почти всегда основывается на использовании, встроенных в большинство реляционных СУБД, преимуществ теории множеств.
Диаграммы сущностей и их связей в базах данных
Диаграмма сущность – связь (ERD) представляет собой визуальное изображение модели данных сущность – связь, относящееся либо ко всей базе данных, либо лишь к ее части. Так как таблицы нашей базы данных SQLite, размещаемой в файле author_book_publisher.db, достаточно немногочисленны, то мы попытаемся представить всю эту базу в одной диаграмме ниже:
Как мы видим, на этой диаграмме представлено пять блоков, каждый из которых является таблицей со свойственным ей перечнем полей. Эти блоки соединяются между собой стрелочками, символизирующими соответствующие отношения между таблицами в нашей базе данных. На диаграмме некоторые, самые первые по списку поля в блоках (таблицах) помечены значком с желтым ключом, указывающим на то, что эти поля являются для соответствующих таблиц, их первичными ключами. Кроме этого, в таблицах можно увидеть значки с синими ключами, обозначающими то, что эти поля в вышеупомянутых таблицах являются внешними ключами.
Стрелки, показывающие в нашей диаграмме отношения между таблицами, как правило, соединяют поле внешнего ключа в одной таблице с полем первичным ключом, в другой таблице. Одной из особенностей, которая в вышеприведенной диаграмме отображается для нашей базы данных, является наличие у таблиц book_publisher и author_publisher сразу двух стрелок. Это объясняется тем, что именно эти две таблицы у нас базе данных являются ассоциативными. Так, например, соединение book_publisher с таблицей book и таблицей publisher показывает нам, что между последними двумя таблицами происходит взаимосвязь, основанная на отношении «многие ко многим».
Работа с объектами SQLAlchemy в Python
Во всей богатой палитре имеющихся инструментариев Python в сфере доступа к данным, несомненное лидерство, как правило, всегда было и остается за SQLAlchemy. Данная программная библиотека Python представляет собой мощный набор инструментов для доступа к базам данных за счет использования так называемого объектно-реляционного преобразователя (ORM), обеспечивающего нам всю мощь и гибкость работы в SQL. На этом же ORM мы в основном и остановимся далее в этой статье.
Работая с объектно-ориентированными языками, подобными Python, мы зачастую тщетно пытаемся корректно отобразить наши объекты в таблицах баз данных или же наоборот, преобразовать SQL-запросы в те или иные объекты. Но, обычно, все эти наши попытки оказываются тщетными, поскольку сама суть работы баз данных, необходимость обмениваться с ними лишь только сугубо скалярной информацией идет вразрез с принципами объектно-ориентированного программирования. Несопоставимость объектов в объектно-ориентированных языках с таблицами в реляционных СУБД является достаточно распространенной проблемой известной, как объектно-реляционное рассогласование импедансов.
Как раз для того, чтобы устранить вышеописанную проблему и предназначен, предоставляемый SQLAlchemy, ORM, который находясь между базой данных SQLite и нашей программой Python, фактически призван надлежащим образом преобразовывать потоки данных между ядром базы данных и объектами Python. Таким образом, SQLAlchemy позволяет нам оперируя категориями объектов, параллельно сохранять мощную функциональность реляционных баз данных.
Создание моделей для SQLAlchemy
Одним из основополагающих условий для объединения возможностей SQLAlchemy с функциональностью нашей базы данных является предварительное создание для этой базы данных так называемых моделей. Модель представляет собой класс Python, объекты которого равнозначны таблицам в базах данных с колонками, эквивалентными свойствам этих объектов. Фактически, модели обеспечивают своеобразную конвертацию результатов SQL-запросов от баз данных в классические объекты Python.
Для более глубокого понимания сущности применения моделей, давайте вернемся к приведенной раннее в этой статье диаграмме с пятью блоками, объединяемыми стрелочками. Блоки в этой диаграмме представляют собой созданные с помощью SQL команд таблицы, которые нам в последствии потребуется преобразовать в классы Python с учетом отношения между таблицами, отображаемых в диаграмме посредством соответствующих стрелочек.
Такое преобразование таблиц в классы, как раз и реализуется на основе моделей, которые как уже указывалось раннее, представляют собой классы Python, унаследованные от класса Base в SQLAlchemy. Данный класс Base, в свою очередь призван обеспечивать операции интерфейса между экземплярами моделей и таблицами в базах данных.
Давайте на примере листинга из …\project\modules\models.py посмотрим, как должны выглядеть модели SQLAlchemy для представления нашей SQLite базы данных из файла author_book_publisher.db:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | |
Вот те действия, которые происходят в вышеприведенном листинге модуля models.py:
- Строка 1 импортирует классы Column, Integer, String, ForeignKey и Table из SQLAlchemy с целью их дальнейшего использования при определении соответствующих атрибутов наших моделей.
- Строка 2 импортирует объекты relationship() и backref для последующего использования при создании отношений между объектами, ассоциирующимися с таблицами.
- Строка 3 импортирует объект declarative_base, с целью последующего обеспечения взаимодействия базы данных с моделями SQLAlchemy.
- Строка 5 создает класс Base, от которого в последующем наследуются все наши будущие модели, обладающие способностью взаимодействовать с таблицами баз данных на основе применения SQLAlchemy ORM.
- Строки с 7 по 12 создают модель author_publisher равнозначную одноименной таблице ассоциаций в базе данных.
- Строки с 14 по 19 создают модель book_publisher равнозначную одноименной таблице ассоциаций в базе данных.
- Строки с 21 по 29 определяют класс Author с моделью для одноименной таблицы author, размещаемой в базе данных.
- Строки с 31 по 38 определяют класс Book с моделью для одноименной таблицы book в базе данных.
- Строки с 40 по 49 определяют класс Publisher с моделью для одноименной таблицы publisher в базе данных.
На данный момент мы получили всего лишь общее преставление о том, как на Python можно сопоставлять объекты в виде соответствующих моделей с реальными таблицами в базах данных. Поэтому, основную магию работы SQLAlchemy ORM, опирающуюся на использование Table, ForeignKey, relationship() и backref, нам еще только предстоит изучить.
Создание объектов равнозначных таблицам ассоциаций с помощью Table
Из приведенного раннее листинга для модуля models.py мы с вами заметили, что author_publisher и book_publisher являются экземплярами класса Table, которые равнозначны одноименным ассоциативным таблицам в нашей базе данных. Следовательно, эти экземпляры в нашей программе призваны будут обеспечивать связь «многие ко многим» между author – publisher и между book – publisher таблицами соответственно.
В SQLAlchemy класс Table с помощью ORM создает уникальный экземпляр объекта, который по своей функциональности равнозначен ассоциативными таблицам в базах данных. С целью создания своего экземпляра, класс Table в качестве параметров обычно последовательно принимает ряд следующих аргументов:
- Имя соответствующей ассоциативной таблицы из базы данных.
- Системный аргумент Base.metadata, обеспечивающий многофункциональную связь непосредственно между создаваемым SQLAlchemy объектом и ядром базы данных.
- Соответствующее количество экземпляров класса Column, которые могут приниматься классом Table в виде остальных его аргументов. В свою очередь, аргументами каждого экземпляра класса Column должны быть: «имя соответствующего поля таблицы», «тип этого поля» и, возможно, как в нашем случае, экземпляр класса ForeignKey, содержащий имя первичного ключа в связываемой таблице.
Создание связей между объектами при помощи ForeignKey
Предусматриваемый SQLAlchemy класс ForeignKey устанавливает зависимость между двумя экземплярами класса Column, ассоциирующимися с полями для двух разных таблиц в используемой базе данных. Таким образом, экземпляр класса ForeignKey указывает экземпляру класса Column то, с каким полем из внешний таблицы он должен связываться. К примеру, нижеследующий код, являясь частью создания экземпляра author_publisher, показывает, как в программе на Python можно смоделировать связь между таблицами author_publisher и author в базе данных:
Column("author_id", Integer, ForeignKey("author.author_id"))
Данный код фактически подключает создаваемый нами экземпляр author_publisher к одноименной таблице в базе данных по полю author_id. В частности, этот код сообщает, что поле author_id имеет тип Integer и является внешним ключом ForeignKey("author.author_id"), связывающим таблицу author по ее первичному ключу author_id.
При создании экземпляра author_publisher от класса Table, кроме подключения к полю author_id также происходит подключение и по полю publisher_id, что фактически эквивалентно обеспечению связи от таблицы author к таблице publisher и наоборот на основе отношений «многие ко многим».
Установка доступа к коллекциям с помощью relationship()
Благодаря классу ForeignKey мы можем установить связь между реальными таблицами в базе данных, но не можем, к примеру, установить перечень книг, написанных каждым автором, то есть не можем из одной таблицы получить доступ к связанным данным в другой таблице. Для решения этой проблемы в SQLAlchemy существует функция relationship(), пример использования которой приведен ниже:
books = relationship("Book", backref=backref("author"))
Вышеприведенный код из нашего модуля models.py в процессе определения класса Author добавляет к нему атрибут books, обеспечивающий доступ для авторов из таблицы author к написанным ими книгам в таблице book. Иными словами, атрибут books из класса Author обеспечивает доступ авторов к соответствующим индивидуальным коллекциям их книг в классе Book. По общепринятым соглашениям все имена атрибутов моделей таблиц (потомков класса Base), ссылающиеся на коллекции, обычно указываются во множественном числе.
Первым аргументом функции relationship() всегда выступает имя внешнего класса, откуда следует получать коллекции. Для вышеприведенного кода первым аргументом в relationship() выступает класс Book, который в свою очередь связан с таблицей book в базе данных. На самом деле, указание в relationship() лишь имени вышеназванного внешнего класса уже достаточно для того, чтобы SQLAlchemy в определении класса Book сам смог бы найти нужную строку, определяющую связь между таблицами author и book:
author_id = Column(Integer, ForeignKey("author.author_id"))
Найдя вышеприведенную строку кода, SQLAlchemy распознает, что класс Book и класс Author могут быть объединены через соответствующий внешний ключ ForeignKey("author.author_id"). При определении атрибута books в функции relationship() также есть и второй ее аргумент backreef. Но, о нем более подробно мы поговорим уже в следующем подразделе нашей статьи.
Кроме books, в классе Author есть определение еще одного подобного ему атрибута publishers, позволяющего ссылаться на находящиеся в классе Publisher коллекции (списки) издателей книг для каждого автора:
publishers = relationship(
"Publisher", secondary=author_publisher, back_populates="authors"
)
При определении вышеназванного атрибута, первый параметр функции relationship() представляет собой имя внешнего класса Publisher, из которого необходимо получать коллекции (списки) издателей. Описание же по второму же и третьему аргументам: secondary = author_publisher и back_populates = "authors" соответственно, приведено ниже:
- secondary предназначен для указания SQLAlchemy того, что связь класса Author с классом Publisher происходит через раннее созданный на базе класса Table экземпляр author_publisher, являющейся аналогом одноименной таблицы ассоциаций в базе данных. Фактически, атрибут secondary заставляет SQLAlchemy находить в экземпляре author_publisher внешний ключ publisher_id, благодаря которому соответствующая таблица ассоциаций в базе данных осуществляет связь между таблицей author и таблицей publisher.
- back_populates предназначен для информирования SQLAlchemy о наличии в связываемом классе Publisher атрибута коллекций под названием authors, который, являясь противоположным создаваемому атрибуту publishers, нуждается в синхронизации с ним.
Создание зеркального атрибута в связываемом классе с помощью backref
Как мы помним, в качестве второго аргумента при создании функцией relationship() атрибута для коллекции books в models.py использовался параметр backref. Этот параметр обладает достаточно магическими свойствами в том плане, что расширяет функциональность не того класса, в котором он объявляется, а того, с которым данный класс связывается. Так, указание backref=backref("author") при определении нашего атрибута books наделяет каждый экземпляр связываемого в этом определения класса Book зеркальным атрибутом, ссылающимся на родительский класс Author.
Например, после вышеуказанной инициализации backref=backref("author") и последующего SQLAlchemy запроса мы сможем получить экземпляр класса Book, атрибуты которого позволят нам вывести всю информацию об авторе интересующей нас книге:
book = session.query(Book).filter_by(Book.Название_книги ==
"Противостояние").one_or_none()
print(f"Имя и фамилия автора: {book.author.Имя} {book.author.Фамилия}")
Существование в этом коде атрибута author для экземпляра класса Book обусловлено именно тем, что раннее при определении класса (модели) Author нами был использован параметр backref. Применение данного параметра иногда может стать просто находкой, особенно в тех случаях, когда в программах нам нужно ссылаться на родительский класс, а все, что у нас есть, это дочерние его экземпляры.
Формирование запросов с помощью SQLAlchemy
Стандартный SQL-запрос типа SELECT * FROM author в SQLAlchemy можно реализовать следующим образом:
results = session.query(Author).all()
Из этого кода видно, что взаимосвязь между таблицами баз данных и программами на Python может осуществляться через специфический объект SQLAlchemy session, благодаря которому в нашем примере объявляется новый сеанс, выполняющий запрос к модели Author и возвращающий из таблицы author все имеющиеся там записи.
Данный код вполне понятен и читабелен, но он отнюдь не убеждает нас в преимуществах использования SQLAlchemy вместо простого SQL, особенно если учесть, что для такого использования нам пришлось предварительно затратить массу времени и усилий на создание моделей для соответствующих таблиц нашей базы данных. Однако, все встает на свои места, если мы поподробнее вникнем в результат выполнения нашего запроса, сохраняемого в переменной results. Здесь мы увидим, что вместо возвращаемого SQL списка списков скалярных данных, в нашей переменной results содержится список экземпляров Author – объектов аналогичных содержащимся в одноименной таблице полям, но с массой очень полезных атрибутов.
Так, в частности, благодаря содержащимся в results возможностям коллекций books и publishers при поддержке SQLAlchemy можно создать несколько иерархических списков авторов, как в разрезе написанных ими книг, так и в разрезе работающих с ними издателей.
Под капотом, работая с Python кодом, SQLAlchemy с одной стороны превращает вызовы объектов и методов в операторы SQL, которые затем направляет на выполнение в ядро нашей базы данных SQLite, а с другой стороны – преобразует данные, возвращаемые SQL-запросами, из этой базы данных в Python объекты.
Давайте посмотрим, как с помощью SQLAlchemy мы можем выполнить, приводимый раннее, агрегированный SQL-запрос, который будет формировать список авторов с указанием количества написанных ими книг:
author_book_totals = (
session.query(
Author.Имя,
Author.Фамилия,
func.count(Book.Название_книги).label("book_total")
)
.join(Book)
.group_by(Author.Фамилия)
.order_by(desc("book_total"))
.all()
)
Результатом вышеприведённого запроса является перечень из имеющихся у нас в базе данных авторов, каждая строка которого содержит такие персональные сведения о них, как: Имя, Фамилия и общее количество написанных каждым автором книг. Формирование агрегирующего счетчика (количества написанных книг) в этом запросе производится в рамках предложения group_by, использующего для объединения записей фамилии авторов. Полученные от запроса результаты в конечном итоге сортируются в порядке убывания агрегированных количеств книг, размещенных в виртуальной колонке под псевдонимом book_total.
Пример программы по взаимодействию с SQLite за счет SQLAlchemy
Описываемая в данном подразделе статьи программа …\project\examples\example_2\main.py абсолютно аналогична по функциональности с уже рассматриваемой программой …\project\examples\example_1\main.py. Но эта программа основана уже не на плоских файлах и применении модуля Pandas, а исключительно на использовании SQLAlchemy во взаимодействии с базой данных SQLite, размещаемой в файле author_book_publisher.db. Как и первый пример, данная программа в файле разбита на main() функцию и вызываемые ею дополнительные функции:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | |
Как уже было сказано, данная программа представляет собой модифицированную версию …\project\examples\example_1\main.py. Поэтому, при дальнейшем описании акцент нами будет сделан лишь на ее отличиях. В частности, ниже приведены отличия, которые в этой программе присущи для функции main():
- Строки с 4 по 7 сначала инициализируют переменную sqlite_filepath и записывают в нее путь к соответствующему файлу базы данных. Затем же, в этих строках создается еще одна переменная engine, которая, ассоциируя вышеупомянутый файл author_book_publisher.db с СУБД SQLite, является непосредственной точкой доступа SQLAlchemy к вышеназванной базе данных.
- Строка 8 создает класс Session используя, предусматриваемый в SQLAlchemy соответствующий инициализатор sessionmaker().
- Строка 9 конфигурирует созданный в строке 8 класс Session необходимыми параметрами из предварительно созданной точки доступа SQLAlchemy (переменной engine).
- Строка 10 создает на основе класса Session его экземпляр session, который в последующем используется программой для связи с SQLAlchemy.
В остальном же функция main() отличается от своего начального аналога лишь тем, что в качестве первого аргумента во всех вызываемых ею функциях указывается не data, а session.
Теперь давайте разберемся с отличиями в прикладных (пользовательских) функциях, вызываемых из main().
В частности, функция get_books_by_publisher() в данном примере была модифицирована под использование в ней возможностей SQLAlchemy и, определенных нами раннее моделей таблиц с необходимыми данными:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | |
Вот что происходит в коде для этой модифицированной функции get_books_by_publishers():
- Строка 6 создает переменную direction, значение которой в зависимости от аргумента ascending становится идентичным вызову таких функций SQLAlchemy*, как desc или asc**.
- Строки с 9 по 11 возвращают из класса (таблицы) Publisher данные по наименованиям фирм издателей из Publisher.Издатель, связанные с общим количеством опубликованных ими книг, которое определяется через func.count в классе (таблице) Book по свойству Book.Название_книги и записывается в виртуальное поле под псевдонимом total_books.
- Строка 12 присоединяет класс Publisher* непосредственно к его books** коллекции.
- Строка 13 суммирует количество книг исходя из атрибута Publisher.Издатель в классе Publisher*.
- Строка 14 сортирует результирующие данные в порядке, заданном соответствующими функциями при инициализации переменной direction в строке 6. Сортировка происходит по количеству книг, опубликованных каждым издателем, хранящемуся в виртуальном поле под псевдонимом total_books.
- Строка 15 закрывает объект, выполняющий запрос session.query() и возвращает результаты в функцию main().
Благодаря применению SQLAlchemy, приведенный выше код, точно также как и в языке SQL, выражает то, что требуется сделать, а не то, как это должно быть получено. Единственно, теперь для описания того, что требуется сделать, мы используем не операторы SQL, а объекты и методы Python. При этом, в результате выполнения запроса благодаря SQLAlchemy, нам возвращается не список кортежей данных, а перечень объектов Python с массой атрибутов с огромным потенциалом возможностей.
Аналогично предыдущей функции, get_authors_by_publisher() также была модифицирована исключительно с целью использования в ней возможностей SQLAlchemy*. Функционально, эта функция очень похожа на уже рассмотренную нами предыдущую функцию, поэтому построчное описание действий в ней опущено:
def get_authors_by_publishers(session, ascending=True):
"""Возвращение списка издателей с количества авторов,
чьи книги ими опубликованы"""
if not isinstance(ascending, bool):
raise ValueError(f"Недопустимое значение сортировки: {ascending}")
direction = asc if ascending else desc
return (
session.query(
Publisher.Издатель,
func.count(Author.Имя).label("total_authors"),
)
.join(Publisher.authors)
.group_by(Publisher.Издатель)
.order_by(direction("total_authors"))
)
В рассматриваемую программу была добавлена функция get_authors(), позволяющая получить сведения об авторах, отсортированные по их фамилиям. Результатом определяемого этой функцией запроса является список объектов Author, одним из атрибутов которых обеспечивается доступ к коллекциям книг в разрезе по написавшим их авторам. Кроме этого, получаемые от функции объекты Author уже содержат иерархические данные и, поэтому не нуждаются в переформатировании:
def get_authors(session):
"""Возвращение сведений об авторах, отсортированных по их фамилиям"""
return session.query(Author).order_by(Author.Фамилия).all()
Наиболее основательным изменениям в нашем примере подверглась функция add_new_book(). Вместе с тем, как и первая ее версия, данный вариант функции относительно сложен лишь на первый взгляд, но при более подробном рассмотрении, ее код для понимания довольно прост. Первым делом эта функция в нашем примере сверяет сведения по добавляемой книге с уже имеющейся в базе данных информацией на предмет выявления совпадений по названию книги, по фамилии и имени ее автора, а также по названию фирмы издателя, опубликовавшего эту книгу.
Если вышеназванные поисковые запросы увенчиваются успехом, то наша функция завершается, не производя при этом никаких действий по изменению содержимого базы данных. Если же в базе данных еще нет добавляемой книги, то создается новый экземпляр класса Book с присоединением к нему добавляемой книги. Затем выполняется поиск по автору, добавляемому вместе с книгой. Если данного автора еще нет в базе данных, то создается новый экземпляр класса Author с присоединением к нему фамилии и имени добавляемого автора. И, наконец, выполняется поиск по фирме издателя, добавляемой вместе с новой книгой. Если данного издателя еще нет в базе данных, то создается новый экземпляр класса Publisher с присоединением к нему добавляемого издателя.
Все вышеописанные действия являются совершенно необходимыми, поскольку предотвращают возникновение в нашей базе каких-либо дубликатов.
После соответствующих изменений экземпляров классов Book, Author и / или Publisher, данная функция завершается обновлением взаимосвязей между таблицами и окончательной фиксацией всех изменений в базе данных.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | |
Так как приведенный выше код относительно длинен, дальнейшее его описание проведем исходя из предварительного объединения его строк на отдельные функциональные группы:
- В строках с 7 по 18 при нахождении в базе данных книги с таким же названием, автором и издателем, что и добавляемая книга, переменной book присваивается экземпляр класса Book. В противном случае, переменной book присваивается значение None.
- Строки 20 и 21 определяют тожественность переменной book значению None и, если они не тождественны, завершают функцию.
- В строках с 24 по 37 при нахождении в базе данных книги с таким же названием и автором переменной book присваивается экземпляр класса Book В противном случае переменной book присваивается обновленный экземпляр класса Book с учетом названия добавляемой книги.
- Строки с 40 по 52 при нахождении в базе данных автора с таким же именем и фамилией, что и в добавляемых сведениях, переменной author присваивается экземпляр класса Author. В противном случае переменной author присваивается обновленный экземпляр класса Author с учетом имени и фамилии добавляемого автора.
- Строки с 55 по 63 при нахождении в базе данных издателя с таким же названием, что и в добавляемых сведениях, переменной publisher присваивается экземпляр класса Publisher. В противном случае переменной publisher присваивается обновленный экземпляр класса Publisher с учетом названия фирмы добавляемого издателя.
- Строка 66 приравнивает экземпляр author к экземпляру book.author, создавая при этом между автором и написанной им книгой соответствующую связь, которая после завершения сеанса фиксируется с помощью SQLAlchemyв базе данных.
- Строка 67 добавляет экземпляр publisher в коллекцию book.publishers, создавая при этом отношение «многие ко многим» между таблицами book и publisher. В результате выполнения этой строки «под капотом» с помощью SQLAlchemy создаются соответствующие ссылки, как в вышеназванных таблицах, так и в объединяющей их ассоциативной таблице book_publisher.
- Строка 68 добавляет к текущему сеансу экземпляр класса Book, распространяя, таким образом, на данный экземпляр свои полномочия.
- Строка 71 фиксирует все изменения и обновления в базе данных.
В приведенном выше коде нашего примера есть некоторые особенности, на которые следует обратить отдельное внимание. Во-первых, нигде при запросах или при обновлениях связей между таблицами (классами) у нас не упоминались ассоциативные таблицы author_publisher и book_publisher. Объясняется это тем, что, указав вышеназванные таблицы при создании своих моделей, мы тем самим дали SQLAlchemy возможность обращаться, модифицировать и синхронизировать их косвенно («под капотом»), параллельно с совершением запросов или обновлением таблиц, содержащих реальные данные о книгах, авторах и издателях.
Во-вторых, все изменения, происходящие с классами и их экземплярами, ассоциирующимися с соответствующими таблицами, фиксируются и отражаются в базе данных на реальных таблицах и их взаимосвязях, лишь после выполнения оператора session.commit(), выполняющего массу «подкапотной» работы, о которой пойдет речь далее.
К примеру, в силу вышеназванного обстоятельства, при создании нового экземпляра класса Book (как в строке 37 для функции add_new_book) объективно не смогут быть проинициализированы такие его атрибуты, как первичный ключ book_id и внешний ключ author_id. Это объясняется тем, что создание вышеназванного экземпляра без оператора session.commit() не позволяет окончательно завершить процесс, добавив в таблицу book новую запись с данными этого экземпляра. Следовательно, значение первичного ключа, автоматически рассчитываемое ядром базы данных, просто не может быть определено без этой новой записи.
Таким образом окончательное определение вышеназванного нового экземпляра класса Book может произойти только после выполнения оператора session.commit(), благодаря которому, в частности, к таблице book будет добавлена новая запись с автоматической инициализацией в ней первичного ключа. И, только после этого, по результатам добавления к нашему сеансу обновленного экземпляра класса Book в строке 68, атрибуту этого экземпляра book.book_id автоматически будет присвоено значение первичного ключа из только что добавленной записи в таблице book.
Благодаря выполнению оператора session.commit() также будет обеспечено добавление первичного ключа author_id из экземпляра класса Author к экземпляру класса Book посредством использования атрибута с одноименной коллекцией author.books. При этом, для экземпляра класса Book вышеназванный author_id ключ, как и следовало ожидать, будет восприниматься уже в качестве внешнего ключа.
Необходимость и специфика общедоступных баз данных
К настоящему моменту, мы уже рассмотрели возможности использования pandas, SQLite и SQLAlchemy для доступа к одним и тем же данным об авторах, их книгах и издателях различными способами. Однако, мы еще не затронули вопросы массового распространения, актуализации и модификации этих или иных данных посредством онлайн доступа к ним со стороны соответствующих пользователей. А ведь, общедоступность баз данных, а также возможность их актуализации и модификации посредством удаленного доступа со стороны сразу нескольких клиентов, является чуть ли не базовым требованием для функционирования всех современных баз данных.
Безусловно, в результате довольно подробного обзора преимуществ применения баз данных в сравнении с плоскими файлами, мы свами четко знаем, что чем более объемны, разнообразны по структуре и сложны по количеству взаимосвязей, обрабатываемые нами данные, тем более очевидной становиться необходимость их размещения в базах данных. Вместе с тем, действительно массовое использование баз данных в локальных или в глобальных сетях, даже с учетом применения в них базовых средств по блокировке распределенных файлов, отнюдь не гарантирует того, что содержащаяся в этих базах данных информация не будет повреждена.
Практическое решение вышеназванной проблемы, как правило, реализуется путем предоставления доступа к базам данных через специальные сервера, использующие соответствующий пользовательский интерфейс. Сервер, в данном контексте рассматривается, как единственное приложение, которому разрешен доступ к базе данных на уровне файлов. При этом, получая такой доступ, сервер через согласованный интерфейс обычно взаимодействует с базой данных посредством использования SQL.
Вышеупомянутое взаимодействие сервера с базой данных продемонстрировано в последнем примере нашей статьи, где представлено web-приложение с довольно простым пользовательским интерфейсом, взаимодействующим с базой данных SQLite, размещенной в файле …\project\data\chinook.db. Эта база данных является усеченной копией существенно более крупной базы данных Spotify, предоставляющей информацию об исполнителях, их музыкальных произведениях и плейлистах.
Использование Flask с Python, SQLite и SQLAlchemy
В программе из последнего примера нашей статьи …\project\examples\example_3\chinook_server.py создается Flask приложение, взаимодействие с которым со стороны пользователей осуществляется с помощью обычных, имеющихся у них браузеров. Данное приложение предусматривает использование следующих внешних модулей Python:
- Flask Blueprint представляет собой часть Flask, используемую для делегирования задач отдельным модулям с заранее определенной функциональностью.
- Flask SQLAlchemy является расширением Flask, обеспечивающим поддержку SQLAlchemy при создании его web-приложений.
- Flask_Bootstrap4 обеспечивает упаковку набора интерфейсных инструментов Bootstrap, интегрируя его с Flask web-приложениями.
- Flask_WTF обеспечивает расширение Flask за счет добавления библиотеки WTForms, предоставляя его web-приложениям удобную возможность по созданию web-форм с последующей проверкой вводимых в них значений.
- python_dotenv представляет собой модуль Python, используемый приложениями для чтения переменных среды из файла и сохранения конфиденциальной информации за пределами программного кода.
Следует отметить, что хотя это и не обязательно, но в нашем примере предусмотрена загрузка файла .env, содержащего переменные среды для приложения. Обычно, в этом файле содержится конфиденциальная информация типа паролей, а также прочие конфигурационные данные, которые мы должны хранить отдельно от своих файлов кода. Как пример, ниже приведено содержимое такого .env файла, который мы можем предварительно сформировать для корректного выполнения рассматриваемого здесь приложения:
SECRET_KEY = "you-will-never-guess"
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLAlCHEMY_ECHO = False
DEBUG = True
Код рассматриваемого приложения в примере достаточно объемен и распределен на множество файлов, размещаемых в тех или иных подкаталогах данного примера. К тому же непосредственно к тематике этой статьи относится лишь малая часть кода приложения. В связи с этим, подробное рассмотрение вышеупомянутого кода оставлено за рамками данной статьи для самостоятельного изучения читателями. Тем не менее, ниже мы можем взглянуть на скринкаст, за которым следует HTML-код шаблона домашней странички artists.html для нашего приложения, а также Python-скрипт, обеспечивающий динамическое представление данных для отображения все той же странички artists.html:
В частности, из приведенного ниже скринкаста мы можем увидеть реальную работу нашего приложения с навигацией по различным меню и функциям:
Воспроизведение скринкаста начинается с домашней страницы приложения, оформленной с использованием Bootstrap 4. На этой странице отображаются, имеющиеся в базе данных исполнители музыкальных произведений с именами и фамилиями, отсортированными по возрастанию. Остальная же часть скринкаста показывает, как реагирует приложение на открытие тех или иных ссылок или же на нажатие определенных пунктов его верхнего уровня меню.
Ниже приведен HTML-шаблон Jinja2, который генерирует домашнюю страничку artists.html для нашего приложения:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | |
Вот что происходит в вышеприведенном коде шаблона Jinja2:
- Строка 1 обеспечивает построение нашей домашней html-странички шаблона на основе использования базового шаблона Jinja2 из файла base.html, который, в свою очередь, содержит весь необходимый для нас шаблонный код HTML5 включая единую для всех страниц сайта панель навигации Bootstrap.
- Строки с 3 по 37 содержат структуру блоков для нашей домашней страничке, также включенную в одноименный макрос Jinja2 из базового шаблона в файле *base.html.
- Строки с 9 по 13 отображают форму для добавления данных по новым исполнителям музыкальных произведений. В этих строках используются возможности *Flask-WTF, обеспечивающие создание форм и проверку вводимых в них данных.
- Строки с 24 по 32 отображают таблицу с именами исполнителей музыкальных произведений благодаря использованию соответствующего for цикла.
- Строки с 27 по 29 отображают имя исполнителя как ссылку на страницу с его музыкальным альбомом, где собраны все песни, связанные с этим конкретным исполнителем.
Ниже из файла routes.py приведен Python-скрипт, обеспечивающий динамическое представление данных для отображения странички artists.html из нашего приложения:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | |
Давайте рассмотрим, что делает приведенный выше код:
- Строки с 1 по 10 импортируют все необходимые нам модули, как для отображения домашней странички приложения, так и для инициализации форм значениями из базы данных. - **Строки с 12 по 15 создают схему вывода домашней странички приложения с исполнителями музыкальных произведений.
- Строки с 17 по 24 создают пользовательскую функцию с целью проверки вводимых в форму Flask-WTF данных по новому исполнителю на предмет того, что они не конфликтуют с данными уже существующих артистов, зарегистрированных в нашей базе данных.
- Строки с 26 по 29 создают специальный класс, обеспечивающий обработку и проверку входных данных из полей web-формы, отображаемой в браузере для сбора сведений по исполнителю с целью дальнейшего его добавления в базу данных.
- Строки с 31 по 32 определяют два равнозначных маршрута для доступа к страничке artists.html, объединяя их затем с функцией artists() посредством ее соответствующего декорирования.
- Строка 30 создает экземпляр класса CreateArtistForm().
- Строка 37 определяет с помощью какого из двух HTTP-методов (GET или POST), запрашивалась наша страничка artists.html. Если оказывается, что данная страничка запрашивалась с помощью метода POST, то далее происходит проверка полей соответствующей формы, с последующим информированием пользователей в случае обнаружения каких-либо допущенных ими ошибок.
- Строки с 39 по 41 создают новый объект с учетом сведений о вновь зарегистрированном исполнители, а затем добавляют его в сеанс SQLAlchemy с последующей фиксацией данного объекта в базе данных путем его сохранения.
- Строка 42 завершает функцию artists() после добавления вновь зарегистрированного исполнителя, перерисовывая при этом страничку artists.html с учетом данных об уже добавленном артисте.
- Строка 44 выполняет SQLAlchemy запрос для получения из базы данных сведений по всем исполнителям, отсортированным по их именам и фамилиям исходя из значения Artist.name.
- Строка 45 отображает страничку artists.html при условии, если она запрашивалась пользователем с помощью такого метода HTTP-запроса, как GET.
Как видно из вышеприведенного описания, реализованная здесь достаточна обширная функциональность была достигнута посредством использования достаточно небольшого объема кода.
Применение REST API-серверов при работе с базами данных
Раннее мы уже говорили о том, что применение серверов является наиболее приемлемым решением для организации взаимодействия между приложениями и базами данных в локальных и глобальных сетях. Такое утверждение, в случае с применением обычных серверов, действительно справедливо, но только в ситуации, когда из своего приложения мы хотим получить доступ к относительно простой базе данных с полностью известной нам структурой таблиц и сетью взаимосвязей между ними. Но, представьте себе ситуацию, когда вам нужно достучаться до базы данных, о перечне и структуре таблиц которой вы не имеете абсолютно никакого представления.
К сожалению, в условиях все усиливающегося распространения концепции открытых данных вышеописанная ситуация становится все более обыденной. Ведь ныне популярность различных порталов с открытыми (общедоступными) данными, под которые пишется масса самых разнообразных приложений, становится чуть ли не базовой тенденцией развития информационных технологий на общемировом уровне. Столь мощная популярность открытых данных, прежде всего, обусловлена ничем иным, как существованием способа получать доступ к ним из баз данных с закрытой (неизвестной) структурой через API благодаря использованию соответствующих REST API-серверов.
API (интерфейс прикладного программирования) представляет собой набор готовых функций, использующийся в виде сервиса, применяемого с целью взаимодействия с внешними приложениями (web-ресурсами) для обеспечения динамического доступа к базам данных. Именно благодаря использованию API мы можем получить универсальный доступ к самым разнообразным базам данным с сотнями различных по структуре, перечню и взаимосвязям таблиц.
Конечно же, не следует забывать, что API для различных REST API-серверов и систем управления данными могут отличаться друг от друга. Но, как правило, описание специфики API для того или иного web-ресурса, обычно приводится для него в обязательном порядке. Кроме того, доступ к открытым данным для преобладающего числа имеющихся в Internet порталов обычно априори уже стандартизован по API, которое присуще такой, наиболее распространенной в мире системе управления данными, как CKAN.
Результатом запроса через API являются данные (как правило, в формате JSON), которые либо поставляются из базы данных, либо отправляются в нее. В большинстве случаев с REST API-серверами через API в виде HTTP-запросов AJAX взаимодействуют одностраничные web-приложения, использующие JavaScript.
В то же время Flask также является отличными инструмент для создания, основанных на применении API, web-приложений. Более подробную информацию об использовании Flask для создания самых разнообразных web-приложений, вы сможете найти в полном руководстве по данному фреймворку.
Вполне вероятно, что свое web-приложение, взаимодействующее с тем или иным REST API-сервером, вы захотите сделать на Django. В этом случае, за более подробной информацией по этому вопросу вы можете обратиться к соответствующей документация Django
Примечание. При подключении базы данных к вашему web-приложению, работающему через обычный сервер, у вас наверняка может возникнут вопрос, какое же СУБД для этой цели лучше всего подходит, достаточным ли будет для данного подключения обойтись таким простым СУБД, как SQLite. На официальном web-сайте этой СУБД утверждается, что SQLite достойно справляется с возложенными на нее функциями при взаимодействии с сайтами, имеющими около 100 000 посещений в день. Ну, а если же ваш сайт ежедневно посещают более 100 тысяч пользователей, то всякие там проблемы с подбором СУБД вы можете запросто возложить на нанятых экспертов заплатив им за это достаточно хорошие деньги.
На самом же деле, все не так просто, как это пишут представители SQLite и, эффективность работы СУБД зависит на только от количества посещений вашего сайта, но и от десятков других факторов. Поэтому, если вы замечаете, что ваш web-сайт начинает «тормозит», то при условии использования в его коде SQLAlchemy, вы можете легко перенести его базу данных с SQLite, например в СУБД MySQL или PostgreSQL. Это объясняется тем, что благодаря SQLAlchemy при переносе баз данных на другие СУБД, все описательные модели данных у вас остаются прежними. Однако, перед переносом своей базы данных на другую СУБД, вы конечно же должны как можно детальнее сравнить SQLite с MySQL и PostgreSQL для того, чтобы выбрать наиболее подходящую СУБД для своего приложения.
Изначально, при старте работы над созданием web-приложения на Python, конечно же имеет смысл остановиться на СУБД SQLite, поскольку, размещаясь лишь в одном файле, вышеназванная база данных является наиболее непритязательной и простой в обслуживании.
Выводы
В этой статье мы с вами рассмотрели множество вопросов о базах данных, как таковых, а также о таких их инструментах, как SQLite, SQL и SQLAlchemy. Кроме того, все эти инструменты баз данных были испробованы нами на практике, например, для перемещения данных из плоских файлов в СУБД SQLite, для доступа к данным с помощью SQL или SQLAlchemy, а также для отображения информации из удаленной базы данных через web-сервер.
В частности, мы с вами узнали:
- Почему реляционные базы данных, в частности, СУБД SQLite являются бесспорно наилучшей альтернативой хранению данных в виде плоских файлов.
- Как нормализовать данные с тем, чтобы уменьшить их избыточность и повысить целостность хранимой информации.
- Как использовать SQLAlchemy для объектно-ориентированного взаимодействия программ на Python с базами данных
- Как создать web-приложение, обеспечивающее возможность удаленного обслуживания баз данных несколькими пользователями через локальные или глобальные сети.
Взаимодействие с базами данных – это, по сути, отдельное направление программирования, которое отвечая за весь спектр когерентного хранения, обновления и предоставления данных, позволяет делать наши программы на Python полностью релевантными, а значит приспособленными к нынешним постоянно изменяющимся реалиям.
Для получения доступа к материалам практических примеров, приведенных в данной статье, вам следует предварительно скачать соответствующий архив с кодом и данными для этих примеров, а затем создать среду для их выполнения. О том, как это сделать вы сможете узнать открыв соответствующий PDF файл с инструкцией.
Возможно будет интересно
Применение геттер и сеттер методов для закрытых атрибутов классов
Что же все-таки обозначает эта загадочная ИНКАПСУЛЯЦИЯ и, как она может быть реализована в классах Python с помощью не менее таинственных ГЕТТЕР и СЕТТЕР методов. Обо всем этом мы с вами узнаем из этой статьи.
Как практиковаться в Python?
Для улучшения качества знаний и повышения уровня программиста, необходим постоянный практикум. Где можно это организовать самостоятельно, и как практиковаться в Python?
