Storing Web Data in an SQL Database

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.

The Future of Twisted Web Development

The classes in twisted.web.resource and twisted.web.server are, as of this writing, widely considered to be past their prime. They have some serious shortcomings, especially their lack of support for Deferred results in Resource.render and Resource.getChild. Both are scheduled to be deprecated in the future in favor of improved classes provided by a next-generation web package known as twisted.web2 . Unfortunately, twisted.web2 is under such heavy development at this time that any examples included in this book would probably stop working soon after it went to press. While twisted.web2 will have a different API, it will continue to use the concepts introduced here of a hierarchy of objects rendering requests.

Since twisted.web.resource and twisted.web.server will eventually be deprecated, you should avoid depending on them too heavily in your code. For a simple web server, you can take the approach in Example 4-3 and use a subclass of twisted.http.Request. twisted.web2 will also provide a backward-compatible interface for Resource objects, so you should be able to port code such as that in Examples 4-5 and 4-6 with little to no effort.

If you're building a major web application with Twisted, you should use the Nevow (pronounced "nouveau") web server framework, which you can download from http://www.nevow.com. Nevow gives you more than just classes for working with HTTP requests: it provides many of the goodies you'd expect in a modern web application framework, including an XML templating system. It also includes some unique features like livepage, which gives you a way to send JavaScript code to the browser through a persistent connection. Nevow is designed from the ground up for Twisted, which means you can use Deferreds everywhere, and it's released under an extremely liberal open source license.

Like twisted.web2, Nevow hasn't yet reached the point where it has stable APIs that can be documented here. But it is mature enough that people are already using it for production web sites. You can find up-to-date documentation and examples at http://www.nevow.com.

 

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.