8:SQL遍历和添加内容¶
遍历存储在RDBMS中的数据资源树,在任意点添加文件夹和文档。
背景¶
我们现在有了sqlacalchemy,为我们提供了一个持久的根。我们如何安排一个无限嵌套的URL空间,其中URL段指向我们类的实例,嵌套在其他实例中?
如前所述,SQLAlchemy使用邻接列表关系允许表中的自联接。这允许资源存储其父级的标识符。通过这个,我们可以在sqlacalchemy中创建一个通用的“节点”模型,它包含 Pyramid 遍历所需的部分。
简而言之,我们使用内置的sqlachemy关系,为RDBMS数据提供python字典行为。这允许我们定义自己的容器和类型,以任何我们喜欢的方式嵌套。
目标¶
重新创建 5:向层次结构添加资源 和 6:在ZODB中存储资源 步骤,可在其中添加文件夹。
将遍历和字典行为扩展到SQLAlchemy模型。
步骤¶
我们将使用前面的步骤作为起点:
$ cd ..; cp -r sqlroot sqladdcontent; cd sqladdcontent $ $VENV/bin/python setup.py develop
Make a Python module for a generic
Node
使我们在sqladdcontent/tutorial/sqltraversal.py
:1from sqlalchemy import ( 2 Column, 3 Integer, 4 Unicode, 5 ForeignKey, 6 String 7 ) 8from sqlalchemy.ext.declarative import declarative_base 9from sqlalchemy.orm import ( 10 scoped_session, 11 sessionmaker, 12 relationship, 13 backref 14 ) 15from sqlalchemy.orm.exc import NoResultFound 16from sqlalchemy.util import classproperty 17from zope.sqlalchemy import ZopeTransactionExtension 18 19DBSession = scoped_session( 20 sessionmaker(extension=ZopeTransactionExtension())) 21Base = declarative_base() 22 23 24def u(s): 25 # Backwards compatibility for Python 3 not having unicode() 26 try: 27 return unicode(s) 28 except NameError: 29 return str(s) 30 31 32def root_factory(request): 33 return DBSession.query(Node).filter_by(parent_id=None).one() 34 35 36class Node(Base): 37 __tablename__ = 'node' 38 id = Column(Integer, primary_key=True) 39 name = Column(Unicode(50), nullable=False) 40 parent_id = Column(Integer, ForeignKey('node.id')) 41 children = relationship("Node", 42 backref=backref('parent', remote_side=[id]) 43 ) 44 type = Column(String(50)) 45 46 @classproperty 47 def __mapper_args__(cls): 48 return dict( 49 polymorphic_on='type', 50 polymorphic_identity=cls.__name__.lower(), 51 with_polymorphic='*', 52 ) 53 54 def __setitem__(self, key, node): 55 node.name = u(key) 56 if self.id is None: 57 DBSession.flush() 58 node.parent_id = self.id 59 DBSession.add(node) 60 DBSession.flush() 61 62 def __getitem__(self, key): 63 try: 64 return DBSession.query(Node).filter_by( 65 name=key, parent=self).one() 66 except NoResultFound: 67 raise KeyError(key) 68 69 def values(self): 70 return DBSession.query(Node).filter_by(parent=self) 71 72 @property 73 def __name__(self): 74 return self.name 75 76 @property 77 def __parent__(self): 78 return self.parent
更新导入
__init__.py
使用我们刚刚创建的新模块。1from pyramid.config import Configurator 2 3from sqlalchemy import engine_from_config 4 5from .sqltraversal import ( 6 DBSession, 7 Base, 8 root_factory, 9 ) 10 11 12def main(global_config, **settings): 13 engine = engine_from_config(settings, 'sqlalchemy.') 14 DBSession.configure(bind=engine) 15 Base.metadata.bind = engine 16 17 config = Configurator(settings=settings, 18 root_factory=root_factory) 19 config.include('pyramid_jinja2') 20 config.scan('.views') 21 return config.make_wsgi_app()
sqladdcontent/tutorial/models.py
非常简单,随着重物移动到共同模块:1from sqlalchemy import ( 2 Column, 3 Integer, 4 Text, 5 ForeignKey, 6 ) 7 8from .sqltraversal import Node 9 10 11class Folder(Node): 12 __tablename__ = 'folder' 13 id = Column(Integer, ForeignKey('node.id'), primary_key=True) 14 title = Column(Text) 15 16 17class Document(Node): 18 __tablename__ = 'document' 19 id = Column(Integer, ForeignKey('node.id'), primary_key=True) 20 title = Column(Text)
我们的
sqladdcontent/tutorial/views.py
5:向层次结构添加资源 步骤:1from random import randint 2 3from pyramid.httpexceptions import HTTPFound 4from pyramid.location import lineage 5from pyramid.view import view_config 6 7from .models import ( 8 Folder, 9 Document 10 ) 11 12 13class TutorialViews(object): 14 def __init__(self, context, request): 15 self.context = context 16 self.request = request 17 self.parents = reversed(list(lineage(context))) 18 19 @view_config(renderer='templates/root.jinja2', 20 context=Folder, custom_predicates=[lambda c, r: c is r.root]) 21 def root(self): 22 page_title = 'Quick Tutorial: Root' 23 return dict(page_title=page_title) 24 25 @view_config(renderer='templates/folder.jinja2', 26 context=Folder) 27 def folder(self): 28 page_title = 'Quick Tutorial: Folder' 29 return dict(page_title=page_title) 30 31 @view_config(name='add_folder', context=Folder) 32 def add_folder(self): 33 # Make a new Folder 34 title = self.request.POST['folder_title'] 35 name = str(randint(0, 999999)) 36 new_folder = self.context[name] = Folder(title=title) 37 38 # Redirect to the new folder 39 url = self.request.resource_url(new_folder) 40 return HTTPFound(location=url) 41 42 @view_config(name='add_document', context=Folder) 43 def add_document(self): 44 # Make a new Document 45 title = self.request.POST['document_title'] 46 name = str(randint(0, 999999)) 47 new_document = self.context[name] = Document(title=title) 48 49 # Redirect to the new document 50 url = self.request.resource_url(new_document) 51 return HTTPFound(location=url) 52 53 @view_config(renderer='templates/document.jinja2', 54 context=Document) 55 def document(self): 56 page_title = 'Quick Tutorial: Document' 57 return dict(page_title=page_title)
5:向层次结构添加资源
addcontent/tutorial/templates
sqladdcontent/tutorial/templates/
sqladdcontent/tutorial/templates/addform.jinja2
添加内容:1<p> 2 <form class="form-inline" 3 action="{{ request.resource_url(context, 'add_folder') }}" 4 method="POST"> 5 <div class="form-group"> 6 <input class="form-control" name="folder_title" 7 placeholder="New folder title..."/> 8 </div> 9 <input type="submit" class="btn" value="Add Folder"/> 10 </form> 11</p> 12<p> 13 <form class="form-inline" 14 action="{{ request.resource_url(context, 'add_document') }}" 15 method="POST"> 16 <div class="form-group"> 17 <input class="form-control" name="document_title" 18 placeholder="New document title..."/> 19 </div> 20 <input type="submit" class="btn" value="Add Document"/> 21 </form> 22</p>
sqladdcontent/tutorial/templates/root.jinja2
:1{% extends "templates/layout.jinja2" %} 2{% block content %} 3 4 <h2>{{ context.title }}</h2> 5 <p>The root might have some other text.</p> 6 {% include "templates/contents.jinja2" %} 7 8 {% include "templates/addform.jinja2" %} 9 10{% endblock content %}
folder
在sqladdcontent/tutorial/templates/folder.jinja2
:1{% extends "templates/layout.jinja2" %} 2{% block content %} 3 4 <h2>{{ context.title }}</h2> 5 {% include "templates/contents.jinja2" %} 6 7 {% include "templates/addform.jinja2" %} 8 9{% endblock content %}
document
在sqladdcontent/tutorial/templates/document.jinja2
:1{% extends "templates/layout.jinja2" %} 2{% block content %} 3 4 <h2>{{ context.title }}</h2> 5 <p>A document might have some body text.</p> 6 7{% endblock content %}
contents
在sqladdcontent/tutorial/templates/contents.jinja2
:1<h4>Contents</h4> 2<ul> 3 {% for child in context.values() %} 4 <li> 5 <a href="{{ request.resource_url(child) }}">{{ child.title }}</a> 6 </li> 7 {% endfor %} 8</ul>
更新
breadcrumbs
在sqladdcontent/tutorial/templates/breadcrumbs.jinja2
:1{% for p in view.parents %} 2<span> 3 <a href="{{ request.resource_url(p) }}">{{ p.title }}</a> >> 4</span> 5{% endfor %}
修改
initialize_db.py
脚本。1import os 2import sys 3import transaction 4 5from sqlalchemy import engine_from_config 6 7from pyramid.paster import ( 8 get_appsettings, 9 setup_logging, 10 ) 11 12from .sqltraversal import ( 13 DBSession, 14 Node, 15 Base, 16 ) 17 18from .models import ( 19 Document, 20 Folder, 21 ) 22 23 24def usage(argv): 25 cmd = os.path.basename(argv[0]) 26 print('usage: %s <config_uri>\n' 27 '(example: "%s development.ini")' % (cmd, cmd)) 28 sys.exit(1) 29 30 31def main(argv=sys.argv): 32 if len(argv) != 2: 33 usage(argv) 34 config_uri = argv[1] 35 setup_logging(config_uri) 36 settings = get_appsettings(config_uri) 37 engine = engine_from_config(settings, 'sqlalchemy.') 38 DBSession.configure(bind=engine) 39 Base.metadata.create_all(engine) 40 41 with transaction.manager: 42 root = Folder(name='', title='My SQLTraversal Root') 43 DBSession.add(root) 44 f1 = root['f1'] = Folder(title='Folder 1') 45 f1['da'] = Document(title='Document A')
通过运行脚本更新数据库。
$ $VENV/bin/initialize_tutorial_db development.ini
运行 Pyramid 应用程序时使用:
$ $VENV/bin/pserve development.ini --reload
在浏览器中打开http://localhost:6543/。
分析¶
addcontent
和 zodb
@view_config
我们得到了一个资源树,它给了我们层次结构。在大多数情况下,这些都是 Pyramid 中成熟的“资源”。
遍历树并匹配内容类型上的视图
知道如何访问任何资源的父级(即使在当前URL之外)
应用所有面向遍历的视图谓词
能够为系统中的任何资源生成完整的URL
更好的是,资源树的数据存储在与核心业务数据分开的表中。同样,用于在树中移动的ORM代码位于单独的模块中。您可以注视业务对象的数据和代码,忽略 Pyramid 部分。
这对于以空白石板开始的项目最有用,因为它们不需要遵循现有的数据或模式。在非树数据上改装树是可能的,但比较困难。