(find an updated version of this recipe @ my github page)

Given a sqlalchemy.schema.Table and a sqlalchemy.sql.expression.Select, this class will return the information from these objects with some extra columns that will properly denote the hierarchical relation between the rows. The returned Hierarchy object could then be executed and it will return the same Select statement submitted plus the following columns: * level: the relative level of the row related to its parent * connect_path: a list with all the ids that compound this part of the hierarchy, from the root node to the current value * is_leaf: boolean indicating is the particular id is a leaf or not The resultset will be returned properly ordered by the levels in the hierarchy

Special remarks: * The selected table must have a self referential foreign key relation, otherwise it will raise MissingForeignKey. * Not every database is supported (at the moment). Check the global var supported_db for an up2date list. Trying to execute Hierarchy with an unsupported db will raise NotImplementedError or HierarchyLesserError or HierarchyGreaterError (check the errors classes docstring for the exact meaning of each of them). * To prevent the query from returning every node as a different starting node and, therefore, having duplicate values, you can provide the 'starting_node' parameter in the **kwargs. The value you must provide is the parent id for the root node you want to start building the hierarchical tree. None has the same meaning as "0" since we perform a coalesce function in the query. By default the system will add a 'starting_node'="0". If you don't want a starting node, pass 'starting_node'=False and the clause will not be added to the query For examples of Hierarchy, check the tests (test1_hierarchy.py)

• Give the following table

  $ psql psql (8.4.4)

  insite=# \d category Table "public.category" Column | Type | Modifiers
  -------------+------------------------+------------------------------------------------------- id | integer | not null default nextval('category_id_seq'::regclass) name | character varying(50) | not null parent_id | integer | Indexes: "category_pkey" PRIMARY KEY, btree (id) "category_name_key" UNIQUE, btree (name) "ix_category_parent_id" btree (parent_id) Foreign-key constraints: "category_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES category(id) Referenced by: TABLE "category" CONSTRAINT "category_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES category(id)

  insite=# select * from category; id | name | parent_id ----+------------+----------- 3 | Python | 2 4 | Java | 2 5 | PHP | 2 9 | PostgreSQL | 7 8 | MySQL | 7 6 | SQLite | 7 1 | Technology |
  2 | Languages | 1 7 | Databases | 1 (9 rows)

this class will allow us to do something like this:

#!python
In [1]: from sqlalchemy import select

In [2]: import hierarchy as hie

In [3]: x = hie.Hierarchy(model.category, select([model.category.c.id, model.category.c.name]))

In [4]: Session.execute(x).fetchall()
Out[4]: 
[(1, u'Technology', 1, [1], False), # third column tells us the level of the row
 (2, u'Languages', 2, [1, 2], False), # fourth column give us a list with all the nodes from the root to the present id
 (3, u'Python', 3, [1, 2, 3], True), # fifth column tells us if the current row is a leaf node or not
 (4, u'Java', 3, [1, 2, 4], True),
 (5, u'PHP', 3, [1, 2, 5], True),
 (7, u'Databases', 2, [1, 7], False),
 (6, u'SQLite', 3, [1, 7, 6], True),
 (8, u'MySQL', 3, [1, 7, 8], True),
 (9, u'PostgreSQL', 3, [1, 7, 9], True)]

In [5]: rs = Session.execute(x).fetchall()

In [6]: for ev in rs:
           if ev.level == 1:
              print(ev.id, ev.name)
           else:
              print(ev.id, " "*2*ev.level+ev.name)
...        
...       
(1, u'Technology')
(2, u'    Languages')
(3, u'      Python')
(4, u'      Java')
(5, u'      PHP')
(7, u'    Databases')
(6, u'      SQLite')
(8, u'      MySQL')
(9, u'      PostgreSQL')

The attached tar file includes:

• init.py
• session.py: just the necessary code to create a session
• hierarchy.py: the actual class that implements everything
• test1_hierarchy.py: the test unit for the class, includes 7 tests Send your comments to mariano.mara gmail com