Lots of web applications use an SQL backend for data storage. With a Twisted application, though, you wouldn't want to use a regular Python SQL library. Standard SQL libraries have blocking function calls: every time you run a query, the query function will pause your application until the server returns a result. This can take a long time, especially if the query requires a lot of processing, or if the network connection to the server is slow. To use an SQL database with Twisted, you need a way to run queries using Deferreds, allowing your app to continue doing other things while it's waiting for the results.

Twisted provides such an SQL library in the twisted.enterprise package. twisted.enterprise doesn't actually include SQL drivers; it would be far too much work to support every database you might potentially want to use. Instead, twisted.enterprise provides an asynchronous API on top of the standard DB-API interface used by many Python database modules. When necessary, it uses threads to prevent database queries from blocking. You can use twisted.enterprise to work with any SQL database, as long as you have a DB-API compatible Python module for that database installed.

4.5.1. How Do I Do That?

First, make sure you have a DB-API compatible Python module installed for your particular database. Then create a twisted.enterprise.adbapi.ConnectionPool object using your database driver and connection information. Example 4-6 uses the MySQLdb module (which you can download from http://sourceforge.net/projects/mysql-python) to connect to a MySQL database. It runs a minimal weblog application that stores posts in a database table.

Example 4-6. databaseblog.py

from twisted.web import resource, static, server, http from twisted.enterprise import adbapi, util as dbutil DB_DRIVER = "MySQLdb" DB_ARGS = { 'db': 'test', 'user': 'your_user_here', 'passwd': 'your_pass_here', } class HomePage(resource.Resource): def _ _init_ _(self, dbConnection): self.db = dbConnection resource.Resource._ _init_ _(self) def render(self, request): query = "select title, body from posts order by post_id desc" self.db.runQuery(query).addCallback( self._gotPosts, request).addErrback( self._dbError, request) return server.NOT_DONE_YET def _gotPosts(self, results, request): request.write("""

MicroBlog

MicroBlog

Like a blog, but less useful

<a href="/new">New Post</a>

""") for title, body in results: request.write("

%s

" % title) request.write(body) request.write("""

""") request.finish( ) def _dbError(self, failure, request): request.setResponseCode(http.INTERNAL_SERVER_ERROR) request.write("Error fetching posts: %s" % failure.getErrorMessage( )) request.finish( ) class NewPage(resource.Resource): def render(self, request): return """New Post

New Post

Title:

Body:

""" class SavePage(resource.Resource): def _ _init_ _(self, dbConnection): self.db = dbConnection resource.Resource._ _init_ _(self) def render(self, request): title = request.args['title'][0] body = request.args['body'][0] query = """ Insert into posts (title, body) values (%s, %s) """ % (dbutil.quote(title, "char"), dbutil.quote(body, "text")) self.db.runQuery(query).addCallback( self._saved, request).addErrback( self._saveFailed, request) return server.NOT_DONE_YET def _saved(self, result, request): request.redirect("/") request.finish( ) def _saveFailed(self, failure, request): request.setResponseCode(http.INTERNAL_SERVER_ERROR) request.write("Error saving record: %s" % ( failure.getErrorMessage( ))) request.finish( ) class RootResource(resource.Resource): def _ _init_ _(self, dbConnection): resource.Resource._ _init_ _(self) self.putChild('', HomePage(dbConnection)) self.putChild('new', NewPage( )) self.putChild('save', SavePage(dbConnection)) if __name__ == "_ _main_ _": from twisted.internet import reactor dbConnection = adbapi.ConnectionPool(DB_DRIVER, **DB_ARGS) f = server.Site(RootResource(dbConnection)) reactor.listenTCP(8000, f) reactor.run( )

Example 4-6's code uses a simple SQL table called posts. You can create it by running the following SQL statement in a MySQL database:

CREATE TABLE posts ( post_id int NOT NULL auto_increment, title varchar(255) NOT NULL, body text, PRIMARY KEY (post_id) )

Then run databaseblog.py to start the server on port 8000. You should be able to view the home page in your browser. If you get this far without any errors, the database connection is working correctly. Try clicking the New Post link and composing a blog entry, as shown in Figure 4-8.

Figure 4-8. Composing a weblog post

Submit the form, and you'll be taken back to the main page. You should see your post, which is now saved in the database. See Figure 4-9.

4.5.2. How Does That Work?

Example 4-6 uses three Resource classes: HomePage, NewPage, and SavePage. HomePage connects to the database and displays the current posts in reverse order. NewPage provides a form for entering new posts. SavePage processes the form and inserts a new record in the database.

Figure 4-9. Displaying a post from the database

First, the databaseblog.py script creates a twisted.enterprise.adbapi.ConnectionPool object. A ConnectionPool represents a managed group of one or more database connections that you can use to send queries to an SQL database. Initialize a ConnectionPool with the name of the database driver module as the first argument. Any additional arguments or keyword arguments will be passed along to the driver when it is initialized.

After the ConnectionPool object in Example 4-6 is created, it gets passed to the HomePage and SavePage classes so that they can run database queries. In order to run these queries, HomePage and SavePage have to process requests asynchronously. They start by using the ConnectionPool.runQuery method to run a database query. This returns a Deferred. Example 4-6 shows how to set up callback and errback handlers for the Deferred so that a response is sent to the client once each query is complete. In each case, the render method returns the special value server.NOT_DONE_YET to indicate that the response is being processed asynchronously.