8:SQL遍历和添加内容

遍历存储在RDBMS中的数据资源树,在任意点添加文件夹和文档。

背景

我们现在有了sqlacalchemy,为我们提供了一个持久的根。我们如何安排一个无限嵌套的URL空间,其中URL段指向我们类的实例,嵌套在其他实例中?

如前所述,SQLAlchemy使用邻接列表关系允许表中的自联接。这允许资源存储其父级的标识符。通过这个,我们可以在sqlacalchemy中创建一个通用的“节点”模型,它包含 Pyramid 遍历所需的部分。

简而言之,我们使用内置的sqlachemy关系,为RDBMS数据提供python字典行为。这允许我们定义自己的容器和类型,以任何我们喜欢的方式嵌套。

目标

步骤

  1. 我们将使用前面的步骤作为起点:

    $ cd ..; cp -r sqlroot sqladdcontent; cd sqladdcontent
    $ $VENV/bin/python setup.py develop
    
  2. 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
    
  3. 更新导入 __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()
    
  4. 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)
    
  5. 我们的 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)
    
  6. 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>
    
  7. 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 %}
    
  8. foldersqladdcontent/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 %}
    
  9. documentsqladdcontent/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 %}
    
  10. contentssqladdcontent/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>
    
  11. 更新 breadcrumbssqladdcontent/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 %}
    
  12. 修改 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')
    
  13. 通过运行脚本更新数据库。

    $ $VENV/bin/initialize_tutorial_db development.ini
    
  14. 运行 Pyramid 应用程序时使用:

    $ $VENV/bin/pserve development.ini --reload
    
  15. 在浏览器中打开http://localhost:6543/。

分析

addcontentzodb @view_config

我们得到了一个资源树,它给了我们层次结构。在大多数情况下,这些都是 Pyramid 中成熟的“资源”。

  • 遍历树并匹配内容类型上的视图

  • 知道如何访问任何资源的父级(即使在当前URL之外)

  • 应用所有面向遍历的视图谓词

  • 能够为系统中的任何资源生成完整的URL

更好的是,资源树的数据存储在与核心业务数据分开的表中。同样,用于在树中移动的ORM代码位于单独的模块中。您可以注视业务对象的数据和代码,忽略 Pyramid 部分。

这对于以空白石板开始的项目最有用,因为它们不需要遵循现有的数据或模式。在非树数据上改装树是可能的,但比较困难。