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

Get Twisted Network Programming Essentials, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.