SQLAlchemy: что это такое и зачем нужно

SQLAlchemy позволяет при работе с базами данных вместо SQL-запросов использовать привычные для разработчика объекты и их методы. И это очень удобно.

Библиотека поддерживает различные СУБД, в том числе SQLite, PostgreSQL и MySQL. Причём управлять ими можно, используя одни и те же универсальные методы.

Давайте познакомимся с SQLAlchemy, разберёмся, как с ней работать, а заодно создадим с её помощью свою базу данных.

Из этой статьи вы узнаете:

• что такое SQLAlchemy;
• как её подключить;
• как создать первую базу данных и таблицу в ней;
• какие дополнительные возможности есть в библиотеке.

Что такое SQLAlchemy

SQLAlchemy — это Python-библиотека, которая позволяет работать с реляционными базами данных с помощью ORM.

Реляционные базы данных хранят информацию в виде связанных между собой таблиц. К таким базам относят Oracle, Microsoft SQL Server, PostgreSQL и другие. Реляционные базы данных работают по принципам реляционной алгебры, но это тема для отдельной статьи.

Сила SQLAlchemy — в её ORM. Расшифровывается как object relational mapper, или «объектно-реляционное отображение». ORM позволяет управлять базами данных с помощью методов объектов в коде и при этом не использовать SQL-запросы. На самом деле это очень удобно, так как позволяет писать привычный код, не переключаясь на SQL.

Например, вместо SQL-запроса можно вызвать несколько методов, которые сделают то же самое:

# SQL-запрос
SELECT product_id, product_name, price, category
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

# ORM-представление
query = session.query(products).filter(products.category == 'Electronics').order_by(products.price.desc()).all()

Здесь мы обращаемся к строкам таблицы products, в которой есть категория Electronics, а затем сортируем товары по убыванию цены. Запрос несложный, но по коду на Python проще понять, какие действия и когда выполняются, особенно если вы незнакомы с SQL.

Ещё одна особенность SQLAlchemy в том, что мы можем заменить базу данных в любой момент — например, с SQLite на PostgreSQL. Код приложения останется ровно тем же и продолжит работать. Нам не придётся переписывать ничего, кроме тех мест, где мы указываем, какую базу данных используем.

Всё благодаря тому, что библиотека состоит из двух компонентов — SQLAlchemy Core и SQLAlchemy ORM.

• SQLAlchemy Core — это как бы абстракция над SQL. Компонент умеет общаться с SQL и превращать Python-код в понятные для баз данных запросы с помощью SQL Expression Language. При этом он понимает, с каким именно видом хранилища данных взаимодействует.
• SQLAlchemy ORM — удобный для разработчика интерфейс для управления базами данных. Компонент позволяет работать с ними и коммитить запросы прямо в Python-коде — без необходимости переписывать всё на язык SQL.

Теперь перейдём к практике. Мы будем работать с SQLAlchemy ORM, потому что это самый простой способ общаться с базами данных. Для начала установим библиотеку, а затем создадим первую таблицу.

Как подключить SQLAlchemy

Если у вас уже установлен Python, то скачать библиотеку SQLAlchemy можно, например, через pip:

pip install sqlalchemy

Если вы используете дистрибутив Anaconda, воспользуйтесь такой командой:

conda install -c anaconda sqlalchemy

Теперь убедитесь, что установка библиотеки прошла успешно:

>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.41'

Если мы получили в ответе версию библиотеки, то всё установлено правильно. При импорте библиотеки сразу зададим ей короткое имя, чтобы постоянно не писать sqlalchemy:

from sqlalchemy as db

Здесь мы указали сокращение — db. Дальше будем использовать именно его.

Как создать первую таблицу

Чтобы создать первую базу данных, нам нужно выбрать один из диалектов SQL. Вот список нескольких, которые поддерживает библиотека SQLAlchemy:

• PostgreSQL;
• MySQL;
• SQLite;
• Oracle;
• Microsoft SQL Server (MS SQL).

Мы будем использовать SQLite, так как для неё не нужно поднимать сервер и она уже установлена вместе с Python. При этом дополнительно импортировать ничего не придётся — SQLAlchemy всё сделает за нас.

Создаём движок

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

Чтобы создать движок, воспользуйтесь функцией create_engine:

engine = db.create_engine('sqlite:///myDatabase.db')

SQLAlchemy использует определённую URL-структуру для обозначения базы данных и её имени. Внутрь функции мы передаём строку с названием диалекта базы данных, который нам нужен (sqlite), а затем через двоеточие и три слеша — название хранилища и расширение db.

Теперь нужно запустить движок и создать подключение:

conn = engine.connect()

С помощью этой строки мы подключились к базе данных. Объект conn понадобится нам дальше для выполнения SQL-запросов. conn — это сокращение от connection — «соединение». А соединять мы будем как раз наши запросы и базу данных.

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

metadata = db.MetaData()

Готово — можно переходить к созданию первой таблицы.

Добавляем первую таблицу

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

Ещё раз напомним, что в SQLAlchemy всё представлено в виде объектов, а значит, таблица — это тоже объект. Его принято называть понятно и с маленькой буквы:

books = db.Table(′books′, metadata, 
  db.Column(′book_id′, db.Integer, primary_key=True),
  db.Column(′book_name′, db.Text),
  db.Column(′book_author′, db.Text),
  db.Column(′book_year′, db.Integer),
  db.Column(′book_is_taken′, db.Boolean, default=False)
)

Появилось много непонятного кода. Давайте разбираться.

• Сначала мы создали таблицу с помощью функции Table. Внутрь передали название этой таблицы (books) и метаданные (metadata), указали, из каких она будет состоять столбцов.
• Каждый столбец — это объект SQLAlchemy, который состоит из нескольких параметров. Обязательными являются имя и тип данных.
• Мы создали пять столбцов: уникальный идентификатор книги (book_id), название (book_name), автор (book_author), год (book_year) и статус доступности книги в библиотеке (book_is_taken).
• Для всех столбцов также указали типы — например, Integer, Text и Boolean. Они представляют собой обычные типы целых чисел, строк и булевых переменных соответственно.
• Ещё мы добавили для первого столбца параметр primary_key=True. Это значит, что столбец будет главным — то есть по нему будут идентифицироваться строки и он будет вычисляться автоматически.
• Для последнего столбца указали значение по умолчанию — False. Всё потому, что сначала книги всегда находятся в библиотеке.

Выглядеть наша таблица будет так:

А в заполненном виде — вот так:

Теперь, чтобы создать объект таблицы, вызовем функцию create_all:

metadata.create_all(engine)

Запустим написанный Python-код и создадим файл с базой данных.

Заполняем таблицу

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

Чтобы добавить элементы в базу данных, нужно создать запрос в виде объекта. Делается это так:

insertion_query = books.insert().values([
  {′book_name′:′Бесы′, ′book_author′:′Фёдор Достоевский′, ′book_year′:1872},
  {′book_name′:′Старик и море′, ′book_author′:′Эрнест Хемингуэй′, ′book_year′:1952}
])

Разберём написанное. В начале мы указали, что обращаемся к таблице books для добавления в неё новых элементов с помощью insert(). Далее мы говорим, что будем сами добавлять значения — values(). И передаём внутрь значений элементы в фигурных скобках — то есть объекты с заполненными свойствами.

Каждая строка — отдельный объект, в котором указываются название столбца и его значение через двоеточие. Заметьте, что мы не указывали значения для столбцов book_id и book_is_taken, потому что они заполняются автоматически.

Чтобы данные закоммитились в базу данных, нужно обратиться к объекту conn и передать ему наш запрос:

conn.execute(insertion_query)

Метод execute отправит запрос к базе данных и попросит её записать новую информацию. Если не выполнить его, мы не увидим новых строк в таблице. Это то же самое, что написать SQL-запрос в терминале и забыть нажать Enter.

Запускаем Python-код и переходим к следующему этапу — выводу данных в консоль.

Выводим элементы базы данных в консоль

Пришло время убедиться, что данные действительно добавляются в таблицу. Для этого в SQLAlchemy есть метод select, который и поможет вывести информацию в консоль.

select_all_query = db.select([books])
select_all_results = conn.execute(select_all_query)

Здесь в первой строке мы создали новый запрос — получить все данные из таблицы books. Так как функция select ожидает, что мы будем обращаться сразу к нескольким таблицам, то объект таблицы нужно передать как список.

Во второй строке мы снова используем соединение conn, чтобы выполнить запрос к базе данных. Внутрь передаём наш новый запрос. Теперь выведите результат в консоль:

print(select_all_results.fetchall())

Просто так вывести значение объекта select_all_results не получится, потому что после выполнения предыдущей команды объект-соединение вернёт нам только указание на расположение данных. Это что-то вроде ссылки на область в памяти компьютера. Если прочитать её, мы увидим набор непонятных значений — например, 0xe1344af.

Чтобы получить сами данные, нужно дополнительно вызвать метод fetchall().

Вот что увидим в консоли после запуска Python-кода:

[(1, ′Бесы′, ′Фёдор Достоевский′, 1872, False), (2, ′Старик и море′, ′Эрнест Хемингуэй′, 1952, False)]

Получили список из двух объектов. Всё, как и задумывалось.

Какие ещё возможности есть в SQLAlchemy

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

С SQLAlchemy можно выполнять любые SQL-запросы прямо в Python-коде. Это значит, что мы можем изменять, удалять, сортировать и фильтровать данные, а также многое другое. Давайте посмотрим на пару примеров.

Напомним, как выглядит наша таблица:

Фильтруем данные — запрос where

Чтобы отфильтровать данные, создадим новый запрос:

select_author_query = db.select([books]).where(books.columns.book_author==′Фёдор Достоевский′)
select_all_results = conn.execute(select_author_query)

Метод where работает как самый простой фильтр: если выполняется условие внутри скобок, то мы забираем строку таблицы в финальную выдачу. А если нет — пропускаем и идём дальше.

Наш фильтр выберет из таблицы все строки, у которых значение колонки book_author равно «Фёдор Достоевский». Это соответствует условию books.columns.book_author==′Фёдор Достоевский′. Здесь мы указываем, что берём таблицу books, смотрим на её столбцы columns и фильтруем по колонке book_author.

Вторая строка кода просто выполнит команду и обновит данные в таблице. За это отвечает соединение — объект conn.

Результат получаем уже знакомым способом — с помощью метода fetchall():

print(select_all_results.fetchall())
>> [(1, ′Бесы′, ′Фёдор Достоевский′, 1872, False)]

Видим, что вывелась только одна строка. Но если бы у нас было больше книг от Достоевского в таблице, то вывелись бы все. Давайте добавим ещё две:

insertion_query = books.insert().values([
  {′book_name′:′Униженные и оскорблённые′, ′book_author′:′Фёдор Достоевский′, ′book_year′:1861},
  {′book_name′:′Братья Карамазовы′, ′book_author′:′Фёдор Достоевский′, ′book_year′:1880}
])

Таблица теперь выглядит так:

Снова выполним запрос:

select_author_query = db.select([books]).where(books.columns.book_author==′Фёдор Достоевский′)


select_all_results = conn.execute(select_author_query)

print(select_all_results.fetchall())

Результат:

[(1, ′Бесы′, ′Фёдор Достоевский′, 1872, False), (3, ′Униженные и 
оскорблённые′, ′Фёдор Достоевский′, 1861, False), (4, ′Братья 
Карамазовы′, ′Фёдор Достоевский′, 1880, False)]

Получили все строки с нужным автором.

Обновляем данные — запрос update

Данные можно не только получать, но и обновлять. Например, мы вдруг вспомнили, что первая версия «Братьев Карамазовых» Достоевского была опубликована в 1879 году. По крайней мере, на это ссылаются некоторые филологи. Поэтому давайте обновим дату публикации.

Чтобы изменить данные, используйте функцию update:

update_query = db.update(books).where(books.columns.book_name==′Братья 
Карамазовы′).values(book_year=1879)


conn.execute(update_query)

Здесь мы сначала вызвали функцию update и передали в неё таблицу. Затем выбрали нужную строку через фильтрацию с помощью функции where. А потом с помощью функции values изменили год в нужной строке.

Стоит отметить, что объект-соединение conn не вернёт ничего после выполнения запроса. Поэтому, чтобы вывести элементы таблицы в консоль, придётся воспользоваться функцией select:

select_all_query = db.select([books])


select_all_results = conn.execute(select_all_query)

print(select_all_results.fetchall())

Посмотрим на обновлённую таблицу:

Отлично — данные обновились.

Удаляем данные — запрос delete

Ненужные строки в SQLAlchemy можно удалить с помощью функции delete:

delete_query = db.delete(books).where(books.columns.book_name==′Братья 
Карамазовы′)


conn.execute(delete_query)

Логика кода выше похожа на предыдущие команды. Мы вызываем функцию delete, в неё передаём объект таблицы, а затем с помощью фильтров выбираем строку, которую нужно удалить. В нашем случае мы хотим удалить книгу с названием «Братья Карамазовы».

Функция delete также не возвращает ничего после выполнения запроса. Поэтому выводить элементы в консоль нужно с помощью функции select:

select_all_query = db.select([books])
select_all_results = conn.execute(select_all_query)

print(select_all_results.fetchall())

Таблица, из которой удалили элемент, выглядит так:

Всё работает — элемент и правда удалён.

Что запомнить

Резюмируем всё, что узнали об SQLAlchemy:

• SQLAlchemy — это библиотека для Python, которая умеет общаться с базами данных прямо в Python-коде.
• Главное её достоинство — ORM. С помощью него можно работать с базами данных, как будто это обычные объекты в языке Python.
• Библиотека поддерживает много популярных диалектов SQL — например, MySQL, SQLite и PostgreSQL.
• SQLAlchemy умеет выполнять все запросы, доступные в SQL. Поэтому вы не заметите отличий от самого SQL, когда перейдёте на неё.
• В библиотеке есть привычные запросы: select, update, delete, filter, where и другие.