Welcome

Welcome on my blog
I'll try to contribute my -modest- knowledge to the community.
Here you'll find samples of python twisted code but also some network configurations.

Hoping you'll find it usefull.

gracefully
cat 'tonthon\'s brain' > /dev/null
Tonthon

Thursday, January 27, 2011

Twisted adbapi : asynchronous database access

Introduction

First of all, read the official twisted documentation.
""" It is a non-blocking interface to the standardized DB-API 2.0 API, which allows you to access a number of different RDBMSes."""

When I first used this module I faced some troubles I didn't expected, so I decided to build an example tutorial covering the basics.

Here we will build a simple sqlite database and access to it through a main DB class.
Let say we store user's informations into a database and we want to retrieve an Avatar object build from those infos.

First step : building our test database
We will use the sqlite3 library to build our database.

import tempfile
import sqlite3

USERS = (('yoen', 'Van der Veld', 'Yoen',),
('esteban', 'Garcia Marquez', 'Estebán',),
('mohamed', 'Al Ghâlib', 'Mohamed',),)

def setup_db():
""" Setup our sqlite database """
# Tempfile ensure us not to override an existing file
myfilename = tempfile.mktemp('.sqlite', 'test-bdd', '/tmp')
conn = sqlite3.connect(myfilename)
curs = conn.cursor()
curs.execute("Create table users (login text unique, name text, \
forname text)")
for login, name, forname in USERS:
curs.execute("INSERT INTO USERS VALUES (?, ?, ?)", (login,
name,
forname))
conn.commit()
curs.close()
return myfilename

dbname = setup_db()

At this point we've got a working database dbname with one table 'users' containing three fields : 'login' (unique), 'name', 'forname'.

Second step : the Avatar Object

class Avatar:
def __init__(self, login, name, forname):
self.login = login
self.name = name
self.forname = forname
def render(self):
msg = "Mr '%s %s' is connected under '%s'" % (self.forname,
self.name,
self.login,)
return msg


Nothing special to say here, don't need more for our example.

Third step : the base connector
This class will interact with the database and provide a get_user_avatar method.

from twisted.enterprise.adbapi import ConnectionPool

class DBPool:
"""
Sqlite connection pool
"""
def __init__(self, dbname):
self.dbname = dbname
self.__dbpool = ConnectionPool('sqlite3', self.dbname)
def shutdown(self):
"""
Shutdown function
It's a required task to shutdown the database connection pool:
garbage collector doesn't shutdown associated thread
"""
self.__dbpool.close()
def build_avatar(self, dbentries):
"""
Build avatar from dbentries
"""
login, name, forname = dbentries[0]
return Avatar(login, name, forname)
def get_user_avatar(self, login):
"""
Build associated avatar object
"""
query = 'SELECT * from `users` where login=?'
return self.__dbpool.runQuery(query, (login,)).addCallback(
self.build_avatar)


Now we have all we need to be able to retrieve avatars from our database.
Some special points to underline :
  • The ConnectionPool automatically start within twisted's standard reactor

  • The ConnectionPool has to be stopped after using it to avoid the "thread.error: can't start new thread" error


Last step : Let's try

from twisted.internet.defer import DeferredList
from twisted.internet import reactor
def printResult(result):
for r in result:
print(r[1])

# We initialize the db pool with our dbname retrieved in the first step.
dbpool = DBPool(dbname)
# DeferredList seems more adapted than chained callbacks in this sort of cases
ret_render = lambda avatar: avatar.render()
deferreds = [dbpool.get_user_avatar(login).addCallback(ret_render)
for login in ('yoen', 'esteban', 'mohamed',)]

dlist = DeferredList(deferreds)
dlist.addCallback(printResult)
# We ask our pool to shutdown all the initialized connections
dlist.addCallback(lambda _:dbpool.shutdown)
# Want to get the hand back :-)
reactor.callLater(4, reactor.stop)
reactor.run()

3 comments:

  1. jesus, that's some pretty nightmarish code just to do a few simple database queries.

    ReplyDelete
  2. You've got three distinct databases queries reduced in two lines of code, I don't thought it was so long.

    Feel free to participate with a better designed code, you'll be welcome.

    ReplyDelete
  3. Thanks for posting this. The bit about calling dbpool.close() to fix the "can't start new thread" issue really helped. The documentation you listed above (which is what I was using as a reference) doesn't talk about that at all, which is pretty weak.

    ReplyDelete