This is a pre-production deployment of Warehouse. Changes made here affect the production instance of PyPI (
Help us improve Python packaging - Donate today!

Adjacency List Relationships helper (only using databases own implementations)

Project Description


Given a `Table`_ object and a `Select`_ expression, 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 (**IMPORTANT**: Oracle requires some extra post processing to generate the list)
- 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 (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

*Supported databases*:

- PostgreSQL (>=8.4.0)
- Oracle (>=10g)

*Databases we might support on a next version*:

- SQL Server
- DB2

*Databases that we know we cannot support* (because they do not implement recursive):

- SQLite

Some examples

First of all, let's set up some imports and variables we will be using ::

>>> import ConfigParser
>>> from sqlalchemy import Table, Column, ForeignKey, MetaData, create_engine
>>> from sqlalchemy import Unicode, select, and_
>>> from sqlalchemy.orm import mapper, relationship, scoped_session, sessionmaker
>>> from sqla_hierarchy import *
>>> DBSession = scoped_session(sessionmaker())
>>> metadata = MetaData()
>>> config = ConfigParser.ConfigParser()
>>> engine = create_engine('postgresql://%s' % config.get('dburi', 'pg-db'))
>>> DBSession.configure(bind=engine)
>>> metadata.bind = engine

Let's build some simple table/class to hold boss/employee relation ::

>>> example_tb = Table('employee', metadata,
... Column('id', Unicode, primary_key=True),
... Column('boss', Unicode, ForeignKey('')))
>>> class Employee(object):
... def __init__(self, employee, boss=None):
... = employee
... self.boss = boss
... def __repr__(self):
... return "<Employee %s, Boss %s>" % (, self.boss)
>>> mapper(Employee, example_tb, properties={ #doctest: +ELLIPSIS
... 'parent': relationship(Employee, remote_side=[])})
<Mapper at 0x...; Employee>
>>> example_tb.drop(checkfirst=True)
>>> example_tb.create(checkfirst=True)

Add some data ::

>>> pl = [Employee(u'King Cold', None), Employee(u'Frieza', u'King Cold'),
... Employee(u'Zarbon', u'Frieza'), Employee(u'Dodoria', u'Frieza'),
... Employee(u'Captain Ginyu', u'Frieza'),
... Employee(u'Jeice', u'Captain Ginyu'),
... Employee(u'Burter', u'Captain Ginyu'),
... Employee(u'Recoome', u'Captain Ginyu'),
... Employee(u'Guldo', u'Captain Ginyu'),
... Employee(u'Dr Gero', None), Employee(u'A-16', u'Dr Gero'),
... Employee(u'A-17', u'Dr Gero'), Employee(u'A-18', u'Dr Gero'),
... Employee(u'Cell', u'Dr Gero'), Employee(u'Cell Junior', u'Cell')]
>>> DBSession.add_all(pl)
>>> DBSession.commit()

Now let's query some basic relations. First we want a list of bosses and employees using some indentation to visually understand who depends on who ::

>>> qry = Hierarchy(DBSession, example_tb, select([example_tb]))
>>> rs = DBSession.execute(qry).fetchall()
>>> for ev in rs:
... if ev.level == 1:
... print(
... else:
... print(" "*2*
Dr Gero
Cell Junior
King Cold
Captain Ginyu

Let's take a look at the special attributes sqla_hierachy added ::

>>> print(rs[0])
(u'Dr Gero', None, 1, ['Dr Gero'], False)

*Level*: The relative position of this record in the tree. '1' means he/she is the root. The higher the number, the bigger the distance is between the root and the current record ::

>>> print(rs[0].level)

*Connect Path*: Print the full list of members of this relation: from the root up to the current record::

>> print(rs[0].connect_path)
['Dr Gero']

*Is Leaf*: If is_leaf is True, this record has no more dependents. If False, there are more records that depend on him/her::

>>> print(rs[0].is_leaf)

Now an example with a record that is a leaf ::

>>> print(rs[9].level, rs[9].is_leaf)
(4, True)
>>> print(rs[9].connect_path)
['King Cold', 'Frieza', 'Captain Ginyu', 'Burter']

.. _Table:
.. _Select: _
Release History

Release History

This version
History Node


Download Files

Download Files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
sqla_hierarchy-0.1-py2.4.egg (12.1 kB) Copy SHA256 Checksum SHA256 2.4 Egg Mar 23, 2011
sqla_hierarchy-0.1-py2.5.egg (12.1 kB) Copy SHA256 Checksum SHA256 2.5 Egg Mar 23, 2011
sqla_hierarchy-0.1-py2.6.egg (12.1 kB) Copy SHA256 Checksum SHA256 2.6 Egg Mar 23, 2011
sqla_hierarchy-0.1-py2.7.egg (12.1 kB) Copy SHA256 Checksum SHA256 2.7 Egg Mar 23, 2011
sqla_hierarchy-0.1.tar.gz (6.7 kB) Copy SHA256 Checksum SHA256 Source Mar 23, 2011

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting