Вводная по сложным запросам в SQLAlchemy

Во время посещения PyConRu 2014 я, с удивлением, узнал, что достаточно большая аудитория python-разработчиков не использует SQLAlchemy в качестве основного инструмента для работы с базой данных. Порассуждав на данную тему после Light Talks с коллегами было принято решение — во чтобы то ни стало написать статью о том, что же можно делать со всей мощью SQLAlchemy.

Обычно в написании сайтов не требуется чего-нибудь этакого от штатного ORM. А если и требуется, то хватает замены на нештатный или прочтения основной части документации. И, как правило, голову ломать над сложными запросами не приходится. Достаточно много различных ORM предлагают классические схемы One-2-Many, One-2-One, Many-2-Many, и т.д. Для обычных запросов и связей этого вполне достаточно. К сожалению, в больших проектах не обходится без частных случаев и программисты при сложных запросах пишут либо raw sql, либо полагаются на то, что им предлагает базовый функционал ORM. Это выглядит не совсем красиво или создает достаточно большую нагрузку на базу данных.

Понятно, что в погоне за скоростью выполнения сценариев, можно пожертвовать красотой кода, но что если скоростью можно пренебречь, а вот кроссплатформенностью — нет? Да и не хочется в python коде видеть что-то кроме python кода. А что если хочется на полную катушку использовать любимый ORM (для меня SQLAlchemy) и не писать raw sql запросы?

Предполагается, что у вас уже настроен доступ к базе данных и вы знаете как маппить классы, создавать сессию и делать простейшие запросы (это описывается в документации к SQLAlchemy www.pythoncentral.io/series/python-sqlalchemy-database-tutorial/).

Ниже представлен набор классов, который мы будем использовать в наших примерах. Конечно он не покроет все те множества случаев, которые могут возникнуть, но, надеюсь, даcт вам низкий старт для написания собственных сложных запросов и поможет избавиться от ручного написания сложных SQL запросов.

Краткое примечание: Я не буду прописывать все импорты для каждого примера.
Вот некоторые функции, которые могут понадобиться:

from sqlalchemy import func, and_, or_, not_, aliased 

За остальным обращайтесь к документации.

Хочу отдельно отметить функцию func. Данная функция позволит сгенерировать почти любое выражение для вашей базы данных.

 from sqlalchemy import Column, Integer, String from sqlalchemy.schema import ForeignKey  class UserStatus(Base): 	__tablename__ = 'user_statuses' 	STATUS_INITIAL = 1  	id = Column(Integer(), primary_key=True) 	name = Column(String(), unique=True)  class User(Base): 	__tablename__ = 'users' 	id = Column(Integer(), primary_key=True) 	username = Column(String(), unique=True) 	password = Column(String(), nullable=False) 	status_id = Column( 		Integer(), 		ForeignKey('user_statuses.id'), 		nullable=False, 		default=UserStatuses.STATUS_INITIAL 	)  class Role(Base): 	__tablename__ = 'roles' 	id = Column(Integer(), primary_key=True) 	name = Column(String(), unique=True)  class UserRole(Base): 	__tablename__ = 'users_roles' 	user_id = Column(Integer(), ForeignKey('users.id')) 	role_id = Column(Integer(), ForeignKey('roles.id'))  class Product(Base): 	__tablename__ = 'products' 	id = Column(Integer(), primary_key=True) 	name = Column(String(), unique=True)  class Order(Base): 	__tablename__ = 'orders' 	id = Column(Integer(), primary_key=True) 	product_id = Column(Integer(), ForeignKey('products.id')) 	user_id = Column(Integer(), ForeignKey('users.id'))  

Да, в структуре отсутствуют отношения. Я их не стал дописывать по одной простой причине, они нам будут не нужны. Это, конечно, круто, когда прописаны все relations и backrefs, но ими можно выбирать только зависимые данные. Мы же попробуем в разных вариациях задействовать сразу все таблицы.

Простой JOIN

К примеру, нам нужно взять всех пользователей с их ролями, продуктами, заказами и статусами

 with SessionContext() as session: 	query = session.query(User, Role, Product, Order, UserStatus) 	records = query.all() 	for user, role, product, order, user_status in records: 		# execute all you need  

В данном случае, SQLAlchemy сгенерирует INNER JOIN. Данный способ хорош, когда у вас есть все индексы в базе (поверьте, очень часто их нет). SQLAlchemy сам сгенерирует запрос на основе данных класса (у нас ведь связи прописаны).

А что если у нас не все так гладко, и нет возможности указать Foreign Key в базе (разные причины)? Для этого SQLAlchemy позволяет явно указать по каким столбцам мы будем связывать таблицы.

 with SessionContext() as session: 	query = session.query(User, Role, Product, Order, UserStatus) 	query = query.join(UserRole, UserRole.user_id == User.id) 	query = query.join(Role, Role.id == UserRole.role_id) 	query = query.join(Order, Order.user_id == User.id) 	query = query.join(Product, Product.id == Order.product_id) 	query = query.join(UserStatus, UserStatus.id == User.status_id)  	records = query.all() 	for user, role, product, order, user_status in records: 		# execute all you need  

В данном случае, даже если в базе несвязанные данные, мы можем выбрать все нужные записи.

Простой LEFT JOIN

Предположим, нам нужно взять ВСЕХ пользователей и даже тех, у кого нет заказов. Т.е. если у пользователя есть заказы, то показать их, а если нет, то показать пользователя без заказов.

 with SessionContext() as session: 	query = session.query(User, Role, Product, Order, UserStatus) 	query = query.join(UserRole, UserRole.user_id == User.id) 	query = query.join(Role, Role.id == UserRole.role_id) 	query = query.join(UserStatus, UserStatus.id == User.status_id) 	query = query.outerjoin(Order, Order.user_id == User.id) 	query = query.outerjoin(Product, Product.id == Order.product_id)  	records = query.all() 	for user, role, product, order, user_status in records: 		# execute all you need  

Тут мы применили функцию outerjoin, что для PostgreSQL сгенерирует LEFT OUTER JOIN Запрос.

Сложные запросы.

Иногда возникает ситуация, когда нам надо сделать очень много сортировок и добавить много условий. Усложняет все, зачастую, сама база.

К примеру. Предположим, пользователи могут заказать один и тот же продукт несколько раз. Нужно выбрать запись, показывающую, покупал ли пользователь продукт, для каждого пользователя и каждого продукта. При этом, если пользователь покупал продукт больше одного раза, то не важно, о какой покупке будет выбрана информация. Если пользователь не покупал данный продукт, то желаемое поведение аналогично left join. Для группировки результатов можно использовать GROUP BY, если бы нам были не важны данные о покупке. Иначе необходимо указать все искомые поля (которые указаны в select) для GROUP BY, что крайне нежелательно, так как создаст дополнительную нагрузку на базу данных. Для выбора данных лучше использовать DISTINCT ON, который просто отсечет дубликаты записей по ID продукта и ID пользователя. Проблема в том, что PostgreSQL требует, чтобы те столбцы, которые указаны в DISTINCT ON, присутствовали в ORDER BY. А сортировать вывод я хочу по имени пользователя (к примеру). Вот тут-то и начинается самое интересное. К счастью, база данных позволяет «обернуть» один запрос в другой.

В SQLAlchemy есть функция cte() *Common Table Expression*. Данная функция создает подзапрос из вашего запроса.

Пример

 with SessionContext() as session: 	 		query = session.query(User, Role, Product, Order, UserStatus) 		query = query.distinct(Product.id, User.id) 		query = query.join(UserRole, UserRole.user_id == User.id) 		query = query.join(Role, Role.id == UserRole.role_id) 		query = query.join(UserStatus, UserStatus.id == User.status_id) 		query = query.outerjoin(Order, Order.user_id == User.id) 		query = query.outerjoin(Product, Product.id == Order.product_id) 		query = query.order_by(Product.id, User.id)  		found_records = query.cte()  		main_query = session.query(found_records).order_by(found_records.c.user.username)  		records = main_query.all() 		for user, role, product, order, user_status in records: 				# execute all you need  

В данном примере мы «изолировали» главный запрос и отсортировали результат так, как нам надо. Также можно поступать при сложных выборках с group_by.

В SQLAlchemy есть еще один полезный инструмент под названием subquery. Эта функция позволяет использовать сгенерированный statement в больших запросах или же в JOIN.

 with SessionContext() as session: 	stmt = session.query(Order.user_id, sqlalchemy.func.count('id').label('users_found')).subquery() 	 	main_query = session.query(User, stmt.c.users_found).outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id) 	records = main_query.all() 	for user, orders in records: 		# execute all you need  

Данный пример делает подзапрос внутри запроса через LEFT OUTER JOIN и выдает количество заказов на каждого найденного пользователя.

Также, бывают ситуации, когда надо связать одну и туже таблицу в одном запросе. Для этого есть функция aliased(). Если честно, то я ее еще ни разу не использовал, поэтому возьму пример из документации

with SessionContext() as session: 	adalias1 = aliased(Address) 	adalias2 = aliased(Address) 	for username, email1, email2 in \ 		session.query(User.name, adalias1.email_address, adalias2.email_address).\ 		join(adalias1, User.addresses).\ 		join(adalias2, User.addresses).\ 		filter(adalias1.email_address=='jack@google.com').\ 		filter(adalias2.email_address=='j25@yahoo.com'): 		print username, email1, email2  

Ну и под конец, пример, который используется у меня в коде и работает на PostgreSQL. Функция over() не находится в разделе диалектов postgresql, поэтому скорее всего будет работать везде. В данном случае я хочу показать как можно работать с функцией func.

 from sqlalchemy import over  with SessionContext() as session: 	query = session.query( 		User, 		over( 			func.row_number(), 		        partition_by=User.id 		) 	) 	for user, row_number in query.all(): 		print "{0}# {1}".format(row_number, user.username) 

Данный пример выведет пронумерованные имена пользователей в том порядке, в котором они были найдены в базе.

В конце статьи повторюсь. Возможно этот код будет не самым быстрым, но зато, как минимум, вы сможете поддерживать множество баз данных, которые поддерживаются SQLAlchemy.
Например, для разработки и тестирования можно не разворачивать полноценную реляционную базу данных на локальной машине, а пользоваться SQLite.

К плюсам такого подхода можно отнести:
— Единообразие кода. В python файлах будет только python
— Экранирование возможных входящих данных средствами SQLAlchemy
— Возможность использовать различные базы данных, не меняя при этом синтаксис запросов
— Возможность вообще не знать SQL, т.к. прямого использования SQL нет.
— Не надо писать километровые запросы самому

Минусы:
— Расход памяти на вызовы функций, на хранение в памяти генераторов. Строка все-таки занимает меньше места в памяти.
— Невозможно увидеть сразу что сгенерировал SQLALchemy (только на момент исполнения)
— Надо знать SQLAlchemy, его функции и возможности, чтобы правильно им воспользоваться. Все-таки библиотека не маленькая

Больше об ORM SQLAlchemy можно почитать тут sqlalchemy.org.