Our Pyramid-based wiki application now needs database-backed storage of pages. This frequently means a SQL database. The Pyramid community strongly supports the SQLAlchemy object-relational mapper (ORM) as a convenient, Pythonic way to interface to databases.
In this step we hook up SQLAlchemy to a SQLite database table.
Note
The alchemy scaffold is really helpful for getting a SQLAlchemy project going, including generation of the console script. Since we want to see all the decisions, we will forgo convenience in this tutorial and wire it up ourselves.
Warning
To make sure that your Python can reach your SQLite library correctly, please make sure the following succeeds:
$ python3.3
Python 3.3.0 (default, Mar 8 2013, 15:50:47)
[GCC 4.2.1 Compatible Apple Clang 4.0 ((tags/Apple/clang-421.0.60))] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlite3 import *
>>>
As before, let’s use the previous package as a starting point for a new distribution. Also, let’s install the dependencies required for a SQLAlchemy-oriented Pyramid application and make a directory for the console script:
(env33)$ cd ..; cp -r step10 step11; cd step11
(env33)$ easy_install-3.3 sqlalchemy pyramid_tm zope.sqlalchemy
Note
We aren’t yet doing python3.3 setup.py develop as we are changing it later.
Our configuration file at development.ini wires together some new pieces:
[app:main]
use = egg:tutorial
pyramid.reload_templates = true
pyramid.includes =
pyramid_tm
sqlalchemy.url = sqlite:///%(here)s/sqltutorial.sqlite
[server:main]
use = egg:pyramid#wsgiref
host = 0.0.0.0
port = 6547
[loggers]
keys = root, sqlalchemy
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = INFO
handlers = console
[logger_sqlalchemy]
level = INFO
handlers =
qualname = sqlalchemy.engine
[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
This engine configuration now needs to be read into the application through changes in tutorial/__init__.py:
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 | from pyramid.authentication import AuthTktAuthenticationPolicy
from pyramid.authorization import ACLAuthorizationPolicy
from pyramid.config import Configurator
from sqlalchemy import engine_from_config
from .models import DBSession, Base
from .security import groupfinder
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='tutorial.models.Root')
# Security policies
authn_policy = AuthTktAuthenticationPolicy(
'sosecret', callback=groupfinder, hashalg='sha512')
authz_policy = ACLAuthorizationPolicy()
config.set_authentication_policy(authn_policy)
config.set_authorization_policy(authz_policy)
config.add_route('wiki_view', '/')
config.add_route('login', '/login')
config.add_route('logout', '/logout')
config.add_route('wikipage_add', '/add')
config.add_route('wikipage_view', '/{uid}')
config.add_route('wikipage_edit', '/{uid}/edit')
config.add_route('wikipage_delete', '/{uid}/delete')
config.add_static_view(name='static', path='tutorial:static')
config.add_static_view('deform_static', 'deform:static/')
config.scan()
return config.make_wsgi_app()
|
We need a command-line script for initializing the database. Enter the following to initialize tutorial/scripts/__init__.py:
# package
Now enter our console script at tutorial/scripts/initializedb.py:
import os
import sys
import transaction
from sqlalchemy import engine_from_config
from pyramid.paster import (
get_appsettings,
setup_logging,
)
from ..models import (
DBSession,
Page,
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:
model = Page(title='Root', body='<p>Root</p>')
DBSession.add(model)
To wire up this new console script, our setup.py needs an entry point:
from setuptools import setup
requires = [
'pyramid',
]
setup(name='tutorial',
install_requires=requires,
entry_points="""\
[paste.app_factory]
main = tutorial:main
[console_scripts]
initialize_tutorial_db = tutorial.scripts.initializedb:main
""",
)
Since setup.py changed, we now run it:
(env33)$ python3.3 setup.py develop
The script references some models in tutorial/models.py:
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 39 | from pyramid.security import Allow, Everyone
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 Page(Base):
__tablename__ = 'wikipages'
uid = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
body = Column(Text)
def __init__(self, title, body):
self.title = title
self.body = body
class Root(object):
__acl__ = [(Allow, Everyone, 'view'),
(Allow, 'group:editors', 'edit')]
def __init__(self, request):
pass
|
Let’s run this console script, thus producing our database and table:
(env33)$ initialize_tutorial_db development.ini
2013-03-12 10:13:56,972 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("wikipages")
2013-03-12 10:13:56,972 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
2013-03-12 10:13:56,974 INFO [sqlalchemy.engine.base.Engine][MainThread]
CREATE TABLE wikipages (
id INTEGER NOT NULL,
title TEXT,
body TEXT,
PRIMARY KEY (id),
UNIQUE (title)
)
2013-03-12 10:13:56,974 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
2013-03-12 10:13:56,977 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT
2013-03-12 10:13:56,981 INFO [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
2013-03-12 10:13:56,983 INFO [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO wikipages (title, body) VALUES (?, ?)
2013-03-12 10:13:56,983 INFO [sqlalchemy.engine.base.Engine][MainThread] ('Root', '<p>Root</p>')
2013-03-12 10:13:56,985 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT
With our data now driven by SQLAlchemy queries, we need to update our tutorial/views.py:
import colander
import deform.widget
from pyramid.decorator import reify
from pyramid.httpexceptions import HTTPFound
from pyramid.renderers import get_renderer
from pyramid.security import remember, forget, authenticated_userid
from pyramid.view import view_config, forbidden_view_config
from .models import DBSession, Page
from .security import USERS
class WikiPage(colander.MappingSchema):
title = colander.SchemaNode(colander.String())
body = colander.SchemaNode(
colander.String(),
widget=deform.widget.RichTextWidget()
)
class WikiViews(object):
def __init__(self, request):
self.request = request
renderer = get_renderer("templates/layout.pt")
self.layout = renderer.implementation().macros['layout']
self.logged_in = authenticated_userid(request)
@reify
def wiki_form(self):
schema = WikiPage()
return deform.Form(schema, buttons=('submit',))
@reify
def reqts(self):
return self.wiki_form.get_widget_resources()
@view_config(route_name='wiki_view',
renderer='templates/wiki_view.pt')
def wiki_view(self):
pages = DBSession.query(Page).order_by(Page.title)
return dict(title='Welcome to the Wiki', pages=pages)
@view_config(route_name='wikipage_add',
permission='edit',
renderer='templates/wikipage_addedit.pt')
def wikipage_add(self):
if 'submit' in self.request.params:
controls = self.request.POST.items()
try:
appstruct = self.wiki_form.validate(controls)
except deform.ValidationFailure as e:
# Form is NOT valid
return dict(title='Add Wiki Page', form=e.render())
# Add a new page to the database
new_title = appstruct['title']
new_body = appstruct['body']
DBSession.add(Page(new_title, new_body))
# Get the new ID and redirect
page = DBSession.query(Page).filter_by(title=new_title).one()
new_uid = page.uid
url = self.request.route_url('wikipage_view', uid=new_uid)
return HTTPFound(url)
return dict(title='Add Wiki Page', form=self.wiki_form.render())
@view_config(route_name='wikipage_view',
renderer='templates/wikipage_view.pt')
def wikipage_view(self):
uid = int(self.request.matchdict['uid'])
page = DBSession.query(Page).filter_by(uid=uid).one()
return dict(page=page, title=page.title)
@view_config(route_name='wikipage_edit',
permission='edit',
renderer='templates/wikipage_addedit.pt')
def wikipage_edit(self):
uid = int(self.request.matchdict['uid'])
page = DBSession.query(Page).filter_by(uid=uid).one()
title = 'Edit ' + page.title
if 'submit' in self.request.params:
controls = self.request.POST.items()
try:
appstruct = self.wiki_form.validate(controls)
except deform.ValidationFailure as e:
return dict(title=title, page=page, form=e.render())
# Change the content and redirect to the view
page.title = appstruct['title']
page.body = appstruct['body']
url = self.request.route_url('wikipage_view', uid=uid)
return HTTPFound(url)
form = self.wiki_form.render(dict(
uid=page.uid, title=page.title, body=page.body)
)
return dict(page=page, title=title, form=form)
@view_config(route_name='wikipage_delete', permission='edit')
def wikipage_delete(self):
uid = int(self.request.matchdict['uid'])
page = DBSession.query(Page).filter_by(uid=uid).one()
DBSession.delete(page)
url = self.request.route_url('wiki_view')
return HTTPFound(url)
@view_config(route_name='login', renderer='templates/login.pt')
@forbidden_view_config(renderer='templates/login.pt')
def login(self):
request = self.request
login_url = request.route_url('login')
referrer = request.url
if referrer == login_url:
referrer = '/' # never use login form itself as came_from
came_from = request.params.get('came_from', referrer)
message = ''
login = ''
password = ''
if 'form.submitted' in request.params:
login = request.params['login']
password = request.params['password']
if USERS.get(login) == password:
headers = remember(request, login)
return HTTPFound(location=came_from,
headers=headers)
message = 'Failed login'
return dict(
title='Login',
message=message,
url=request.application_url + '/login',
came_from=came_from,
login=login,
password=password,
)
@view_config(route_name='logout')
def logout(self):
request = self.request
headers = forget(request)
url = request.route_url('wiki_view')
return HTTPFound(location=url,
headers=headers)
The introduction of a relational database means significant changes in our tutorial/tests.py:
import unittest
import transaction
from pyramid import testing
def _initTestingDB():
from sqlalchemy import create_engine
from tutorial.models import (
DBSession,
Page,
Base
)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
DBSession.configure(bind=engine)
with transaction.manager:
model = Page('FrontPage', 'This is the front page')
DBSession.add(model)
return DBSession
class WikiViewTests(unittest.TestCase):
def setUp(self):
self.session = _initTestingDB()
self.config = testing.setUp()
def tearDown(self):
self.session.remove()
testing.tearDown()
def test_wiki_view(self):
from tutorial.views import WikiViews
request = testing.DummyRequest()
inst = WikiViews(request)
response = inst.wiki_view()
self.assertEqual(response['title'], 'Welcome to the Wiki')
class WikiFunctionalTests(unittest.TestCase):
def setUp(self):
self.session = _initTestingDB()
self.config = testing.setUp()
from pyramid.paster import get_app
app = get_app('development.ini')
from webtest import TestApp
self.testapp = TestApp(app)
def tearDown(self):
self.session.remove()
testing.tearDown()
def test_it(self):
res = self.testapp.get('/', status=200)
self.assertIn(b'Welcome', res.body)
res = self.testapp.get('/add', status=200)
self.assertIn(b'Log', res.body)
Run the tests in your package using nose:
(env33)$ nosetests .
..
-----------------------------------------------------------------
Ran 2 tests in 1.971s
OK
Run the WSGI application:
(env33)$ pserve development.ini --reload
Open http://127.0.0.1:6547/ in your browser.
Let’s start with the dependencies. We made the decision to use SQLAlchemy to talk to our database. We also, though, installed pyramid_tm and zope.sqlalchemy. Why?
Pyramid has a strong orientation towards support for transactions. Specifically, you can install a transaction manager into your app application, either as middleware or a Pyramid “tween”. Then, just before you return the response, all transaction-aware parts of your application are executed.
This means Pyramid view code usually doesn’t manage transactions. If your view code or a template generates an error, the transaction manager aborts the transaction. This is a very liberating way to write code.
The pyramid_tm package provides a “tween” that is configured in the development.ini configuration file. That installs it. We then need a package that makes SQLAlchemy and thus the RDBMS transaction manager integrate with the Pyramid transaction manager. That’s what zope.sqlalchemy does.
Where do we point at the location on disk for the SQLite file? In the configuration file. This lets consumers of our package change the location in a safe (non-code) way. That is, in configuration. This configuration-oriented approach isn’t required in Pyramid; you can still make such statements in your __init__.py or some companion module.
The initializedb is a nice example of framework support. You point your setup at the location of some [console_scripts] and these get generated into your virtualenv’s bin directory. Our console script follows the pattern of being fed a configuration file with all the bootstrapping. It then opens SQLAlchemy and creates the root of the wiki, which also makes the SQLite file. Note the with transaction.manager part that puts the work in the scope of a transaction (as we aren’t inside a web request where this is done automatically.)
The models.py does a little bit extra work to hook up SQLAlchemy into the Pyramid transaction manager. It then declares the model for a Page.
Our views have changes primarily around replacing our dummy dictionary-of-dictionaries data with proper database support: list the rows, add a row, edit a row, and delete a row.