Вступна по складних запитах в 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
    
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.