SQL

Enamel provides a wrapping around SQLAlchemy and sAsync for asynchronous SQL transactions.

enamel.storage.SQL represents a SQLAlchemy AccessBroker? object with a startup method which reads a tables dictionary. Keywords of the tables dictionary represent a table with the value being a list sql.Column objects.

class SQLStorage(storage.SQL):
    tables = {
        'users':[
            sql.Column('uid', sql.Integer, primary_key = True),
            sql.Column('username', sql.String(255)),
            sql.Column('password', sql.String(255))
        ]
    }

    @sql.transact
    def authenticateUser(self, username, password):
        """ Returns a user matching the provided credentials, or returns None
            Iff the password is None then we must check the username only
        """
        return self.users.select(
            sql.and_(
                self.users.c.username==username,
                self.users.c.password==sha.sha(password).hexdigest()
            )
        ).execute().fetchone()

    @sql.transact
    def addUser(self, username, password):
        """Adds a user. Password should be sha hashed by the caller"""
        return self.users.insert().execute(
            username = username,
            password = password
        )

The SQLAlchemy website has further documentation on available types and methods. Database methods should be preceded with the sql.transact decorator (from sAsync).

Connecting and instantiation

The connect string is given as a parameter to the SQL Storage object's constructor. For example:

class MyEnamel(enamel.Enamel):
    storage = SQLStorage('sqlite:///test.db')
    ...

For information on other connect strings for MySQL and Postgres look at http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_establishing

Using SQL objects in pages

It is important to understand how deferreds work with Twisted which is documented here http://twistedmatrix.com/projects/core/documentation/howto/async.html. While this document and the concepts may seem bewildering, it's at the very least essential to gloss over them even if it is not immediately obvious.

The nice thing is that Nevow handles deferreds very well, so they can be returned from render methods. All database transactions in Enamel are wrapped in a deferred layer called sAsync.

Enamel storage objects are exposed via self.enamel.storage in all page objects which are a child of some Enamel deployment.

For example, consider we add the following method to our storage object

    @sql.transact
    def getUsers(self):
        """Get all users"""
        return self.users.select().execute().fetchall()

The simplest way to expose the result of this query in your page is as follows

class Page(pages.Standard):
    def render_users(self, ctx, data):
        def showUsers(databaseResult):
            return ctx.tag[
                [
                    tags.div["Username: %s" % user[1], tags.br, "Id: %s" % user[0]]
                for user in databaseResult]
            ]

        def brokenUsers(error):
            return ctx.tag[tags.div(style="border: 1px solid red")["Error fetching users: %s" % repr(error)]]

        return self.enamel.storage.getUsers().addCallback(showUsers).addErrback(brokenUsers)

This seems quite odd since we have functions inside a function, but it is perfectly fine to do so. For cleaner syntax you could move the functions into the page class. It is also suggested to use a data method and a pattern/sequence renderer to produce the HTML output.

For example use the template syntax as follows

<table n:render="sequence" n:data="users">
    <tr n:pattern="header">
        <td>Name</td>
        <td>Id</td>
    </tr>
    <tr n:pattern="item" n:render="sequence">
        <td n:pattern="item" n:render="string"></td>
    </tr>
</table>

And swap render_ for data_ as folows

class Page(pages.Standard):
    def data_users(self, ctx, data):
        request = self.enamel.storage.getUsers()

        def showUsers(databaseResult):
            # Convert our SQL result object into a list
            return [ i for i in databaseResult ]

        return request.addBoth(showUsers)

Other render patterns are shown in the API documentation at http://python.net/crew/mwh/nevowapi/nevow.rend.html