ltree
is a sequence of labels that can be used to represent a hierarchical tree-like structure. The PostgreSQL database provides an ltree data type with very powerful indexing functionalities, making it a practical way to store tree-like information in a relational database, often more flexible or performing than implementing an adjacency list or nested set.
This extension module contains objects to help manipulating ltree
in Python and interacting with the PostgreSQL data types using psycopg
Ltree
objects can be created from a string containing a dot-separated sequence of labels, a python sequence of labels or multiple arguments. Valid labels are sequences of alphanumeric ascii characters and underscore. Empty strings and None
are skipped.:
>>> from ltree import Ltree
>>> Ltree('a.b.c')
Ltree('a.b.c')
>>> Ltree(['a', '', 'b', 0, None, 'c'])
Ltree('a.b.0.c')
>>> Ltree('a', '', 'b', 0, None, 'c')
Ltree('a.b.0.c')
Ltree
objects can also be concatenated to other ltree or other objects representing valid labels. They can be sliced as a normal Python sequence: slicing will return a new Ltree
object; accessing by index will return the label as a string:
>>> 'first' + Ltree('a.b') + Ltree('c') + 42
Ltree('first.a.b.c.42')
>>> Ltree('a.b.c.d.e.f.g')[:2]
Ltree('a.b')
>>> Ltree('a.b.c.d.e.f.g')[5:]
Ltree('f.g')
>>> Ltree('a.b.c.d.e.f.g')[1:3]
Ltree('b.c')
>>> Ltree('a.b.c.d.e.f.g')[2]
'c'
The Lquery
object works similarly to Ltree
but also supports star symbols. Sequence of stars get merged together (because PostgreSQL lquery not always does the right thing with two stars in a row):
>>> from ltree import Lquery
>>> Lquery('a.*.b')
Lquery('a.*.b')
>>> Lquery('a.*{1}') + Lquery('*{2}.b')
Lquery('a.*{3}.b')
>>> Lquery('a.*{1}') + Lquery('*.b')
Lquery('a.*{1,}.b')
In order to pass Ltree
and Lquery
objects to psycopg2 you can register the ltree adapters using the ltree.pg.register_ltree()
function. Because the ltree
type doesn't have a fixed OID, the function takes a connection or cursor as argument to look it up:
>>> import psycopg2
>>> cnn = psycopg2.connect('')
>>> import ltree.pg
>>> ltree.pg.register_ltree(cnn)
Once the adaptation bits are in place shuttling Ltree
back and forth the database is a breeze:
>>> cur = cnn.cursor()
>>> cur.execute('select %s::ltree', [Ltree('a.b.c')])
>>> cur.fetchone()[0]
Ltree('a.b.c')
>>> cur.execute(
... "select %s::ltree ~ %s::lquery",
... [Ltree('a.b.c'), Lquery('a.*')])
>>> cur.fetchone()[0]
True
The ltree.django
module contains some Django helper. Importing it will registers the lqmatch
lookup, which can be used to filter a model for lquery
matching (the ~
operator):
objs = MyModel.objects.filter(code__lqmatch=Lquery('a.b.*')) #doctest: +SKIP