sfmagic.org Rewrite: From Database to Template

19:10 Fri 21 Dec 2007
[, , ]

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,

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">

<py:for each="row in c.players">


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(':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”.

Leave a Reply