从SQLalchemy中的自引用表创建树 [英] Creating a tree from self referential tables in SQLalchemy
问题描述
我正在为一个面向iPhone的网站建立一个基本的CMS的烧瓶,而且我正遇到一些麻烦。我有一个非常小的数据库只有一个表(页)。这里是模型:
$ b $ pre $ class Page(db.Model):
__tablename__ ='pages'
id = db.Column(db.Integer,primary_key = True)
title = db.Column(db.String(100),nullable = False)
content = db.Column(db.Text,nullable = False)
parent_id = db.Column(db.Integer,db.ForeignKey(pages.id),nullable = True)
正如你所看到的,对于子页面,它们只是在 parent_id
字段中引用另一个页面对象。我想在管理面板中做的是有一个嵌套的无序列表,所有的页面嵌套在他们的父页面。我对于如何做到这一点很少有想法。所有我能想到的是以下(这只会工作(也许 - 我没有测试过)2级):
<$ c $页面中的页面为Page.query.filter_by(parent_id = None)
为页面中的页面:$ b $如果Page.query.filter_by(parent_id = page.id):
page.sub_pages =页面.query.filter_by(parent_id = page.id)
然后我将它格式化成一个列表模板。我如何使这个工作可能超过10个嵌套的页面?
感谢提前堆!
编辑:我查了一下,发现 http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships ,所以我加了
children = db.relationship(Page,backref = db.backref(parent,remote_side = id))
到我的页面
模型的底部。我正在递归地查看所有内容并将其添加到对象树中。我可能没有任何意义,但这是我能描述它的最好方法。
编辑2: / strong>我做了一个递归函数来运行所有的页面,并生成一个大的嵌套字典与所有的页面和他们的孩子,但它不断崩溃的Python,所以我认为这只是一个无限循环...这里是功能
def get_tree(base_page,dest_dict):
dest_dict = {'title':base_page.title,'content ':base_page.content}
children = base_page.children
如果孩子:
dest_dict ['children'] = {}
孩子中的孩子:
get_tree base_page,dest_dict)
else:
return
测试它:
@ app.route('/ test /')
def test():
pages = Page.query.filter_by(parent_id =无)
pages_dict = {}
页面中的页面:
get_tree(page,pages_dict)
return str(pages_dict)
有人有什么想法吗? / p>
请看 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_base
Base = declarative_base(元数据=元数据)
类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删除
cascade =all,
#many to one + adjacency list - remote_side
#需要引用'remote'
#列连接条件。
backref = backref(parent,remote_side ='TreeNode.id'),
#将在name属性中表示为字典
#。
collection_class = attribute_mapped_collection('name'),
)
$ b $ 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):
returnTreeNode(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
如果孩子:
dest_dict ['children'] = {}
对于孩子中的孩子:
get_tree(child,dest_dict)
else:
retu
使用db中递归获取数据的查询:
#4级深
node = session.query(TreeNode).\
options(joinedload_all(children,children,
children,children))。\
filter(TreeNode.name ==rootnode)。\
first()
I'm building a basic CMS in flask for an iPhone oriented site and I'm having a little trouble with something. I have a very small database with just 1 table (pages). Here's the model:
class Page(db.Model):
__tablename__ = 'pages'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
content = db.Column(db.Text, nullable=False)
parent_id = db.Column(db.Integer, db.ForeignKey("pages.id"), nullable=True)
As you can see, for sub pages, they just reference another page object in the parent_id
field. What I'm trying to do in the admin panel is have a nested unordered list with all the pages nested in their parent pages. I have very little idea on how to do this. All i can think of is the following (which will only work (maybe—I haven't tested it) 2 levels down):
pages = Page.query.filter_by(parent_id=None)
for page in pages:
if Page.query.filter_by(parent_id=page.id):
page.sub_pages = Page.query.filter_by(parent_id=page.id)
I would then just format it into a list in the template. How would I make this work with potentially over 10 nested pages?
Thanks heaps in advance!
EDIT: I've looked around a bit and found http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships, so I added
children = db.relationship("Page", backref=db.backref("parent", remote_side=id))
to the bottom of my Page
model. and I'm looking at recursively going through everything and adding it to a tree of objects. I've probably made no sense, but that's the best way I can describe it
EDIT 2: I had a go at making a recursive function to run through all the pages and generate a big nested dictionary with all the pages and their children, but it keeps crashing python so i think it's just an infinite loop... here's the function
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(base_page, dest_dict)
else:
return
and the page i'm testing it with:
@app.route('/test/')
def test():
pages = Page.query.filter_by(parent_id=None)
pages_dict = {}
for page in pages:
get_tree(page, pages_dict)
return str(pages_dict)
anyone got any ideas?
Look at http://sqlamp.angri.ru/index.html
or http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py
UPD: For adjacency_list.py declarative example
from sqlalchemy.ext.declarative import declarative_base
Base = 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
)
Fix recursion
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
Use query in example for recursive fetch data from db:
# 4 level deep
node = session.query(TreeNode).\
options(joinedload_all("children", "children",
"children", "children")).\
filter(TreeNode.name=="rootnode").\
first()
这篇关于从SQLalchemy中的自引用表创建树的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!