Не так давно мне посчастливилось организовать 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
> 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'а.
