Edit me on GitHub

7: RDBMS Root Factories

Using SQLAlchemy to provide a persistent root resource via a resource factory.

Background

In 6: Storing Resources In ZODB we say using a Python object database, the ZODB, for storing our resource tree information. The ZODB is quite helpful at keeping a graph structure that we can use for traversal’s “location awareness”.

Relational databases, though, aren’t hierarchical. We can, though, use the SQLAlchemy’s adjacency list relationship to provide a tree-like structure. We will do this in the next two steps.

In the first step, though, we get the basics in place: SQLAlchemy, a SQLite table, transaction-awareness, and a root factory that gives us a context. We will use 2: Basic Traversal With Site Roots as a starting point.

Note

This step presumes you are familiar with the material in Databases Using SQLAlchemy

Goals

  • Introduce SQLAlchemy and SQLite into the project, including transaction awareness
  • Provide a root object that is stored in the RDBMS and use that as our context

Steps

  1. We are going to use the siteroot step as our starting point:

    $ cd ..; cp -r siteroot sqlroot; cd sqlroot
    
  2. Introduce some new dependencies and a console script in sqlroot/setup.py:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    from setuptools import setup
    
    requires = [
        'pyramid',
        'pyramid_jinja2',
        'pyramid_tm',
        'sqlalchemy',
        'pyramid_tm',
        'zope.sqlalchemy',
        'pyramid_debugtoolbar'
    ]
    
    setup(name='tutorial',
          install_requires=requires,
          entry_points="""\
          [paste.app_factory]
          main = tutorial:main
          [console_scripts]
          initialize_tutorial_db = tutorial.initialize_db:main
          """,
    )
    
  3. Now we can initialize our project:

    $ $VENV/bin/python setup.py develop
    
  4. Our configuration file at sqlroot/development.ini wires together some new pieces:

    [app:main]
    use = egg:tutorial
    pyramid.reload_templates = true
    pyramid.includes =
        pyramid_debugtoolbar
        pyramid_tm
    sqlalchemy.url = sqlite:///%(here)s/sqltutorial.sqlite
    
    [server:main]
    use = egg:pyramid#wsgiref
    host = 0.0.0.0
    port = 6543
    
    # Begin logging configuration
    
    [loggers]
    keys = root, tutorial, sqlalchemy
    
    [logger_tutorial]
    level = DEBUG
    handlers =
    qualname = tutorial
    
    [logger_sqlalchemy]
    level = INFO
    handlers =
    qualname = sqlalchemy.engine
    
    [handlers]
    keys = console
    
    [formatters]
    keys = generic
    
    [logger_root]
    level = INFO
    handlers = console
    
    [handler_console]
    class = StreamHandler
    args = (sys.stderr,)
    level = NOTSET
    formatter = generic
    
    [formatter_generic]
    format = %(asctime)s %(levelname)-5.5s [%(name)s][%(threadName)s] %(message)s
    
    # End logging configuration
    
  5. The setup.py had an entry point for a console script at sqlroot/tutorial/initialize_db.py gets some bootstrapping changes:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    import os
    import sys
    import transaction
    
    from sqlalchemy import engine_from_config
    
    from pyramid.paster import (
        get_appsettings,
        setup_logging,
        )
    
    from .models import (
        DBSession,
        Root,
        Base,
        )
    
    
    def usage(argv):
        cmd = os.path.basename(argv[0])
        print('usage: %s <config_uri>\n'
              '(example: "%s development.ini")' % (cmd, cmd))
        sys.exit(1)
    
    
    def main(argv=sys.argv):
        if len(argv) != 2:
            usage(argv)
        config_uri = argv[1]
        setup_logging(config_uri)
        settings = get_appsettings(config_uri)
        engine = engine_from_config(settings, 'sqlalchemy.')
        DBSession.configure(bind=engine)
        Base.metadata.create_all(engine)
    
        with transaction.manager:
            root = Root(title='My SQLTraversal Root')
            DBSession.add(root)
    
  6. Our startup code in sqlroot/tutorial/__init__.py gets some bootstrapping changes:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    from pyramid.config import Configurator
    
    from sqlalchemy import engine_from_config
    
    from .models import (
        DBSession,
        Base,
        root_factory
        )
    
    
    def main(global_config, **settings):
        engine = engine_from_config(settings, 'sqlalchemy.')
        DBSession.configure(bind=engine)
        Base.metadata.bind = engine
    
        config = Configurator(settings=settings,
                              root_factory=root_factory)
        config.include('pyramid_jinja2')
    
        config.scan('.views')
        return config.make_wsgi_app()
    
  7. Create sqlroot/tutorial/models.py with our SQLAlchemy model for our persistent root:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    from sqlalchemy import (
        Column,
        Integer,
        Text,
        )
    
    from sqlalchemy.ext.declarative import declarative_base
    
    from sqlalchemy.orm import (
        scoped_session,
        sessionmaker,
        )
    
    from zope.sqlalchemy import ZopeTransactionExtension
    
    DBSession = scoped_session(
        sessionmaker(extension=ZopeTransactionExtension()))
    Base = declarative_base()
    
    
    class Root(Base):
        __name__ = ''
        __parent__ = None
        __tablename__ = 'root'
        uid = Column(Integer, primary_key=True)
        title = Column(Text, unique=True)
    
    
    def root_factory(request):
        return DBSession.query(Root).one()
    
  8. Let’s run this console script, thus producing our database and table:

    $ $VENV/bin/initialize_tutorial_db development.ini
    2013-09-29 15:42:23,564 INFO  [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("root")
    2013-09-29 15:42:23,565 INFO  [sqlalchemy.engine.base.Engine][MainThread] ()
    2013-09-29 15:42:23,566 INFO  [sqlalchemy.engine.base.Engine][MainThread]
    CREATE TABLE root (
        uid INTEGER NOT NULL,
        title TEXT,
        PRIMARY KEY (uid),
        UNIQUE (title)
    )
    
    
    2013-09-29 15:42:23,566 INFO  [sqlalchemy.engine.base.Engine][MainThread] ()
    2013-09-29 15:42:23,569 INFO  [sqlalchemy.engine.base.Engine][MainThread] COMMIT
    2013-09-29 15:42:23,572 INFO  [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
    2013-09-29 15:42:23,573 INFO  [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO root (title) VALUES (?)
    2013-09-29 15:42:23,573 INFO  [sqlalchemy.engine.base.Engine][MainThread] ('My SQLAlchemy Root',)
    2013-09-29 15:42:23,576 INFO  [sqlalchemy.engine.base.Engine][MainThread] COMMIT
    
  9. Nothing changes in our views or templates.

  10. Run your Pyramid application with:

    $ $VENV/bin/pserve development.ini --reload
    
  11. Open http://localhost:6543/ in your browser.

Analysis

We perform the same kind of SQLAlchemy setup work that we saw in Databases Using SQLAlchemy. In this case, our root factory returns an object from the database.

This models.Root instance is the context for our views and templates. Rather than have our view/template code query the database, our root factory gets the top and Pyramid does the rest by passing in a context.

This point is emphasized by the fact that we didn’t have to change our view logic or our templates. They depended on a context. Pyramid found the context and passed it into our views.

Extra Credit

  1. What will Pyramid do if the database doesn’t have a Root that matches the SQLAlchemy query?

Table Of Contents

Previous topic

6: Storing Resources In ZODB

Next topic

8: SQL Traversal and Adding Content