programming unlimited "categories" in python ?

[Stephen]

Thank you for the help, Thomas.  Your suggestion is a good
solution.   I had briefly considered something similar ~ not
caching the information but rather creating the "catdict"
each and every time that I need to use it (which circumvents
the problem of DB synchronization especially in multi-user 
environments) ~ however, I thought to myself "there has to
be a better way, that's more scalable and faster". Hence my
initial post.

Thank you.

Stephen.



> If You're willing to sacrifice the memory, You could cache the 
> information.
> 
> First extract all the category/parent pairs into a list. (eg. SELECT 
> parent_id, category_id FROM Categories)
> then create and fill a dictionary which contain the parent and all 
> children:
> 
> >>> catlist = [(0, 1), (0, 2), (1, 3), (3, 4), (1, 5), (3, 6), (2, 7), 
>  (7, 8), (5, 9), (2, 10), (7, 11)]
> >>>
> >>> catdict = {0: (None, [])} # (parent, children)
> >>> for catpair in catlist:
> ...  catdict[catpair[1]] = (catpair[0], [])
> ...
> >>> for catpair in catlist:
> ...  parent = catdict.get(catpair[0])
> ...  while parent:
> ...   parent[1].append(catpair[1]) # append to list of children
> ...   parent = catdict.get(parent[0]) # parent's parent
> ...
> >>> print catdict
> {11: (7, []), 10: (2, []), 9: (5, []), 8: (7, []), 7: (2, [8, 11]), 6: 
> (3, []), 5: (1, [9]), 4: (3, []), 3: (1, [4, 6]), 2: (
> 0, [7, 8, 10, 11]), 1: (0, [3, 4, 5, 6, 9]), 0: (None, [1, 2, 3, 4, 5, 
> 6, 7, 8, 9, 10, 11])}
> 
> Now you can get all the children of a given category by calling 
> catdict[category_id].
> 
> Ofcourse You'd have to keep the dictionary in sync when updating the 
> DB.