7: RDBMS Root Factories¶
Using SQLAlchemy to provide a persistent root resource via a resource factory.
Background¶
In 6: Storing Resources In ZODB we used 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, however, use SQLAlchemy's adjacency list relationship to provide a tree-like structure. We will do this in the next two steps.
In the first step, 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.
注釈
This step presumes you are familiar with the material in 19: Databases Using SQLAlchemy.
注釈
Traversal's usage of SQLAlchemy's adjacency list relationship and polymorphic table inheritance came from Kotti, a Pyramid-based CMS inspired by Plone. Daniel Nouri has advanced the ideas of first-class traversal in SQL databases with a variety of techniques and ideas. Kotti is certainly the place to look for the most modern approach to traversal hierarchies in SQL.
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¶
We are going to use the siteroot step as our starting point:
$ cd ..; cp -r siteroot sqlroot; cd sqlroot
Introduce some new dependencies and a console script in
sqlroot/setup.py
:1from setuptools import setup 2 3requires = [ 4 'pyramid', 5 'pyramid_jinja2', 6 'pyramid_tm', 7 'sqlalchemy', 8 'zope.sqlalchemy', 9 'pyramid_debugtoolbar' 10] 11 12setup(name='tutorial', 13 install_requires=requires, 14 entry_points="""\ 15 [paste.app_factory] 16 main = tutorial:main 17 [console_scripts] 18 initialize_tutorial_db = tutorial.initialize_db:main 19 """, 20)
Now we can initialize our project:
$ $VENV/bin/python setup.py develop
Our configuration file at
sqlroot/development.ini
wires together some new pieces:1[app:main] 2use = egg:tutorial 3pyramid.reload_templates = true 4pyramid.includes = 5 pyramid_debugtoolbar 6 pyramid_tm 7sqlalchemy.url = sqlite:///%(here)s/sqltutorial.sqlite 8 9[server:main] 10use = egg:pyramid#wsgiref 11host = 0.0.0.0 12port = 6543 13 14# Begin logging configuration 15 16[loggers] 17keys = root, tutorial, sqlalchemy 18 19[logger_tutorial] 20level = DEBUG 21handlers = 22qualname = tutorial 23 24[logger_sqlalchemy] 25level = INFO 26handlers = 27qualname = sqlalchemy.engine 28 29[handlers] 30keys = console 31 32[formatters] 33keys = generic 34 35[logger_root] 36level = INFO 37handlers = console 38 39[handler_console] 40class = StreamHandler 41args = (sys.stderr,) 42level = NOTSET 43formatter = generic 44 45[formatter_generic] 46format = %(asctime)s %(levelname)-5.5s [%(name)s][%(threadName)s] %(message)s 47 48# End logging configuration
The
setup.py
has an entry point for a console script atsqlroot/tutorial/initialize_db.py
, so let's add that script: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 .models import ( 13 DBSession, 14 Root, 15 Base, 16 ) 17 18 19def usage(argv): 20 cmd = os.path.basename(argv[0]) 21 print('usage: %s <config_uri>\n' 22 '(example: "%s development.ini")' % (cmd, cmd)) 23 sys.exit(1) 24 25 26def main(argv=sys.argv): 27 if len(argv) != 2: 28 usage(argv) 29 config_uri = argv[1] 30 setup_logging(config_uri) 31 settings = get_appsettings(config_uri) 32 engine = engine_from_config(settings, 'sqlalchemy.') 33 DBSession.configure(bind=engine) 34 Base.metadata.create_all(engine) 35 36 with transaction.manager: 37 root = Root(title='My SQLTraversal Root') 38 DBSession.add(root)
Our startup code in
sqlroot/tutorial/__init__.py
gets some bootstrapping changes:1from pyramid.config import Configurator 2 3from sqlalchemy import engine_from_config 4 5from .models 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()
Create
sqlroot/tutorial/models.py
with our SQLAlchemy model for our persistent root:1from sqlalchemy import ( 2 Column, 3 Integer, 4 Text, 5 ) 6 7from sqlalchemy.ext.declarative import declarative_base 8 9from sqlalchemy.orm import ( 10 scoped_session, 11 sessionmaker, 12 ) 13 14from zope.sqlalchemy import ZopeTransactionExtension 15 16DBSession = scoped_session( 17 sessionmaker(extension=ZopeTransactionExtension())) 18Base = declarative_base() 19 20 21class Root(Base): 22 __name__ = '' 23 __parent__ = None 24 __tablename__ = 'root' 25 uid = Column(Integer, primary_key=True) 26 title = Column(Text, unique=True) 27 28 29def root_factory(request): 30 return DBSession.query(Root).one()
Let's run this console script, thus producing our database and table:
1$ $VENV/bin/initialize_tutorial_db development.ini 22013-09-29 15:42:23,564 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("root") 32013-09-29 15:42:23,565 INFO [sqlalchemy.engine.base.Engine][MainThread] () 42013-09-29 15:42:23,566 INFO [sqlalchemy.engine.base.Engine][MainThread] 5CREATE TABLE root ( 6 uid INTEGER NOT NULL, 7 title TEXT, 8 PRIMARY KEY (uid), 9 UNIQUE (title) 10) 11 12 132013-09-29 15:42:23,566 INFO [sqlalchemy.engine.base.Engine][MainThread] () 142013-09-29 15:42:23,569 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT 152013-09-29 15:42:23,572 INFO [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit) 162013-09-29 15:42:23,573 INFO [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO root (title) VALUES (?) 172013-09-29 15:42:23,573 INFO [sqlalchemy.engine.base.Engine][MainThread] ('My SQLAlchemy Root',) 182013-09-29 15:42:23,576 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT
Nothing changes in our views or templates.
Run your Pyramid application with:
$ $VENV/bin/pserve development.ini --reload
Open http://localhost:6543/ in your browser.
Analysis¶
We perform the same kind of SQLAlchemy setup work that we saw in 19: 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 and template code query the database, our root
factory gets the top and Pyramid does the rest by passing in a context
.
This point is illustrated 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¶
What will Pyramid do if the database doesn't have a
Root
that matches the SQLAlchemy query?