Skip to main content

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: _

Project details

Release history Release notifications | RSS feed

This version


Download files

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

Source Distribution

sqla_hierarchy-0.1.tar.gz (6.7 kB view hashes)

Uploaded source

Built Distributions

sqla_hierarchy-0.1-py2.7.egg (12.1 kB view hashes)

Uploaded 2 7

sqla_hierarchy-0.1-py2.6.egg (12.1 kB view hashes)

Uploaded 2 6

sqla_hierarchy-0.1-py2.5.egg (12.1 kB view hashes)

Uploaded 2 5

sqla_hierarchy-0.1-py2.4.egg (12.1 kB view hashes)

Uploaded 2 4

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Fastly Fastly CDN Google Google Object Storage and Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page