- Что такое SQLAlchemy
- Начало работы
- Подключение библиотеки
- Выбор базы данных
- Создание движка
- Создание первой таблицы
- Заполнение таблицы с книгами
- Проверка результатов
- Возможности SQLAlchemy
- Фильтр данных
- Изменение данных
- Удаление данных
- Кратко про SQLAlchemy
Как разработчику уйти от запросов SQL, заменив их на привычные ему методы и объекты? В нашей статье расскажем о библиотеке SQLAlchemy, покажем ее возможности, а также разберемся, как создавать таблицы и базы данных.
Что такое SQLAlchemy
SQLAlchemy — это фреймворк библиотеки Python, созданный в 2005 году Майком Байером. Назначение фреймворка — работа с базами данных реляционного типа, которая позволяет использовать возможности object relational mapper или сокращенно ORM. Эта аббревиатура расшифровывается как «объектно-реляционное преобразование».
SQLAlchemy можно использовать для работы с РostgreSQL, Oracle, SQLite, MySQL, Microsoft SQL Server и рядом других реляционных баз данных. Для хранения информации в таких БД служат таблицы, которые связаны между собой.
ORM можно назвать фундаментальной основой SQLAlchemy, так как эта технология программирования служит связующим звеном между написанным программистом кодом и собственно базой данных. Она использует методы объектов в коде без привлечения запросов SQL. То есть это настоящий маст-хэв для разработчиков, которые не готовы отказаться от обкатанных приемов в своей работе.
Давайте сравним, как будет выглядеть один и тот же запрос в SQL и в виде Python-кода. Предположим, нам нужно отсортировать по убыванию стоимости товары из категории «цветы» в таблице Products. В обоих случаях код будет несложным, но очередность действий проще отследить, используя ORM-представление.
| SQL | SELECT product_id, product_name, price, categoryFROM productsWHERE category = ‘flowers’ORDER BY price DESC; |
| ORM | query = session.query(products).filter(products.category == ‘flowers’).order_by(products.price.desc()).all() |
Что особенно приятно при работе с SQLAlchemy, так это возможность беспроблемного перехода с одной БД на другую. Например, меняя РostgreSQL на Oracle, не нужно переписывать код приложения. Достаточно изменить только название базы данных в соответствующих местах.
Достичь такой плавности при смене библиотек позволила компоновка SQLAlchemy, которая включает в себя две составляющих: SQLAlchemy ORM и SQLAlchemy Core.
SQLAlchemy ORM — это компонент, который упрощает управление базами данных за счет удобства представления информации. Запросы составляются и обрабатываются сразу на языке Python, то есть «переводить» их на SQL не нужно. При этом ORM сфокусирован на объектах и моделях.
SQLAlchemy Core (ядро) включает в себя полнофункциональный набор инструментов абстракции SQL. Он состоит из трех модулей, расположенных поверх DB-API:
- схемы и типов;
- SQL Expression Language — стандартизованного языка выражений, который подходит для работы с разными БД;
- Engine, который включает в себя connection pooling и dialect.
DB-API — это набор правил, которому подчиняются все перечисленные выше модули. А они, в свою очередь, отвечают за реализацию взаимодействия с базами данных. То есть, в отличие от ORM, в Core фокусировка направлена на таблицы, индексы и другие атрибуты аналогично стандартному SQL.
Оба компонента можно использовать по отдельности. ORM подойдет для основного массива проектов, подключать к работе Core удобно при обработке больших объемов данных.
Начало работы
Подключение библиотеки
Для работы с SQLAlchemy вам потребуется Python. Активные релизы для Linux, Windows, MacOS и других операционных систем можно скачать на официальном сайте сообщества.
После установки Python можно приступать к загрузке библиотеки SQLAlchemy. Сделать это можно несколькими способами.
- Команда для дистрибутива Anaconda будет выглядеть следующим образом:
| conda install -c anaconda sqlalchemy |
- Стандартный вызов библиотеки будет осуществляться через pip:
| pip install sqlalchemy |
После выполнения команды производится установка SQLAlchemy. По завершении процесса важно убедиться, что он прошел без ошибок. Для этого в командную строку вписывается запрос:
| >>> import sqlalchemy>>> sqlalchemy.__version__ |
Под этими строками программа должна написать установленную версию библиотеки, например, ‘2.0.31’. Это подтверждает, что все работает правильно.
Теперь присвоим нашей SQLAlchemy сокращенное название — bb (у вас оно может быть другим):
| from sqlalchemy as bb |
Выбор базы данных
SQLAlchemy поддерживает разные БД, включая Oracle, PostgreSQL, SQLite. Новичкам лучше использовать последний вариант, так как он устанавливается по умолчанию вместе с Python и не требует дополнительных драйверов. Здесь и далее все примеры будут приводиться именно для него.
Создание движка
Движок (объект Engine) нужен для подключения к базе данных. Он будет отвечать за управление БД и выполнение SQL-запросов. Для его создания нужно обратиться к команде create_engine:
| engine = bb.create_engine(‘sqlite:///myDatabase13.bb’) |
Внутри скобок нужно указать название вашей БД (sqlite), а после тройного слеша — имя хранилища (myDatabase13) и название библиотеки (bb).
Напоминаем, что все примеры команд мы приводим для SQLite. В других БД запрос может выглядеть иначе, так как разработчики редко придерживаются единого формата. Чтобы программа могла обрабатывать SQL-запросы, которые отличаются от принятых в конкретной database, нужно установить драйвер соответствующего диалекта.
Следующий этап — запуск движка для подключения к базе данных. Эта задача решается с помощью команды:
| conn = engine.connect() |
Здесь используется сокращение от слова connection, которое переводится как «подключение» или «соединение». В дальнейшем эта команда будет нужна для связи запросов с БД.
Теперь вводим запрос:
| metadata = bb.MetaData() |
Эта команда служит для привязки метаданных. То есть данных, в которых будет храниться вся информация по таблицам.
Создание первой таблицы
Предположим, что нам нужно создать таблицу с перечнем книг и указанием их авторов для городской библиотеки.
В SQLAlchemy всем объектам, включая таблицы, присваиваются понятные и простые названия. Поэтому нашу database назовем незатейливо — books.
В нашей таблице мы создадим пять столбцов:
- book-id — порядковый номер книги, то есть ее уникальный идентификатор;
- book-name — название;
- book-author — автор;
- book_year — год издания;
- book_is_taken — «книгу взяли», для отслеживания наличия книг в библиотеке.
Всего мы будем использовать три типа представления информации:
- Integer — целые числа;
- Text — текстовый формат;
- Boolean — булевые переменные, которые могут принимать только значение True (истина) или False (ложь).
При таких исходных условиях код в Python для создания файла с базой данных будет выглядеть следующим образом.
| books = bb.Table(‘books’, metadata,bb.Column(‘book_id’, bb.Integer, primary_key=True),bb.Column(‘book_name’, bb.Text),bb.Column(‘book_author’, bb.Text),bb.Column(‘book_year’, bb.Integer),bb.Column(’book_is_taken’, bb.Boolean, default=False)) |
Функция Table в первой строке служит для создания собственно таблицы, которая называется books и содержит метаданные. Пять столбцов — это объекты библиотеки SQLAlchemy, каждому из которых присвоено уникальное имя и тип данных.
Первый столбец с нумерацией книг нам нужно было сделать главным, чтобы он служил идентификатором строк и формировался автоматически. Для этого мы использовали параметр primary_key=True.
Для удобства принимаем, что все книги сейчас находятся в городской библиотеке. Поэтому последней строке «book_is_taken» по умолчанию присваиваем значение «Ложь».
До создания таблицы остался один шаг. Вводим команду:
| metadata.create_all(engine) |
После запуска написанного нами кода на Python в библиотеке SQLAlchemy появится первая таблица. Но данных в ней пока нет. Поэтому переходим к следующему этапу.
Заполнение таблицы с книгами
Допустим, мы хотим добавить в нашу таблицу следующие книги:
- Максим Фрай, «Лабиринт», 1996 год;
- Сергей Лукьяненко, «Спектр», 2002 год;
- Теа Бекман, «Крестоносец в джинсах», 1973 год.
Каждый добавленный в таблицу элемент должен быть в виде объекта. Поэтому код в командной строке прописывается следующим образом:
| insertion_query = books.insert()values([{‘book_name’: ‘Лабиринт’, ‘book_author’: ‘Максим Фрай’, ‘book_year’: 1996},{‘book_name’: ‘Спектр’, ‘book_author’: ‘Сергей Лукьяненко’, ‘book_year’: 2002},{‘book_name’: ‘Крестоносец в джинсах’, ‘book_author’: ‘Теа Бекман’, ‘book_year’: 1973}]) |
В первой строке указываем, к какой именно таблице мы обращаемся. С помощью функции insert() даем программе понять, что будем добавлять в БД новые элементы. Вставка values() показывает, что все данные будут добавлены пользователем самостоятельно.
Так как все добавляемые книги являются объектами с заполненными свойствами, каждую их них заключаем в фигурные скобки и выделяем отдельной строкой. Нам нужно указать данные только для трех из пяти столбцов, так как первый (book_id) и последний (book_is_taken) программа заполнит автоматически.
Однако набранного кода недостаточно для того, чтобы наша таблица обновилась в базе данных. Для переноса информации нужно создать дополнительный запрос с помощью объекта conn.
| conn.execute(insertion_query) |
Обратите внимание на метод execute в коде Python — это ключик, который открывает двери БД для записи новой информации. Если пропустить данный этап, таблица не обновится, оставшись пустой.
Проверка результатов
Теперь нужно проверить, все ли данные были внесены верно. Для этого выполняется сбор информации с помощью двух методов — select и conn.
| select_all_query = bb.select([books])select_all_results = conn.execute(select_all_query) |
Команда в первой строке показывает, что мы просим программу собрать всю информацию из таблицы с книгами. Напомним, что она представлена в виде объекта.
Запрос select подразумевает сбор данных сразу с нескольких источников, поэтому нам нужно его конкретизировать. А именно: поставить перед программой задачу отобразить объекты таблицы в виде списка.
Вторая строка с запросом conn нужна, чтобы связать нашу команду с database. В противном случае программа просто не поймет поставленную задачу.
Вывод информации в консоль выполняется с помощью третьей команды — print. Обратите внимание, что в этом запросе мы дополнительно используем метод fetchall(). Если его убрать, то вместо списка с результатами программа выведет в консоль набор цифр и букв. Они показывают, где именно находятся данные, но нужная нам информация с перечнем книг отображаться на экране не будет.
| print(select_all_results.fetchall()) |
Команда Print запустит написанный нами Python-код. Если все было сделано верно, в консоли появится список из трех объектов.
| [(1, ‘Лабиринт’, ‘Максим Фрай’, 1996, False), (2, ‘Спектр’, ‘Сергей Лукьяненко’, 2002, False), (3, ‘Крестоносец в джинсах’, ‘Теа Бекман’, 1973, False)] |
Первая таблица в БД SQLite готова, заполнена, проверена. Теперь разберемся, как работать с этими данными.
Возможности SQLAlchemy
Так как для создания команд в SQLAlchemy используется код Python, программист может прямо в нем ставить задачи по выполнению самых разных SQL-запросов. Основные из них — это фильтр данных, обновление информации, удаление ненужных сведений.
Сейчас наша таблица состоит из трех элементов и выглядит следующим образом.
| book_id | book_name | book_author | book_year | book_is_taken |
| 1 | Лабиринт | Максим Фрай | 1996 | False |
| 2 | Спектр | Сергей Лукьяненко | 2002 | False |
| 3 | Крестоносец в джинсах | Теа Бекман | 1973 | False |
На этом примере покажем, как обрабатывать текущую информацию и вносить в нее изменения.
Фильтр данных
Для сортировки элементов в SQLAlchemy применяется метод Where. После этого слова в скобках указываем условие, которое должно быть выполнено, чтобы информация из строки в таблице отобразилась в финальной выдаче. Все остальное будет признано программой нерелевантным запросу.
Предположим, что нам нужно сделать выборку по автору.
| select_author_query = bb.select([books]).where(books.columns.book_author==’Сергей Лукьяненко’)select_all_results = conn.execute(select_author_query)print(select_all_results.fetchall()) |
В первой строке мы вписали фразу books.columns.book_author==’Сергей Лукьяненко’. Она означает, что программе нужно:
- обратиться к таблице с книгами;
- среди ее колонок (columns) выбрать столбец с авторами;
- из указанного столбца вычленить только книги конкретного писателя.
Вторая строка нужна для создания связки между запросом и базой данных. Напомним, что за эту задачу отвечает объект conn.
Третья строка служит для вывода результата на экран с помощью команды print и метода fetchall.
В наш библиотечный каталог сейчас внесена всего одна книга российского фантаста, поэтому программа выведет только ее.
| >> [(2, ‘Спектр’, ‘Сергей Лукьяненко’, 2002, False)] |
Добавим еще пару книг этого автора и снова введем команду where для их вывода в консоль.
| insertion_query = books.insert().values([{‘book_name’:’Черновик’, book_author’:’Сергей Лукьяненко’, ‘book_year’: 2006},{‘book_name’:’Ночной дозор’, book_author’:’Сергей Лукьяненко’, ‘book_year’: 1998}, |
В нашей таблице появятся две новые строки.
| book_id | book_name | book_author | book_year | book_is_taken |
| 1 | Лабиринт | Максим Фрай | 1996 | False |
| 2 | Спектр | Сергей Лукьяненко | 2002 | False |
| 3 | Крестоносец в джинсах | Теа Бекман | 1973 | False |
| 4 | Черновик | Сергей Лукьяненко | 2006 | False |
| 5 | Ночной дозор | Сергей Лукьяненко | 1998 | False |
Повторяем команду сделать выборку по конкретному автору.
| select_author_query = bb.select([books]).where(books.columns.book_author==’Сергей Лукьяненко’)select_all_results = conn.execute(select_author_query)print(select_all_results.fetchall()) |
Программа должна вывести список из трех книг.
| >> [(2, ‘Спектр’, ‘Сергей Лукьяненко’, 2002, False), (4, ‘Черновик’, ‘Сергей Лукьяненко’, 2006, False), (5, ‘Ночной дозор’, ‘Сергей Лукьяненко’, 1998, False)] |
Изменение данных
Информацию о каждом элементе можно изменять с помощью функции обновления. Для этого используется запрос update.
Например, книга «Лабиринт» также издавалась под другим названием — «Чужак». Изменим его.
| update_query = bb.update(books).where(books.columns.book_name==’Лабиринт’).values(book_name=’Чужак’)conn.execute(update_query) |
С помощью команды update мы показали программе, что будем вносить изменения в таблицу books. Для выбора нужной нам книги нужно использовать фильтр where, а для смены названия — функцию values.
Чтобы проверить результат и вывести его на консоль, воспользуемся командами select, conn, print.
| select_all_query = bb.select([books])select_all_resalts = conn.execute(select_all_query)print(select_all_resalts.fetchall()) |
После обновления в первой строке таблицы должно измениться название книги.
| book_id | book_name | book_author | book_year | book_is_taken |
| 1 | Чужак | Максим Фрай | 1996 | False |
| 2 | Спектр | Сергей Лукьяненко | 2002 | False |
| 3 | Крестоносец в джинсах | Теа Бекман | 1973 | False |
| 4 | Черновик | Сергей Лукьяненко | 2006 | False |
| 5 | Ночной дозор | Сергей Лукьяненко | 1998 | False |
Удаление данных
Предположим, книга из-за ветхости пришла в негодность, и ее нужно удалить из каталога. Для этой цели воспользуемся функцией delete.
Составление Python-кода в этом случае строится по той же логике, что и команды, которые мы разбирали ранее. Сначала нужно обратиться к методу delete, привязать к нему объект (нашу таблицу), с помощью фильтра where выбрать удаляемую строку.
Например, нам нужно вычеркнуть из каталога книгу «Крестоносец в джинсах».
| delete_query = bb.delete(books).where(books.columns.book_name==’Крестоносец в джинсах’)conn.execute(delete_query) |
Как и в предыдущих примерах, для соединения с database используем функцию conn.
Чтобы внесенные изменения отобразились в консоли, воспользуемся запросами select, conn, print, fetchall().
| select_all_query = bb.select([books])select_all_results = conn.execute(select_all_query)print(select_all_results.fetchall()) |
В таблице должна измениться нумерация и исчезнуть ненужная строка.
| book_id | book_name | book_author | book_year | book_is_taken |
| 1 | Чужак | Максим Фрай | 1996 | False |
| 2 | Спектр | Сергей Лукьяненко | 2002 | False |
| 3 | Черновик | Сергей Лукьяненко | 2006 | False |
| 4 | Ночной дозор | Сергей Лукьяненко | 1998 | False |
Кратко про SQLAlchemy
Библиотека SQLAlchemy создана для работы с базами данных. Вместо запросов SQL в ней используется Python-код. Лежащая в ее основе технология ORM позволяет все элементы БД рассматривать и обрабатывать как обычные объекты. Это удобно для разработчиков, которые привыкли работать с Python и стремятся оптимизировать взаимодействие с database.
Основная масса запросов в SQLAlchemy — стандартная. В пул команд входят функции filter, where, update, select, delete. То есть программисту не нужно ломать голову, запоминая или придумывая что-то новое. При этом библиотека обладает высокой адаптивностью — вы можете работать, например, в SQLite, а затем перенести всю информацию в PostgreSQL.