sfmagic.org Rewrite: From Database to Template
Having gone through some hoops trying to get this rather simple step to work, I can now talk to the database and have the results shown in a Genshi template.
Today’s hidden gotcha that took me way too long to track down: the default template renderer expects a leading slash before its template in the render call, like so: render('/file.mak'). It also expects an extension, but I got that part. So I was trying to figure out what in my project was wrong when calling render('/players'), and trying all kinds of things to get that to work before, finally, starting a new Pylons project, testing in it for a while, and grasping that detail. I’m going to claim that jet lag is responsible for these little oversights.
The following are the pieces that are, as far as I can tell, necessary to get SQLAlchemy’s SQL Expressions working in Pylons.
In development.ini:
sqlalchemy.default.url = mysql://user:password@localhost/databasename sqlalchemy.echo = true sqlalchemy.pool_recycle = 3600
In pysfmagic/environment.ini:
config['pylons.g'].sa_engine = engine_from_config(config, 'sqlalchemy.default.')
In pysfmagic/model/__init__.py:
from pylons import config
from sqlalchemy import Column, MetaData, Table, types
from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session(sessionmaker(autoflush=True, transactional=True,
bind=config['pylons.g'].sa_engine))
metadata = MetaData()
players_table = Table('players', metadata,
Column('id', types.Integer, primary_key=True, nullable=False),
Column('name', types.Unicode(255), nullable=False),
Column('dci', types.Integer, nullable=False)
)
conn = config['pylons.g'].sa_engine.connect()
And this is pysfmagic/controllers/players.py:
import logging
from pysfmagic.lib.base import *
from pylons import config
from pysfmagic.model import *
from sqlalchemy.sql import select
log = logging.getLogger(__name__)
class PlayersController(BaseController):
def index(self):
import cgi
import pprint
c.pretty_environ = cgi.escape(pprint.pformat(request.environ))
players_call = select([players_table])
players = conn.execute(players_call)
c.players = players
return render('players')
The line defining players gives me a table object containing rows, each of which can be accessed as a dict with the table’s column names as keys, nothing astonishing there but it’s pretty handy.
“c” is shared between the controller and the template.
The return line is where I wasted a bunch of time, as I stated at the start of the article…
The pysfmagic/templates/players.html file:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns:py="http://genshi.edgewall.org/" xml:lang="en" lang="en">
<head>
<title>Players</title>
</head>
<body>
<ul>
<py:for each="row in c.players">
<li>${row['id']}</li>
</py:for>
</ul>
</body>
</html>
That xmlns:py attribute is necessary, incidentally, or Genshi will complain.
Lastly, I like the simplicity of Routes—these are the lines I added to pysfmagic/config/routing.py in order to make every URL map to controller to handle request/action from that controller/id to perform that action upon, and to push the ‘player’ URL to ‘players’:
map.connect(':controller/:action/:id')
map.connect(':player', controller='players', action='index')
map.connect('*url', controller='template', action='view')
Apart from the config issues that take me too long to get past, or the detours I make for myself with missing “little” details like slashes, I like Pylons so far, and still feel good about getting to the “actual coding” part—this still all feels like setup, and maybe it’s just a truism of programming that the setup always seems to take too long, and to get in the way of the fun part of “the real coding”.