SQLAlchemy — это библиотека Python для работы с реляционными базами данных. С ее помощью можно работать с PostgreSQL, MySQL, SQLite и другими СУБД.
Что такое SQLAlchemy и из каких компонентов состоит
Библиотека SQLAlchemy предоставляет инструменты Core и ORM для работы с базами данных.
SQLAlchemy Core — низкоуровневый API для работы напрямую с SQL. Позволяет генерировать и выполнять SQL-запросы, управлять таблицами и схемами базы.
- Полный контроль над SQL-запросами. То есть можно явно указать, какие столбцы, условия, сортировку и группы нужно использовать.
- Близок к «чистому» SQL: структура запросов должна быть явно описана в Python-коде.
- Подходит для сложных запросов, где требуется оптимизация или использование специфичных SQL-функций.
SQLAlchemy ORM (Object-Relational Mapping) — высокоуровневый инструмент, абстрагирующий от прямого написания SQL-запросов. Работает через объекты Python, представляющие строки таблиц.
- Удобен для разработки приложений с бизнес-логикой, так как будет меньше ручного написания SQL-запросов.
- Позволяет работать с данными как с объектами, а не таблицами и строками. ORM автоматически преобразует действия с объектами в запросы.
Основные компоненты
- Engine — это объект, который отвечает за подключение к базе данных и управление низкоуровневыми операциями, такими как выполнение SQL-запросов и управление соединениями с базой данных.
— В Core необходим для выполнения SQL-запросов, создания таблиц и работы с транзакциями.
— В ORM также нужен для создания сессий и выполнения операций с базой данных через объекты Python.
- Session — компонент, который управляет всеми операциями с объектами базы данных в SQLAlchemy ORM.
Он позволяет создавать, обновлять, удалять и читать данные в виде объектов Python. Также он управляет транзакциями и гарантирует, что изменения в базе данных будут сделаны атомарно.
- MetaData — это объект, который хранит описание структуры базы данных: таблицы, столбцы, связи между таблицами (например, внешние ключи).
— В Core необходим для создания и модификации таблиц. Все таблицы должны быть зарегистрированы в объекте MetaData перед их созданием или изменением в базе данных.
— В ORM используется для работы с таблицами через классы, чтобы связать их с базой данных.
Начало работы с SQLAlchemy
Как установить библиотеку SQLAlchemy и начать работу
- Для начала установим SQLAlchemy с помощью пакетного менеджера pip.
pip install sqlalchemy
- Затем нужно установить драйвер для вашей базы данных.
Для PostgreSQL SQLAlchemy требует драйвера psycopg2:
pip install sqlalchemy psycopg2
Или легкую версию psycopg2-binary (подходит для большинства случаев):
pip install psycopg2-binary
Для MySQL нужны драйвера pymysql:
pip install pymysql
pip install mysql-connector-python
Что касается SQLite, то драйвер для него уже встроен в Python.
- Создаем объект Engine, который отвечает за подключение.
Импортируем функцию create_engine:
from sqlalchemy import create_engine
Затем создаем объект Engine для выбранной базы данных.
Для PostgreSQL:
engine = create_engine('postgresql+psycopg2://user:password@localhost/dbname')
Для MySQL:
engine = create_engine('mysql+pymysql://user:password@localhost/dbname')
Для SQLite:
engine = create_engine('sqlite:///dbname.db')
Вместо user и password нужно указать учетные данные, вместо localhost — хост сервера базы данных, вместо dbname — имя базы данных.
В примерах ниже будем считать, что engine уже определен.
Что делать дальше?
После создания объекта Engine можно начинать работу с SQLAlchemy и создавать таблицы. Для этого используют Core или ORM.
Важно:
Хотя основные процессы SQLAlchemy (создание баз данных, чтение данных и т. д.) унифицированы для разных видов баз данных, каждая база имеет свои особенности. Например, некоторые типы данных специфичны для конкретной СУБД:
— PostgreSQL поддерживает JSONB и ARRAY.
— MySQL работает с TINYINT и ENUM.
— SQLite использует упрощенную типизацию (например, VARCHAR ведет себя как TEXT, так как SQLite не ограничивает длину строк).
Работа с SQLAlchemy Core
Core используют, когда нужны полный контроль над SQL-запросами, высокая производительность и работа с нестандартными конструкциями SQL.
В SQLAlchemy Core SQL-запросы можно писать напрямую, используя методы insert(), select(), update() и delete(). Эти методы обозначают SQL-запросы, которые отправляются в базу данных для выполнения.
Создание таблиц через MetaData
В SQLAlchemy Core таблицы, а точнее их сущности, создаются с использованием объекта MetaData, который служит контейнером для описания таблиц и их связей.
MetaData — схема, которая хранит метаинформацию о структуре базы данных: названия таблиц, столбцов, их типы и ограничения.
Создаем таблицу
- Сначала нужно импортировать компоненты: Table для создания таблицы, Column для описания столбцов, Integer и Spring как типы данных для столбцов и MetaData для управления схемой базы данных:
from sqlalchemy import Table, Column, Integer, String, MetaData
- Создаем объект MetaData для управления схемой базы данных:
metadata = MetaData()
- Описываем таблицу:
users = Table(
'users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('email', String)
)
- Создаем таблицу в базе данных с помощью метода create_all:
metadata.create_all(engine)
Таблица созданы. Теперь рассмотрим операции добавления, чтения, обновления и удаления данных из таблицы.
Добавление данных в таблицу
- Для этого сначала импортируем функцию вставки insert:
from sqlalchemy import insert
- Вставляем данные. Например, в таблицу users вставим данные о пользователе Maria:
insert_query = insert(users).values(name='Maria', age=30) with engine.connect() as connection: connection.execute(insert_query)
Чтение данных из таблицы
- Импортируем функцию выбора select:
from sqlalchemy import select
- Запрашиваем данные. Например, из таблицы user_table:
select_query = select(users) with engine.connect() as connection: result = connection.execute(select_query) for row in result: print(row)
Обновление данных в таблице
- Для этого сначала импортируем функцию обновления update:
from sqlalchemy import update
- Обновляем данные. Например, изменим email пользователя с именем Maria в таблице users:
update_query = update(users).where(users.c.name == 'Maria').values(email='newemail@example.com') with engine.connect() as connection: connection.execute(update_query)
Удаление данных из таблицы
- Для этого сначала импортируем функцию удаления delete:
from sqlalchemy import delete
- Удаляем данные. Например, удалим пользователя с именем Maria из таблицы users:
delete_query = delete(users).where(users.c.name == 'Maria') with engine.connect() as connection: connection.execute(delete_query)
Запросы SQL через метод execute()
Метод execute() в SQLAlchemy Core не только выполняет запросы, сгенерированные SQLAlchemy, но и позволяет напрямую выполнять сырые SQL-запросы. Это полезно для сложных запросов или операций, которые трудно выразить средствами ORM или Core.
result = engine.execute("SELECT * FROM users WHERE name = :name", {"name": "John"})
for row in result:
print(row)
Работа с SQLAlchemy ORM
ORM используют, когда нужно представить таблицы в виде классов Python. После этого с базой данных можно работать через объекты, а не точные SQL-запросы.
Чтобы представить таблицы базы данных в виде Python-классов, используют декларативный класс. В этом подходе каждый класс будет представлять таблицу в базе данных, а атрибуты класса — столбцы таблицы.
Создание таблиц через декларативный класс
- Для использования декларативного подхода нужно сначала импортировать необходимые компоненты, а именно:
— declarative_base: базовый класс для создания таблиц с использованием декларативной модели.
— Column, Integer, String: для описания столбцов таблицы и их типов.
from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String
- Создаем базовый класс Base, который будет использоваться для определения всех моделей (таблиц):
Base = declarative_base()
- Определяем класс, который будет представлять таблицу в базе данных. Например, создадим таблицу users:
class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) email = Column(String)
- После того как класс определен, можно создать таблицу в базе данных с помощью метода create_all:
Base.metadata.create_all(engine)
Работа с сессиями
Session управляет всем процессом работы с базой данных в ORM. Это объект, который отслеживает изменения объектов, сохраняет их в базе данных и управляет транзакциями.
Транзакция — это набор операций, которые выполняются как единое целое. Каждая транзакция должна удовлетворять четырем ключевым свойствам, которые называются ACID.
Рассмотрим, как создать объект Session и управлять с его помощью транзакциями.
Убедитесь, что перед этим вы создали engine и declarative_base, который передали в класс User.
- Сначала импортируем sessionmaker:
from sqlalchemy.orm import sessionmaker
- Создаем Session:
Session = sessionmaker(bind=engine) session = Session()
Управление транзакциями (commit, rollback, close)
Session автоматически начинает транзакцию при первом запросе к базе данных.
— Вызов commit() завершает сессию и сохраняет все изменения в базе данных.
— Вызов rollback() откатывает все изменения, сделанные в рамках текущей транзакции.
— Вызов close() закрывает сессию и освобождает все ресурсы, связанные с ней. Это не завершает транзакцию, а просто закрывает соединение с базой данных. Его обычно используют в конце работы с сессией, чтобы гарантировать, что все ресурсы освобождены.
Пример
- Добавим пользователя Maria с электронной почтой:
user = User(name='Maria', email='maria@example.com')
- Добавим пользователя в сессию:
session.add(user)
- Сделаем блок try-except-finally (для обработки исключений):
try:
# Подтверждаем транзакцию
session.commit()
print("Пользователь успешно добавлен!")
except:
# В случае ошибки откатываем транзакцию
session.rollback()
print("Произошла ошибка, откатываем транзакцию.")
finally:
# Закрываем сессию
session.close()
CRUD-операции
CRUD (Create, Read, Update, Delete) — это набор операций для управления данными через ORM. Он является стандартом взаимодействия с базами данных.
Create (создание)
Добавление объектов в базу данных. Соответствует операции INSERT в SQL.
- Создаем пользователя John с электронной почтой:
user = User(name='John', email='john@example.com')
- Добавляем пользователя в сессию:
session.add(user)
- Сохраняем изменения в базе данных:
session.commit()
Read (чтение)
Выполнение запроса данных из базы. Это соответствует операции SELECT в SQL.
- Запрашиваем данные через метод query() с фильтрацией по имени:
user = session.query(User).filter_by(name='John').first()
Здесь мы применяем фильтрацию, аналогичную SQL-условию WHERE name = ‘John’. Метод first() возвращает первую запись из результата запроса.
- Также можно использовать фильтры для поиска более конкретных записей аналогично WHERE в SQL. Применим сортировку и ограничение:
users = session.query(User).filter(User.age > 18).order_by(User.name).limit(10).all()
Здесь мы запрашиваем всех пользователей старше 18 лет, сортируем их по имени и ограничиваем результат первыми 10 записями.
Update (обновление)
Изменение существующих записей в базе данных. Это соответствует операции UPDATE в SQL.
- Находим пользователя, данные о котором нужно обновить:
user = session.query(User).filter_by(name='John').first()
- Изменяем атрибуты объекта:
user.name = 'Jane'
- Сохраняем изменения в базе данных:
session.commit()
Delete (удаление)
Удаление записей из базы данных. Это соответствует операции DELETE в SQL.
- Находим пользователя, которого нужно удалить:
user = session.query(User).filter_by(name='John').first()
- Удаляем запись:
session.delete(user)
- Подтверждаем удаление:
session.commit()
Связи между таблицами
В реляционной модели данных часто используются связи между таблицами. Основные типы связей:
- Один-к-одному (One-to-One): каждая запись одной таблицы связана с одной записью другой таблицы.
- Один-ко-многим (One-to-Many): одна запись одной таблицы может быть связана с несколькими записями другой таблицы.
- Многие-ко-многим (Many-to-Many): записи обеих таблиц могут быть связаны друг с другом в произвольном количестве.
SQLAlchemy ORM предоставляет инструменты для определения таких связей:
— ForeignKey определяет внешний ключ, указывающий на связь столбца с другой таблицей.
— relationship используется для указания логической связи между объектами.
ForeignKey
Нужен для указания столбца, который ссылается на другой столбец в другой таблице. Создает внешний ключ, обеспечивает связь на уровне базы данных.
Создадим декларативный класс и две базы данных — User и Address.
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
В примере:
- Таблица Address имеет внешний ключ user_id, который ссылается на поле id в таблице users.
- Это связь «один-ко-многим», так как один пользователь может иметь несколько адресов.
relationship
Инструмент relationship используют для создания логической связи между классами Python. Он позволяет напрямую обращаться к связанным объектам, создает между ними связь и позволяет работать с ними в объектно-ориентированном стиле.
Расширим предыдущий код, добавив связь через relationship:
from sqlalchemy.orm import relationship
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
# Связь с таблицей Address
addresses = relationship('Address', back_populates='user')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
# Связь с таблицей User
user = relationship('User', back_populates='addresses')
Теперь можно работать со связанными объектами.
- Добавлять данные:
new_user = User(name='John', addresses=[ Address(email_address='john.home@example.com'), Address(email_address='john.work@example.com') ]) session.add(new_user) session.commit()
- Получать доступ к связанным данным:
user = session.query(User).filter_by(name='John').first()
print("Адреса пользователя:", [address.email_address for address in user.addresses])
address = session.query(Address).filter_by(email_address='john.home@example.com').first()
print("Имя владельца адреса:", address.user.name)
Работа с большими запросами
Подзапросы
Подзапросы позволяют выполнить запрос внутри другого запроса, если нужно сделать сложную выборку данных и использовать ее в основном запросе.
- Пример подзапроса в ORM:
from sqlalchemy import func
# Подсчет количества пользователей по возрасту
result = session.query(User.age, func.count(User.id)).group_by(User.age).all()
for row in result:
print(f"Age: {row[0]}, Count: {row[1]}")
В примере:
— С помощью session.query(User.age, func.count(User.id)) создаем запрос, который выбирает два поля: возраст пользователя (User.age) и количество пользователей с этим возрастом (func.count(User.id)).
— Метод group_by(User.age) группирует результаты по возрасту, чтобы подсчитать количество пользователей для каждого возраста.
— Метод .all() выполняет запрос и возвращает все результаты, которые затем выводятся на экран.
- Пример подзапроса в Core:
from sqlalchemy import select, func
subquery = select([func.count().label('user_count')]).select_from(users)
main_query = select([users.c.name, subquery.c.user_count]).select_from(users)
with engine.connect() as connection:
result = connection.execute(main_query)
for row in result:
print(row)
В примере:
— В subquery создаем подзапрос, который подсчитывает количество всех пользователей с помощью func.count().
— В main_query основной запрос выбирает имена пользователей из таблицы users и добавляет результат подзапроса (subquery.c.user_count).
— Метод select_from(users) указывает таблицу, из которой будет выполняться запрос. В данном случае основной запрос выбирает пользователей из users, а подзапрос считает количество пользователей.
— Через connection.execute(main_query) выполняем запрос и выводим результаты.
Объединение запросов (join)
Как и с подзапросами, SQLAlchemy позволяет объединять данные из нескольких таблиц по определенным условиям с помощью join.
- Пример объединения запросов ORM:
result = session.query(User, Address).join(Address).all()
for user, address in result:
print(f"User: {user.name}, Address: {address.email_address}")
В примере:
— С помощью session.query(User, Address) создаем запрос, который выбирает данные из двух таблиц — User и Address.
— Метод .join(Address) инициирует внутреннее соединение (INNER JOIN) между таблицами User и Address.
— Результат содержит кортежи объектов User и Address, которые мы выводим на экран, получая имя пользователя и его адрес электронной почты.
- Пример объединения запросов Core:
main_query = select([users.c.name, addresses.c.email_address]).select_from( users.join(addresses) ) with engine.connect() as connection: result = connection.execute(main_query) for row in result: print(row)
В примере:
— Через users.join(addresses) выполняем соединение таблицы users с таблицей addresses по внешнему ключу, который, скорее всего, связан через поле user_id в таблице addresses.
— Метод select([users.c.name, addresses.c.email_address]) выбирает два поля: имя пользователя и его адрес электронной почты.
— Метод select_from(users.join(addresses)) выполняет соединение этих двух таблиц.
— Используем connection.execute(main_query) для выполнения запроса и вывода результатов.
Подведем итог
SQLAlchemy — мощный и гибкий инструмент для работы с базами данных в Python.
Если вам нужно работать с объектами, моделировать данные как классы Python и абстрагироваться от SQL-запросов, то используйте SQLAlchemy ORM.
Если же вам нужны больше контроля над SQL-запросами, работа с «сырыми» SQL-запросами или выполнение сложных операций с большими объемами данных, для этого подойдет SQLAlchemy Core.
