在SQLalchemy中从自引用表创建树

看看http://sqlamp.angri.ru/index.html

或http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

UPD： 对于adjacency_list.py声明性示例

from sqlalchemy.ext.declarative import declarative_basebase = declarative_base(metadata=metadata)class TreeNode(base):    __tablename__ = 'tree'    id = Column(Integer, primary_key=True)    parent_id = Column(Integer, ForeignKey('tree.id'))    name = Column(String(50), nullable=False)    children = relationship('TreeNode',  # cascade deletions  cascade="all",  # many to one + adjacency list - remote_side  # is required to reference the 'remote'   # column in the join condition.  backref=backref("parent", remote_side='TreeNode.id'),  # children will be represented as a dictionary  # on the "name" attribute.  collection_class=attribute_mapped_collection('name'),         )    def __init__(self, name, parent=None):        self.name = name        self.parent = parent    def append(self, nodename):        self.children[nodename] = TreeNode(nodename, parent=self)    def __repr__(self):        return "TreeNode(name=%r, id=%r, parent_id=%r)" % (         self.name,         self.id,         self.parent_id     )

修正递归

def get_tree(base_page, dest_dict):    dest_dict = { 'title': base_page.title, 'content': base_page.content }    children = base_page.children    if children:        dest_dict['children'] = {}        for child in children: get_tree(child, dest_dict)    else:        return

在示例中使用查询从数据库递归获取数据：

 # 4 level deep node = session.query(TreeNode).  options(joinedload_all("children", "children",     "children", "children")).  filter(TreeNode.name=="rootnode").  first()