How to manage db connections using webpy with SQLObject?

Question

Web.py has its own database API, web.db. It's possible to use SQLObject instead, but I haven't been able to find documentation describing how to do this properly. I'm especially interested in managing database connections. It would be best to establish a connection at the wsgi entry point, and reuse it. Webpy cookbook contains an example how to do this with SQLAlchemy. I'd be interested to see how to properly do a similar thing using SQLObject.

This is how I currently do it:

class MyPage(object):
  def GET(self):
    ConnectToDatabase()
    ....
    return render.MyPage(...)

This is obviously inefficient, because it establishes a new database connection on each query. I'm sure there's a better way.

score 0 · Answer 1 · answered Dec 07 '11 at 18:43

As far as I understand the SQLAlchemy example given, a processor is used, that is, a session is created for each connection and committed when the handler is complete (or rolled back if an error has occurred).

I don't see any simple way to do what you propose, i.e. open a connection at the WSGI entry point. You will probably need a connection pool to serve multiple clients at the same time. (I have no idea what are the requirements for efficiency, code simplicity and so on, though. Please comment.)

Inserting ConnectToDatabase calls into each handler is of course ugly. I suggest that you adapt the cookbook example replacing the SQLAlchemy session with a SQLObject connection.

How to manage db connections using webpy with SQLObject?

1 Answers1