Does SQLAlchemy support "closure tables?"

Question

I've been reading about closure tables as a way of modeling hierarchies over SQL.

Does [SQLAlchemy] have any built-in support for creating and traversing hierarchical collections of object instances (tree structured collections) using closure tables?

Answer 1

I've recently blogged on this matter.

For the most part, sqlalchemy doesn't do anything that SQL also doesn't do; sqlalchemy really only provides ways of generating sql, and turning the result sets into nice python objects.

If your database provides some useful tools for working with recurrence relations, such as the CONNECT BY or WITH RECURSIVE constructs, then sqlalchemy can be easily adapted to treat the resulting queries as selectable and mappable python objects.

In the linked post, I was faced with implementing a read-write partial order on MySQL, which offers no SQL level assistance for recursive data structures. The solution I devised created an analogue of a sqlalchemy style instrumented attribute that maintained the transitive closure invariant behind the scenes.