How to traverse a tree stored in SQL database with Python? -

i have root tree stored in sql using materialized path (storing path string each row).

what best way visit each node node without starting root each time? materialized path right approach?

harry ├── jane │   ├── mark │   └── diane │       ├── mary │       └── george │           └── jill └── bill 

what expect code first starts @ harry , visits jane, diane, george, jill. in order visit mary, need go 1 level jill, , visit mary. mary doesn't have children, , we've visited every node in level (george, mary), go level visit mark. no more children left on level, go 1 level jane, we've no other node on level, go again. finally, have bill on level, , visit him. when nodes have been visited, finished.

i thought storing each level of tree in separate tables, , storing references tables in table seems bit inefficient because i'd have store level traversal on, , manipulate data.

level_0_table: harry, bill

level_1_table: jane

level_2_table: mark, diane

level_3_table: mary, george

level_4_table: jill

i'm not sure if "materialized paths" best data structure out there seems highly redundant. perhaps want adjacency lists (that is, storing id, parent_id every entry) or nested sets (storing id, left , right neighbor ids). here nice overview on both structures. want perform depth-first search (dfs) on tree. time ago, question covered in this thread, might find useful. there way of performing dfs means of sql queries, implementation depend on database software use. in case, can implement dfs using stack store ids of elements still need visit. after every visit push children of node onto stack , proceed next popped element. here nice example.