Chapter 8. Databases

Because Twisted applications run in an event loop, the application must not make blocking calls in the main thread or the entire event loop will stall. Because most databases expose a blocking API, Twisted provides twisted.enterprise.adbapi as a non-blocking interface to the DB-API 2.0 API implemented by Python bindings for most popular databases, including MySQL, Postgres, and SQLite.

Nonblocking Database Queries

Switching from the blocking API to adbapi is a straightforward transformation: instead of creating individual database connections, use a connection from adbapi.ConnectionPool, which manages a pool of connections run in separate threads for you. Once you have a database cursor, instead of using the blocking execute and fetchall methods, use dbpool.runQuery to execute a SQL query and return the result.

Example 8-1 demonstrates executing a nonblocking SELECT query on a hypothetical SQLite database called users.db (the errback has been omitted for brevity).

Example 8-1. db_test.py

from twisted.internet import reactor
from twisted.enterprise import adbapi

dbpool = adbapi.ConnectionPool("sqlite3", "users.db")

def getName(email):
    return dbpool.runQuery("SELECT name FROM users WHERE email = ?",
                           (email,))

def printResults(results):
    for elt in results:
        print elt[0]

def finish():
    dbpool.close()
    reactor.stop()

d = getName("jane@foo.com")
d.addCallback(printResults)

reactor.callLater(1, finish)
reactor.run()

Tip

When using adbapi with SQLite, if you encounter an error of the form:

sqlite3.ProgrammingError: SQLite objects created in a thread
can only be used in that same thread.The object was created in
thread id 5972 and this is thread id 4916

you’ll need to create your ConnectionPool with check_same_thread=False, as in:

dbpool = adbapi.ConnectionPool("sqlite3", "users.db",
                               check_same_thread=False)

See Twisted ticket 3629 for details.

The first argument to adbapi.ConnectPool is the import string for the desired database bindings. The rest of the arguments are passed to the underlying connect method for your database bindings and thus differ based on which database you are using. For example, connecting to a MySQL database might look like adbapi.ConnectionPool("MySQLdb", db="users").

dbpool.runQuery returns a Deferred, so we can attach callbacks and errbacks for processing the result of the query just as we’ve done with Deferreds in previous chapters.

The parts of the API you are most likely to use map neatly to blocking counterparts:

adbapi.ConnectionPool()

connection = db-module.connect() followed by cursor = connection.cursor()

runOperation()

cursor.execute()

runQuery()

cursor.execute() followed by cursor.fetchall()

runInteraction()

Running multiple queries inside a transaction

Note that because we are using a ConnectionPool, we don’t have to take care of connecting to or disconnecting from the database.

Example 8-2 uses runInteraction to create the SQLite users database from Example 8-1.

Example 8-2. db_transaction_test.py

from twisted.internet import reactor
from twisted.enterprise import adbapi

dbpool = adbapi.ConnectionPool("sqlite3", "users.db")

def _createUsersTable(transaction, users):
    transaction.execute("CREATE TABLE users (email TEXT, name TEXT)")
    for email, name in users:
        transaction.execute("INSERT INTO users (email, name) VALUES(?, ?)",
                            (email, name))

def createUsersTable(users):
    return dbpool.runInteraction(_createUsersTable, users)

def getName(email):
    return dbpool.runQuery("SELECT name FROM users WHERE email = ?",
                           (email,))

def printResults(results):
    for elt in results:
        print elt[0]

def finish():
    dbpool.close()
    reactor.stop()

users = [("jane@foo.com", "Jane"), ("joel@foo.com", "Joel")]
d = createUsersTable(users)
d.addCallback(lambda x: getName("jane@foo.com"))
d.addCallback(printResults)

reactor.callLater(1, finish)
reactor.run()

Note that the function called by dbpool.runInteraction uses the blocking cursor methods of the underlying database driver and runs in a separate thread. It must be a thread-safe function.

dbpool.runInteraction returns a Deferred. In this example, _createUsersTable implicitly returns None, which Twisted considers success, invoking the first callback in the callback chain.

More Practice and Next Steps

This chapter discussed how to interact with databases in a non-blocking fashion using Twisted’s adbapi. adbapi provides an asynchronous interface to Python’s DB-API 2.0 specification, which is defined in PEP 249. The methods in the asynchronous interface map directly to methods in the blocking API, so converting a service from blocking database queries to adbapi is straightforward.

For an example of how a large project uses Twisted’s relational database support, check out the Buildbot continuous integration framework.

Twistar is a library that builds an object-relational mapper (ORM) on top of adbapi.