воскресенье, 16 октября 2011 г.

SQLAlchemy for Python. Some queries examples including generated SQL

UPDATE on 2011-10-18. Ответил на некоторые комментарии прямо внизу поста.

Не так давно мне посчастливилось организовать technology validation самого продвинутого ORM-фреймверка для языка Python, который называется SQLAlchemy.

Признаюсь что перед стартом этого процесса я был достаточно скептически настроен, у меня была уверенность что я не увижу функционала, который бы хотя бы немного дотягивал до Java/Hibernate или .NET/NHibernate.

На моем последнем .NET-проекте, который назывался TravelConfirm, я предложил использовать NHibernate, о чем ни разу не жалею.

Более того, рекомендую всем сомневающимся внедрять его везде, где бы вы планировали использовать Entity Framework. NHibernate в связке с Fluent NHibernate прекрасно показали себя в работе с MySQL и Amazon RDS несмотря на то, что мы использовали в том числе и хранимые процедуры.

Но давайте вернемся к SQLAlchemy.

Статья на Wikipedia гласит что SQLAlchemy это open source SQL toolkit и object relational mapper, первый релиз которого был в феврале 2006-го года и что проект лицензируется под достаточно лояльной open source лицензией MIT.
http://en.wikipedia.org/wiki/SQLAlchemy

SQLAlchemy поддерживает довольно внушительный список СУБД, который в первом приближении сопоставим с таковым у Java/Hibernate:
  • Drizzle
  • Firebird
  • IBM DB2 / Informix
  • MaxDB
  • Microsoft Access
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • Sybase

Актуальную информацию по поддерживаемым диалектам можно увидеть тут:
http://www.sqlalchemy.org/docs/dialects/index.html

Еще одна особенность, которую я постояно наблюдаю в стеке Python, так это огромное изобилие библиотек, функциональность которых перекрывает, а иногда даже дублирует друг друга. Это вносит некоторую дополнительную головную боль разработчику, но это "правильная вещь". В рамках SQLAlchemy это выражается в том, что диалект каждой из представленных СУБД умеет работать поверх нескольких реализаций DB-API драйверов.

Например с MySQL я могу работать используя MySQL Connector/J, MySQL Connector/Python, mysql-python, OurSQL либо pymysql.
Подробную информацию о поддерживаемых диалектах и DB-API драйверах можно увидеть по следующей ссылке:
http://www.sqlalchemy.org/docs/core/engines.html#supported-databases

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

Хочу сделать акцент на том, что SQLAlchemy меня просто поразил чистотой генерируемого SQL-кода, который часто превосходит даже такого титана как NHibernate. Код приводится как есть, я только лишь расставил переносы строк и отступы для лучшей читаемости - в оригинале все эти запросы формируются в несколько длинных строк. Точно так же как это делает NHibernate.

Нативный SQL-запроса с параметрами:
def test_query_with_from_native_sql_with_parameters(self):
  with DbSession() as session:
   result = session.query(User)\
   .from_statement('SELECT * FROM user where name=:name')\
   .params(name='User 4')\
   .all()
Generated SQL:
SELECT * FROM user where name=?

Извлечение пользователей с сортировкой по трем параметрам в разных направлениях:
def test_query_with_order_by_several_columns(self):
  with DbSession() as session:
   result = session.query(User)\
   .order_by(User.password)\
   .order_by(User.name.desc())\
   .order_by(User.age)\
   .all()
Generated SQL:
SELECT 
  user.user_id AS user_user_id, 
  user.name AS user_name, 
  user.age AS user_age, 
  user.password AS user_password 
 FROM user 
 ORDER BY 
  user.password, 
  user.name DESC, 
  user.age

Извлечение пользователя, который первым попался под руку:
def test_query_with_first(self):
  with DbSession() as session:
   result = session.query(User).first()
Generated SQL:
SELECT 
  user.user_id AS user_user_id, 
  user.name AS user_name, 
  user.age AS user_age, 
  user.password AS user_password 
 FROM user
  LIMIT ? OFFSET ?

Извлечение всех уникальных имен пользователей:
def test_query_with_distinct(self):
  with DbSession() as session:
   result = session.query(User.name).distinct().all()
Generated SQL:
SELECT DISTINCT user.name AS user_name
FROM user

Использование традиционных агрегирующих функций:
def test_query_with_max_min_avg_count(self):
  with DbSession() as session:
   max_user_age = session.query(func.max(User.age)).scalar()
   min_user_age = session.query(func.min(User.age)).scalar()
   avg_user_age = session.query(func.avg(User.age)).scalar()
   count_user_age = session.query(func.count(User.age)).scalar()
Generated SQL:
SELECT max(user.age) AS max_1 
 FROM user
 
 SELECT min(user.age) AS min_1 
 FROM user
 
 SELECT avg(user.age) AS avg_1 
 FROM user
 
 SELECT count(user.age) AS count_1 
 FROM user

Извлечение с группировкой:
def test_query_with_group_by(self):
        with DbSession() as session:
            result = session.query(User.name, func.count(User.user_id))\
    .group_by(User.name)\
    .all()
Generated SQL:
SELECT 
  user.name AS user_name, 
  count(user.user_id) AS count_1 
 FROM user 
 GROUP BY user.name

Надуманный вариант с подзапросом вместо традиционного join'а:
def test_query_with_subquery(self):
  with DbSession() as session:
   subquery = session.query(User.name).filter(User.age > 21).subquery()
   result = session.query(Developer).join(subquery, subquery.c.name == Developer.name).all()
Generated SQL:
SELECT 
  developer.developer_id AS developer_developer_id, 
  developer.name AS developer_name 
 FROM developer JOIN (SELECT user.name AS name 
 FROM user 
 WHERE user.age > ?) AS anon_1 ON anon_1.name = developer.name


Выборка данных с тремя inner join'ами:
def test_query_which_join_three_tables(self):
  with DbSession() as session:
   result = session.query(User, Developer, Parent)\
   .join(Developer, Developer.name == User.name)\
   .join(Parent, Developer.name == Parent.name)\
   .all()
Generated SQL:
SELECT 
  user.user_id AS user_user_id, 
  user.name AS user_name, 
  user.age AS user_age, 
  user.password AS user_password, 
  developer.developer_id AS developer_developer_id, 
  developer.name AS developer_name, 
  parent.parent_id AS parent_parent_id, 
  parent.name AS parent_name 
 FROM user 
  JOIN developer ON developer.name = user.name 
  JOIN parent ON developer.name = parent.name

Выборка данных с одним jeft outer join'ом:
def test_query_with_left_outer_join(self):
  with DbSession() as session:
   result = session.query(Developer, Parent)\
   .outerjoin(Parent, Parent.name == Developer.name)\
   .all()
Generated SQL:
SELECT 
  developer.developer_id AS developer_developer_id, 
  developer.name AS developer_name, 
  parent.parent_id AS parent_parent_id, 
  parent.name AS parent_name 
 FROM developer 
  LEFT OUTER JOIN parent ON parent.name = developer.name

Ограничение выборки используя методы limit и offset из querying DSL:
def test_query_with_limit_offset(self):
  with DbSession() as session:
   result = session.query(User).order_by(User.name).limit(1).offset(1).all()
Generated SQL:
SELECT 
  user.user_id AS user_user_id, 
  user.name AS user_name, 
  user.age AS user_age, 
  user.password AS user_password 
 FROM user ORDER BY user.name
  LIMIT ? OFFSET ?

Ограничение выборки используя метод slice с двумя параметрами из querying DSL. Вероятно в каком-то из диалектов метод slice может быть более эффективен:
def test_query_with_slice(self):
  with DbSession() as session:
   result = session.query(User).order_by(User.name).slice(start=1, stop=2).all()
Generated SQL:
SELECT 
  user.user_id AS user_user_id, 
  user.name AS user_name, 
  user.age AS user_age, 
  user.password AS user_password 
 FROM user 
 ORDER BY user.name
  LIMIT ? OFFSET ?

Объединение нескольких запросов через использование union:
def test_query_with_union_selects_distinct_records(self):
  with DbSession() as session:
   query1 = session.query(User.name)
   query2 = session.query(Developer.name)
   query3 = session.query(Parent.name)
   result = query1.union(query2, query3).all()
Generated SQL:
SELECT anon_1.user_name AS anon_1_user_name 
 FROM (SELECT user.name AS user_name 
 FROM user UNION SELECT developer.name AS developer_name 
 FROM developer UNION SELECT parent.name AS parent_name 
 FROM parent) AS anon_1

Объединение нескольких запросов через использование union all:
def test_query_with_union_all(self):
  with DbSession() as session:
   query1 = session.query(User.name,)
   query2 = session.query(Developer.name)
   query3 = session.query(Parent.name)
   result = query1.union_all(query2, query3).all()
Generated SQL:
SELECT anon_1.user_name AS anon_1_user_name 
 FROM (SELECT user.name AS user_name 
 FROM user UNION ALL SELECT developer.name AS developer_name 
 FROM developer UNION ALL SELECT parent.name AS parent_name 
 FROM parent) AS anon_1


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

Более того, DSL для построения запросов в SQLAlchemy даже умеет ряд вещей, которых я не видел ни в NHibernate ни в Linq, а именно:
  • подзапросы
  • предложения UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL, HAVING, CASE
  • indexing hint'ы
  • bulk insert для любой СУБД (у NHibernate есть ограниченные возможности, причем работают только в Microsoft SQL Server)
  • bulk update/delete. При этом database session/unit of work не трекает изменений, но не смотря на это такая функциональность бывает нужна приложению

SQLAlchemy поддерживает три основных варианта мэппинга иерархии классов аналогично NHibernate, правда они используют немного другие термины.
У SQLAlchemy термины Single Table Inheritance, Joined Table Inheritance, Concrete Table Inheritance соответствуют терминам Table per class hierarchy, Table per subclass, Table per concrete class из NHibernate:
http://www.sqlalchemy.org/docs/orm/inheritance.html

Более детально ознакомится с функциональными возможностями и их использованием можно используя официальную документацию SQLAlchemy:
http://www.sqlalchemy.org/docs/


UPDATE on 2011-10-18. Ответил на некоторые комментарии прямо внизу поста.

> Что с Foreign Keys? (mapping/использование/генерируемый SQL)

GUID. Поддержка реализована в первую очередь для PostgreSQL, но она не сложная и может быть расширена для других СУБД:
http://www.sqlalchemy.org/docs/core/types.html#backend-agnostic-guid-type

GUID.Comb не поддерживается, так что если алгоритм очень нужен, то придется его портировать с NHibernate.

HiLo генератор так же не реализован. Есть пример кастомного генератора для database shards, где говорится что его можно использовать как прототип для HiLo генератора:
https://bitbucket.org/sqlalchemy/sqlalchemy/src/54ee83285eef/examples/sharding/attribute_shard.py

Post Insert генераторы не выделены явно, однако поддерживаются по-умолчанию. Identity, Guid.Native и любой другой генератор, который вычисляется на стороне БД декларируется одинаково:
http://www.sqlalchemy.org/docs/core/schema.html#server-side-defaults

Database Sequence генератор:
http://www.sqlalchemy.org/docs/core/schema.html#defining-sequences

Composite Primary Key генератор как частный случай Assigned Key генератора:
http://stackoverflow.com/questions/2374243/is-it-possible-to-get-sqlalchemy-to-create-a-composite-primary-key-with-an-integ

К сожалению привести примеры SQL не могу, так как для этого нужно делать тесты.


> 2nd layer cache (memcached)?

SQLAlchemy умеет работать поверх Beaker Caching, который отправляет NHibernate в тяжелый нокаут по количеству поддерживаемых cache-бекендов.
С другой стороны, SQLAlchemy предоставляет более явный Caching API, что одновременно и хорошо и плохо - все зависит от личных предпочтений:
http://www.sqlalchemy.org/docs/orm/examples.html#beaker-caching


> Подзапросы в NH не так красиво как в Py, но всё же

Если быть более точным, то поздапросы в NHibernate работают только при использовании QueryOver и недоступны в Linq.


> NHibernate {bulk/batching} на вскидку 2 варианта и с недавнего (3.2) времени дэфолтный:

Bulk insert и query batching это немного разные вещи. Насколько эффективно реализован query batching в SQLAlchemy сказать не могу. Но вот bulk insert в SQLAlchemy реализован удобно, просто и не зависит от СУБД. При этом NHibernate поддерживает bulk insert только у Microsoft SQL Server'а, поскольку эта функциональность упирается в возможности нижележащего ADO.NET Data Provider'а.

4 комментария:

Dmitriy Schekhovtsov комментирует...

1. Что с Foreign Keys? (mapping/использование/генерируемый SQL)
2. 2nd layer cache (memcached)?
как подключается, предусмотрено ли фреймворком?
3. Подзапросы в NH
http://bartreyserhove.blogspot.com/2008/08/nhibernate-subqueries.html
не так красиво как в Py, но всё же
4. NHibernate {bulk/batching}
на вскидку 2 варианта
старый: http://nhforge.org/blogs/nhibernate/archive/2008/10/30/bulk-data-operations-with-nhibernate-s-stateless-sessions.aspx
и с недавнего (3.2) времени дэфолтный:
http://fabiomaulo.blogspot.com/2011/03/nhibernate-32-batching-improvement.html

Alexey Samoshkin комментирует...

SQL немного почище чем в NHibernate, и в целом не отличается от написанного вручную. Алиасы таблиц и колонок сделаны почитабельней.

На мой взгляд, рассмотрено слишком много стандартных запросов, в которых с большой долей вероятности справилось бы любое решение, претендующее называться ORM tool. Хотелось бы посмотреть на то, как себя ведет SQLAlchemy для сложных нетривиальных запросов, а также посмотреть на те уникальные фичи, которых нет у NH например, и о которых Вы упомянули.

Varela комментирует...

1) SQLAlchemy позволяет проводить отношения 1-n, m-n, 1-1. Сделано достаточно интуитивно и можно легко кастомизировать структуру в которую будут грузиться связанные элементы. Так же можно сделать ленивую загрузку и жадную загрузку для коллекции, чтобы элементы грузилить при первом обращении или сразу после запроса.

2) Есть возможность подключить кэширование на уровне запросов; это не часть фреймверка, но входит в стандартные примеры.

3) Кэширования 1-го уровня как такого в SQLAlchemy нет, есть Identity Map, что мне кажется более правильным с точки зрения ORM.

Alexey Diyan комментирует...

@Дмитрий Шеховцов:
Постарался ответить на все твои вопросы прямо внизу поста.

@Алексей Самошкин:
Согласен, приведенные запросы недостаточно сложные. Постараюсь в будущем опубликовать еще примеры по мэппингу связей, мэппингу иерархий классов. Можно будет туда включить и пару примеров со сложными запросами.

Насчет уникальных фич пока сложно сказать, на это все нужно время. Возможно по мере работы с этим ORM что-то будет попадаться под руку.

У SQLAlchemy есть например API с помощью которого можно извлечь всю информацию о схеме БД. Это может быть полезно если ты например хочешь провести валидацию на предмет соответствия твоего мэппинга и схемы БД.

Еще в SQLAlchemy есть поддержка database shards, который в отличии от NHibernate, находится не в alpha-версии.

@valera:
Спасибо большое за комментарий. Возможно некоторые другие аспекты я немного рассмотрю в следующем посте о SQLAlchemy.